Altering an Existing LOB Column

11.2 Altering an Existing LOB Column

You can use the ALTER TABLE statement to change the storage characteristics of a LOB column.

ALTER TABLE BNF
This section has the syntax for ALTER TABLE in Backus Naur (BNF) notation, parts of which have been simplified to keep the focus on LOB-specific parameters.
ALTER TABLE MODIFY vs ALTER TABLE MOVE LOB
This section compares the storage characteristics while using ALTER TABLE MODIFY and ALTER TABLE MOVE LOB.
ALTER TABLE SecureFiles LOB Features
This section discusses the features of SecureFile LOBs that work with the ALTER TABLE statement.

Parent topic: Persistent LOBs: Advanced DDL

11.2.1 ALTER TABLE BNF

This section has the syntax for ALTER TABLE in Backus Naur (BNF) notation, parts of which have been simplified to keep the focus on LOB-specific parameters.

See Also:

ALTER TABLE for more information on usage of ALTER TABLE statement.

ALTER TABLE [ schema.]table ... [ ... | column_clauses | ... | move_table_clause] ...;

<column_clauses> ::= ... | modify_LOB_storage_clause ...

<modify_LOB_storage_clause> ::= MODIFY LOB (LOB_item) ( modify_LOB_parameters )

<modify_LOB_parameters> ::=
{ storage_clause
  | PCTVERSION integer
  | FREEPOOLS integer
  | REBUILD FREEPOOLS
  | LOB_retention_clause
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | { ENCRYPT encryption_spec | DECRYPT }
  | { CACHE
  | { NOCACHE | CACHE READS } [ logging_clause ]
  | allocate_extent_clause
  | shrink_clause
  | deallocate_unused_clause
}. . .
<move_table_clause> ::= MOVE ...[ ... | LOB_storage_clause | ...] ...

<LOB_storage_clause> ::= 
  LOB
  { (LOB_item [, LOB_item ]...)
      STORE AS [ SECUREFILE | BASICFILE ] (LOB_storage_parameters)
  | (LOB_item)
      STORE AS [ SECUREFILE | BASICFILE ]
        { LOB_segname (LOB_storage_parameters)
        | LOB_segname
        | (LOB_storage_parameters)
        }
  }

<LOB_storage_parameters> ::=
  { TABLESPACE tablespace
  | { LOB_parameters [ storage_clause ]
    }
  | storage_clause
  }
    [ TABLESPACE tablespace
    | { LOB_parameters [ storage_clause ]
      }
    ]...

<LOB_parameters> ::=
  [ { ENABLE | DISABLE } STORAGE IN ROW
  | CHUNK integer
  | PCTVERSION integer
  | RETENTION [ { MAX | MIN integer | AUTO | NONE } ]
  | FREEPOOLS integer
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | LOB_encryption_clause
  | { CACHE |  NOCACHE | CACHE READS } [ logging_clause ] } }
  ]

<LOB_retention_clause> ::= 
  {RETENTION [ MAX | MIN integer | AUTO | NONE ]}

<LOB_deduplicate_clause> ::=
  { DEDUPLICATE 
  | KEEP_DUPLICATES
  }

<LOB_compression_clause> ::=
  { COMPRESS [ HIGH | MEDIUM | LOW ]
  | NOCOMPRESS 
  }

<LOB_encryption_clause> ::=
  { ENCRYPT [ USING 'encrypt_algorithm' ] 
    [ IDENTIFIED BY password ]
  | DECRYPT 
  }

Parent topic: Altering an Existing LOB Column

11.2.2 ALTER TABLE MODIFY vs ALTER TABLE MOVE LOB

This section compares the storage characteristics while using ALTER TABLE MODIFY and ALTER TABLE MOVE LOB.

There are two kinds of changes to existing storage characteristics:

Some changes to storage characteristics merely apply to the way the data is accessed and do not require moving the entire existing LOB data. For such changes, use the ALTER TABLE MODIFY LOB syntax, which uses the modify_LOB_storage_clause from the ALTER TABLE BNF. Examples of changes that do not require moving the entire existing LOB data are: RETENTION, PCTVERSION, CACHE, NOCACHELOGGING, NOLOGGING, or STORAGE settings, shrinking the space used by the LOB data, and deallocating unused segments.

