Working with LOBs

Now that we know the benefits of Oracle SecureFiles, let's illustrate how to work with the LOB data in the Oracle Database. In our case study, you will see the comparison between BasicFiles and SecureFiles.

Let's first verify the setting of the db_securefile initialization parameter:

conn sys/oracle as SYSDBA
show parameter db_securefile

NAME             TYPE      VALUE
---------------- --------- ---------
db_securefil     string    PREFERRED

We will use the default setting of the parameter because it allows the creation of a SecureFile on the ASSM tablespace and the behavior can be overridden by explicitly specifying BasicFile.

For testing purposes, let's create two tablespaces with different segment space management. The TBS_BASIC tablespace is a manually managed tablespace while TBS_SECURE is an ASSM tablespace:

conn sys/oracle as SYSDBA

/*Create a manually managed tablespace*/
CREATE TABLESPACE tbs_basic
DATAFILE '/u01/app/oracle/oradata/orcl/tbs_basic.dbf'
SIZE 200M
SEGMENT...