Internal LOBs-Datatypes
Starting with Oracle Database 11g, Oracle introduced a new underlying architecture for LOBs known as SecureFiles. The prior existing LOB architecture is known as BasicFiles. By default in 11g, when you create a LOB, it will be created as a BasicFiles LOB. Starting with Oracle 12c, 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 afuture release.
•\ There are fewer parameters to manage with SecureFiles, namely, thefollowing attributes don’t apply to SecureFiles: CHUNK, PCTVERSION,FREEPOOLS, FREELISTS, or FREELIST GROUPS.
•\ SecureFiles allow for the use of advanced encryption, compression,and deduplication. If you’re going to use these advanced LOBfeatures, then you need to obtain a license for the Advanced SecurityOption and/or the Advanced Compression Option. If you’re notusing advanced LOB features, then you can use SecureFiles LOBswithout 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:
$ sqlplus eoda/foo@PDB1
SQL> create table t( id int primary key,txt clob)segment creation immediate;Table created.
You can verify that the column was created as a SecureFiles LOB as follows:
SQL> select column_name, securefile from user_lobs where table_name=’T’;COLUMN_NAME SECUREFILE
Seemingly, LOBs are as simple to use as the NUMBER, DATE, or VARCHAR2 datatypes. Or are they? The prior small examples show the tip of the iceberg—the bare minimum you can specify about a LOB. Using DBMS_METADATA, we can get the entire picture:
SQL> select dbms_metadata.get_ddl( ‘TABLE’, ‘T’ ) from dual;DBMS_METADATA.GET_DDL(‘TABLE’,’T’)
CREATE TABLE “EODA”.”T”
(“ID” NUMBER(*,0),”TXT” CLOB,PRIMARY KEY (“ID”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” ENABLE) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “USERS”
LOB (“TXT”) STORE AS SECUREFILE (TABLESPACE “USERS” ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATESSTORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
As you can see, there are quite a few parameters. Before going into the details of these parameters, in the next section I’ll generate the same type of output for a BasicFiles LOB. This will provide a basis for discussing the various LOB attributes.