See Also:
ALTER TABLE
Some changes to storage characteristics require changes to the way the data is stored, hence requiring movement of the entire existing LOB data. For such changes use the ALTER TABLE MOVE LOB syntax instead of the ALTER TABLE MODIFY LOB syntax because the former performs parallel operations on SecureFiles LOBs columns, making it a resource-efficient approach. The ALTER TABLE MOVE LOB syntax can process any arbitrary LOB storage clause represented by the LOB_storage_clause in the ALTER TABLE BNF, and will move the LOB data to a new location.
Examples of changes that require moving the entire existing LOB data are: TABLESPACE, ENABLE/DISABLE STORAGE IN ROW, CHUNK, COMPRESSION, DEDUPLICATION and ENCRYPTION settings.

As an alternative to ALTER TABLE MOVE LOB, you can use online redefinition to enable one or more of these features. As with ALTER TABLE, online redefinition of SecureFiles LOB columns can be executed in parallel.

11.2.3 ALTER TABLE SecureFiles LOB Features

This section discusses the features of SecureFile LOBs that work with the ALTER TABLE statement.

ALTER TABLE with Advanced LOB Compression
When used with the ALTER TABLE statement, advanced LOB compression syntax alters the compression mode of the LOB column. The examples in this section demonstrate how to issue ALTER TABLE statements for specific compression scenarios.
ALTER TABLE with Advanced LOB Deduplication
When used with the ALTER TABLE statement, advanced LOB deduplication syntax alters the deduplication mode of the LOB column. The examples in this section demonstrate how to issue ALTER TABLE statements for specific deduplication scenarios.
ALTER TABLE with SecureFiles Encryption
The examples in this section demonstrate how to issue ALTER TABLE statements for to enable SecureFiles encryption.

Parent topic: Altering an Existing LOB Column

11.2.3.1 ALTER TABLE with Advanced LOB Compression

When used with the ALTER TABLE statement, advanced LOB compression syntax alters the compression mode of the LOB column. The examples in this section demonstrate how to issue ALTER TABLE statements for specific compression scenarios.

Example: Altering a SecureFiles LOB Column to Enable LOW Compression

ALTER TABLE t1 MOVE LOB(a) STORE AS SECUREFILE(COMPRESS LOW)

Example: Altering a SecureFiles LOB Column to Disable Compression

ALTER TABLE t1 MOVE LOB(a) STORE AS SECUREFILE(NOCOMPRESS)

Example: Altering a SecureFiles LOB Column to Enable HIGH Compression

ALTER TABLE t1 MOVE LOB(a) STORE AS SECUREFILE(COMPRESS HIGH);

Example: Altering a SecureFiles LOB Column to Enable Compression on One partition

ALTER TABLE t1 MOVE PARTITION p1 LOB(a) STORE AS SECUREFILE(COMPRESS HIGH);

Parent topic: ALTER TABLE SecureFiles LOB Features

11.2.3.2 ALTER TABLE with Advanced LOB Deduplication

When used with the ALTER TABLE statement, advanced LOB deduplication syntax alters the deduplication mode of the LOB column. The examples in this section demonstrate how to issue ALTER TABLE statements for specific deduplication scenarios.

Example: Altering a SecureFiles LOB Column to Disable Deduplication

ALTER TABLE t1 MOVE LOB(a) STORE AS SECUREFILE(KEEP_DUPLICATES);

Example: Altering a SecureFiles LOB Column to Enable Deduplication

ALTER TABLE t1 MOVE LOB(a) STORE AS SECUREFILE(DEDUPLICATE);

Example: Altering a SecureFiles LOB Column to Enable Deduplication on One Partition

ALTER TABLE t1 MOVE PARTITION p1 LOB(a) STORE AS SECUREFILE(DEDUPLICATE);

Parent topic: ALTER TABLE SecureFiles LOB Features

11.2.3.3 ALTER TABLE with SecureFiles Encryption

The examples in this section demonstrate how to issue ALTER TABLE statements for to enable SecureFiles encryption.

Consider the following points when using the ALTER TABLE statement with SecureFiles Encryption:

The ALTER TABLE statement enables and disables SecureFiles Encryption. Using the REKEY option with the ALTER TABLE statement also enables you to encrypt LOB columns with a new key or algorithm.
The DECRYPT option converts encrypted columns to clear text form.