Datatypes - Expert Oracle Database Architecture - page 559

When discussing LOBs, I'll break the preceding list into two pieces: LOBs stored in the database, or internal LOBs,
which include CLOB , BLOB , and NCLOB ; and LOBs stored outside of the database, or the BFILE type. I will not discuss
CLOB , BLOB , or NCLOB independently, since from a storage and option perspective they are the same. It is just that a
CLOB and NCLOB support textual information and a BLOB does not. But the options we specify for them—the CHUNK size,
RETENTION , and so on—and the considerations are the same, regardless of the base type. Since BFILE s are significantly
different, I'll discuss them separately.

Internal LOBs
Starting with Oracle Database 11 g , Oracle introduced a new underlying architecture for LOBs known as SecureFiles .
The prior existing LOB architecture is known as BasicFiles . By default in 11 g , when you create a LOB, it will be created
as a BasicFiles LOB. Starting with Oracle 12 c , when creating a LOB column in an ASSM-managed tablespace, by
default the LOB will be created as a SecureFiles LOB.
Going forward, I recommend using SecureFiles over BasicFiles for the following reasons:

• Oracle's documentation states that BasicFiles will be deprecated in a future release.

• There are fewer parameters to manage with SecureFiles, namely the following attributes don't

apply to SecureFiles: CHUNK , PCTVERSION , FREEPOOLS , FREELISTS , or FREELIST GROUPS .
SecureFiles allow for the use of advanced encryption, compression, and de-duplication. If •

you're going to use these advanced LOB features, then you need to obtain a license for the
Advanced Security Option and/or the Advanced Compression Option. If you're not using
advanced LOB features, then you can use SecureFiles LOBs without an extra license.

In the following subsections, I'll detail the nuances of using both SecureFiles and BasicFiles.

Creating a SecureFiles LOB
The syntax for a SecureFiles LOB is, on the face of it, very simple—deceptively simple. You may create tables with
column datatypes of CLOB , BLOB , or NCLOB , and that is it.

EODA@ORA12CR1> create table t
2 ( id int primary key,
3 txt clob
4 )
5 segment creation immediate
6 /
Table created.

You can verify that the column was created as a SecureFiles LOB as follows:

EODA@ORA12CR1> select column_name, securefile from user_lobs where table_name='T';

COLUMN_NAME SECUREFILE
------------ ------------
TXT YES