The default underlying LOB architecture is SecureFiles. It is recommended to create a LOB as a SecureFiles. As discussed previously, SecureFiles allows you to use features such as compression and encryption.

The DB_SECUREFILE parameter is not required, but you can explicitly state which LOB architecture to implement in the create statement.

SQL> create table patchmain ( patch_id number, patch_desc clob)

lob(patch_desc) store as securefile (tablespace lob_data);

If the tablespace is not specified, the LOB segment is stored in the same tablespace as its table.

Before viewing the data dictionary details regarding the LOB columns, insert a record into the table to ensure that segment information is available; for example,

SQL> insert into patchmain values(1,’clob text’);

You can now verify a LOB’s architecture by querying the USER_SEGMENTS view:

SQL> select segment_name, segment_type, segment_subtype from user_segments;

Here is some sample output, indicating that a LOB segment is a SecureFiles type:

SEGMENT_NAME                                     SEGMENT_TYPE                                                            

SEGMENT_SU

PATCHMAIN SYS_IL0000022340C00002$$
You can also query the USER_LOBS view to verify the SecureFiles LOB architecture:

SQL> select table_name, segment_name, index_name, securefile, in_row from user_lobs;

Here is the output:

You can create a partitioned table that has a LOB column. Doing so lets you spread a LOB across multiple tablespaces. Such partitioning helps with balancing I/O, maintenance, and backup and recovery operations.

All partitioning schemes supported by Oracle are fully supported on LOBs. The next example creates a LIST-partitioned table in which LOB column data is stored in tablespaces separate from those of the table data:

SQL> CREATE TABLE patchmain( patch_id NUMBER,region VARCHAR2(16) ,patch_desc CLOB)

LOB(patch_desc) STORE AS (TABLESPACE patch1) PARTITION BY LIST (REGION) (PARTITION p1 VALUES (‘EAST’) LOB(patch_desc) STORE AS SECUREFILE (TABLESPACE patch1 COMPRESS HIGH) TABLESPACE inv_data1

PARTITION p2 VALUES (‘WEST’) LOB(patch_desc) STORE AS SECUREFILE (TABLESPACE patch2 DEDUPLICATE NOCOMPRESS) TABLESPACE inv_data2

PARTITION p3 VALUES (DEFAULT) LOB(patch_desc) STORE AS SECUREFILE (TABLESPACE patch3 COMPRESS LOW) TABLESPACE inv_data3

Note that each LOB partition is created with its own storage options; again, it is optional, but the default will be in the same tablespace as the table. You can view the details about the LOB partitions as shown here:

SQL> select table_name, column_name, partition_name, tablespace_name ,compression, deduplication from user_lob_partitions;

Here is some sample output:

TABLE_NAME COLUMN_NAME   PARTITION_ TABLESPACE_NAME   

You can also view DBA/ALL/USER_PART_LOBS for information about partitioned LOBs.

You can change the storage characteristics of a partitioned LOB column after it is created. To do so, use the ALTER TABLE … MODIFY PARTITION statement. You can have compression and deduplication as well:

SQL> alter table patchmain modify partition p2 lob(patch_desc) (compress high);

SQL> alter table patchmain modify partition p3 lob(patch_desc) (deduplicate lob);

You can transparently encrypt a SecureFiles LOB column (just like any other column). Or you can also use tablespace encryption to encrypt the tablespace used for your table and LOBs. The ENCRYPT clause enables Secure Files encryption, using Oracle Transparent Data Encryption (TDE). To do this at the column level or tablespace level, you will need to enable a common keystore for the CDB and PDBs using united mode so you can manage the keys in the common keystore.

The Oracle Base Database service creates the keystore when it creates the database for the TDE. The parameters WALLET_ROOT and TDE_CONFIGURATION will have the details of the configuration.

If the database was not created with creating a keystore, you can create the keystore with the following commands:

SQL> administer key management create keystore ‘/opt/oracle/dcs/ commonstore/wallets/db23c_db23c/tde’ identified by “Cr4zyPa$$word1”;

Then the PDB will need to have the keystore enabled and open:

SQL> administer key management set keystore open identified by “Cr4zyPa$$word1”;

After the TDE wallet configuration is all set up, a table can be created with an encrypted column:

SQL> create table patchmain( patch_id number, patch_desc clob encrypt)

lob (patch_desc) store as securefile;

You can also alter a column to enable encryption:

SQL> alter table patchmain modify (patch_desc clob encrypt);

The tablespace can be created with encryption:

SQL> create tablespace lob_data encryption using ‘AES256’ encrypt;

Note partitioning, advanced compression, and advanced security options, which have been discussed in this chapter, are extra-cost options that are available only with the Oracle enterprise edition.