Large Objects - Pro Oracle Database 12c Administration - page 279

If you have existing tablespaces, you can verify the use of ASSM by querying the DBA_TABLESPACES view. The
SEGMENT_SPACE_MANAGEMENT column should have a value of AUTO for any tablespaces that you want to use with
SecureFiles:

select tablespace_name, segment_space_management
from dba_tablespaces;

Also, SecureFiles usage is governed by the DB_SECUREFILE database parameter. You can use either ALTER SYSTEM
or ALTER SESSION to modify the value of DB_SECUREFILE . Table 11-2 describes the valid values for DB_SECUREFILE .

Table 11-2. Description of DB_SECUREFILE Settings

DB_SECUREFILE Setting Description

NEVER Creates the LOB as a BasicFiles type, regardless of whether the SECUREFILE option is

specified

PERMITTED Default value in 11g; allows creation of SecureFiles LOBs

PREFERRED

New in Oracle Database 12c, and the default value; specifies that all LOBs are created as a
SecureFiles type, unless otherwise stated

ALWAYS Creates the LOB as a SecureFiles type, unless the underlying tablespace isn't using ASSM

IGNORE Ignores the SecureFiles option, along with any SecureFiles settings

Creating a Table with a LOB Column
By default, in Oracle Database 11g and lower, when you create a LOB, it's a BasicFiles type. Starting with Oracle
Database 12c, the default underlying LOB architecture is SecureFiles. If you're using Oracle Database 11g or higher, I
recommend that you always create a LOB as a SecureFiles type. As discussed previously, SecureFiles allows you to use
features such as compression and encryption.

Creating a BasicFiles LOB Column
To create a LOB column, you have to specify a LOB data type. Here is a pre-11g example of creating a table with a CLOB
data type:

create table patchmain(
patch_id number
,patch_desc clob)
tablespace users;

If you're using Oracle Database 11g or higher, I recommend that you explicitly specify the STORE AS BASICFILE
clause in order to avoid confusion as to which LOB architecture is implemented. Listed next is such an example:

create table patchmain(
patch_id number
,patch_desc clob)
tablespace users
lob(patch_desc) store as basicfile;