Oracle 18cR3 Large Objects (LOBS)

Oracle Large Objects Version 18.3.0.1

General Information

Related Data Dictionary Objects

ALL_LOBS	DBMS_DEFERGEN_LOB	LOBCOMPPARTV$
ALL_LOB_PARTITIONS	DBMS_LOB	LOBFRAG$
ALL_LOB_SUBPARTITIONS	DBMS_LOBUTIL	LOBFRAGV$
ALL_LOB_TEMPLATES	DEF$_LOB	PARTLOB$
ALL_PART_LOBS	DEFLOB	USER_LOBS
BLOB	DEFSUBPARTLOB$	USER_LOB_PARTITIONS
CLOB	GV_$LOBSTAT	USER_LOB_SUBPARTITIONS
DBA_LOBS	GV_$TEMPORARY_LOBS	USER_LOB_TEMPLATES
DBA_LOB_PARTITIONS	LOBPOINTER	USER_PART_LOBS
DBA_LOB_SUBPARTITIONS	LOB$	V_$LOBSTAT
DBA_LOB_TEMPLATES	LOBCOMPPART$	V_$TEMPORARY_LOBS
DBA_PART_LOBS

SQL CREATE

BLOB CREATE TABLE <table_name> ( column_name data_type, column_name data_type, column_name data_type) LOB (lob_name) STORE AS (TABLESPACE <tablespace_name> STORAGE (INITIAL <lob_size>) [CHUNK] <LOGGING | NOLOGGING> TABLESPACE <tablespace_name>;

CREATE TABLE blobtab (

      recid    NUMBER(5),

      blobcol BLOB)

      LOB (blobcol) STORE AS blobseg (TABLESPACE uwdata STORAGE (INITIAL 1M)

      CHUNK 4096

      NOCACHE NOLOGGING)

      TABLESPACE uwdata;

      

      desc lobtab

      

      col segment_name format a30

      

      SELECT segment_name, segment_type, tablespace_name

      FROM user_segments

      ORDER BY 2,1;

CLOB <LOB_storage_clause> ::= LOB { (LOB_item [, LOB_item ]...) STORE AS [ SECUREFILE | BASICFILE ] (LOB_storage_parameters) | (LOB_item) STORE AS [ SECUREFILE | BASICFILE ] { LOB_segname (LOB_storage_parameters) | LOB_segname | (LOB_storage_parameters)} } <LOB_storage_parameters> ::= { TABLESPACE tablespace | { LOB_parameters [ storage_clause ]} | storage_clause} [ TABLESPACE tablespace | { LOB_parameters [ storage_clause ]}]... <LOB_parameters> ::= [<ENABLE | DISABLE> STORAGE IN ROW] [CHUNK <chunk_size>] [PCTVERSION <integer>] [RETENTION < MAX | MIN integer | AUTO | NONE>] [FREEPOOLS <integer>] [LOB_deduplicate_clause] -- see securefiles [LOB_compression_clause] -- see securefiles [LOB_encryption_clause] -- see securefiles [<CACHE | NOCACHE | CACHE READS>] [<LOGGING | NOLOGGING>

CREATE TABLE clobtab (

      recid   NUMBER(5),

      clobcol CLOB)

      LOB (clobcol) STORE AS clobseg (TABLESPACE uwdata STORAGE (INITIAL 1M)

      CHUNK 4096

      NOCACHE NOLOGGING)

      TABLESPACE uwdata;

      

      desc clobtab

      

      col segment_name format a30

      

      SELECT segment_name, segment_type, tablespace_name

      FROM user_segments

      ORDER BY 2,1;

Partition Storage <LOB_partition_storage> := PARTITION partition {LOB_storage_clause | varray_col_properties} [LOB_storage_clause | varray_col_properties]...[(SUBPARTITION subpartition {LOB_storage_clause | varray_col_properties} [LOB_storage_clause | varray_col_properties]...)]

CREATE TABLE print_media_demo (

      product_id       NUMBER(6),

      ad_id            NUMBER(6),

      ad_composite     BLOB,

      ad_sourcetext    CLOB,

      ad_finaltext     CLOB,

      ad_fltextn       NCLOB,

      ad_textdocs_ntab textdoc_tab,

      ad_photo         BLOB,

      ad_graphic       BFILE,

      ad_header        adheader_typ)

      NESTED TABLE ad_textdocs_ntab

       STORE AS textdocs_nestedtab_demo

       LOB (ad_composite, ad_photo, ad_finaltext)

      STORE AS(STORAGE (INITIAL 20M))

      PARTITION BY RANGE (product_id) (

      PARTITION p1 VALUES LESS THAN (3000) TABLESPACE part1

      LOB (ad_composite, ad_photo)

       STORE AS (TABLESPACE part2 STORAGE (INITIAL 10M))

       NESTED TABLE ad_textdocs_ntab

       STORE AS nt_p1 (TABLESPACE part3),

      PARTITION P2 VALUES LESS THAN (MAXVALUE)

      LOB (ad_composite, ad_finaltext)

       STORE AS SECUREFILE (TABLESPACE part4)

       NESTED TABLE ad_textdocs_ntab STORE AS nt_p2) TABLESPACE part5;

Create LOB Options

CHUNK CHUNK <integer>

Specifies the number of bytes to be allocated for LOB manipulation, always a multiple of the database block size, if the block is stored out-of-line which will almost always be the optimal configuration.

The value of CHUNK must be less than or equal to the value of NEXT extent, either the default value or that specified in the storage_clause. If CHUNK exceeds the value of NEXT, then the database returns an error. You cannot change the value of CHUNK once it is set.

As rule of thumb ... set CHUNK to 8K if the BLOB is 8K or smaller else set to 32K if performance is the goal ... otherwise 8K.

FREEPOOLS FREESPOOLS <integer>

The number of groups of free lists for the LOB segment: Generally set to the number of instances in a stand-alone or RAC environment.

Specify this parameter only if the database is running in automatic undo mode. In this mode, FREEPOOLS is the default unless you specify the FREELIST GROUPS parameter of the storage_clause. If you specify neither FREEPOOLS nor FREELIST GROUPS the database uses a default of FREEPOOLS 1.

This clause is not valid for SecureFiles. If both SECUREFILE and FREEPOOLS are specified, FREEPOOLS is ignored.

LOGGING / NOLOGGING If not specified for a LOB segment defaults to the table in which the LOB is defined. According to the Oracle docs: "Regardless of whether LOGGING or NOLOGGING is set, LOBs never generate rollback information (undo) for LOB data pages because old LOB data is stored in versions. Rollback information that is created for LOBs tends to be small because it is only for the LOB index page changes."

PCTVERSION PCTVERSION <integer>

Specifies the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. If the database is running in manual undo mode, then the default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.

You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. PCTVERSION is the default in manual undo mode. RETENTION is the default in automatic undo mode. You cannot specify both PCTVERSION and RETENTION.

This clause is not valid for SecureFiles.

RETENTION According to the Oracle docs: "As an alternative to the PCTVERSION parameter, you can specify the RETENTION parameter in the LOB storage clause of the CREATE TABLE or ALTER TABLE statement. Doing so, configures the LOB column to store old versions of LOB data for a period of time, rather than using a percentage of the table." Retention is then based upon the undo_retention startup parameter's value.

CREATE TABLE clob_retain(

      recid INTEGER,

      clobcol CLOB)

      LOB (clobcol) STORE AS BASICFILE clobseg (TABLESPACE uwdata 

        CHUNK 4096 RETENTION NOCACHE LOGGING STORAGE (MAXEXTENTS 20));

      

      CREATE TABLE clob_retain(

      recid INTEGER,

      clobcol CLOB)

      LOB (clobcol) STORE AS SECUREFILE clobseg (TABLESPACE uwdata 

        CHUNK 4096 RETENTION NOCACHE LOGGING STORAGE (MAXEXTENTS 20));

SECUREFILE See the Secure Files Library Link at Page Bottom

STORAGE IN ROW

If most table LOBs are 8K bytes or less then use the following guidelines to maximize database performance:

Use ENABLE STORAGE IN ROW

Set the DB_BLOCK_SIZE initialization parameter to 8K bytes and use a chunk size of 8K bytes

Best performance can be obtained by reading and writing large pieces of a LOB value at a time because:

If accessing the LOB from the client side and the client is at a different node than the server, then large reads/writes reduce network overhead
If using the NOCACHE option, then each small read/write incurs an I/O. Reading/writing large quantities of data reduces the I/O.
Writing to the LOB creates a new version of the LOB chunk. Therefore, writing small amounts at a time incurs the cost of a new version for each small write. If logging is on, then the chunk is also stored in the redo log.

Use LOB Buffering to Read/Write Small Chunks of Data: If you must read or write small pieces of LOB data on the client, then use LOB buffering.

STORE AS See BLOB Demo Above

ALTER

Cache Reads CACHE READS applies only to LOB storage. It specifies that LOB values are brought into the buffer cache only during read operations but not during write operations.

<CACHE READS | NOCACHE>

conn uwclass/uwclass@pdbdev

      

      CREATE TABLE cache_test (

      testlob  BLOB)

      LOB (testlob) STORE AS (CACHE READS);

      

      SELECT table_name, cache

      FROM user_lobs;

      

      ALTER TABLE cache_test MODIFY LOB  (testlob) (NOCACHE);

Move Table Containing An LOB Segment To A Different Tablespace ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name> LOB (<lob_column_name>) STORE AS <lob_segment_name> (TABLESPACE <tablespace_name>);

SELECT tablespace_name, bytes

      FROM user_ts_quotas;

      

      conn / as sysdba

      

      ALTER USER uwclass

      QUOTA 10M ON example;

      

      conn uwclass/uwclass

      

      SELECT tablespace_name, bytes

      FROM user_ts_quotas;

      

      ALTER TABLE lobtab

      MOVE TABLESPACE uwdata

      LOB (lobcol)  STORE AS lobseg (TABLESPACE example);

      

      SELECT segment_name, tablespace_name

      FROM user_segments

      WHERE segment_name IN ('LOBTAB', 'LOBSEG');

      

      SELECT segment_name, segment_type, tablespace_name

      FROM user_segments;

Move LOB Only ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name> LOB (<lob_column_name>) STORE AS <lob_segment_name> (TABLESPACE <tablespace_name>);

ALTER TABLE lobtab

      MOVE LOB (lobcol)

       STORE AS (TABLESPACE example DISABLE STORAGE IN ROW);

      

      SELECT segment_name, tablespace_name

      FROM user_segments

      WHERE segment_name IN ('LOBTAB', 'LOBSEG');

Shrink LOB Storage ALTER TABLE <table_name> MODIFY LOB (<column_name>) (SHRINK SPACE CASCADE);

desc user_lobs

      

      col column_name format a20

      

      SELECT table_name, column_name, segment_name, tablespace_name

      FROM user_lobs;

      

      ALTER TABLE blobtab MODIFY LOB(blobcol) (SHRINK SPACE CASCADE);

DROP

Drop LOB segment ALTER TABLE <table_name> DROP COLUMN <column_name>;

ALTER TABLE lobtab DROP COLUMN lobcol;

Drop Table DROP TABLE <table_name> [PURGE];

DROP TABLE lobtab PURGE;

Functions

Length LENGTH(bl IN BLOB) RETURN INTEGER;

conn pm/pm@pdbdev

      

      SELECT LENGTH(ad_composite)

      FROM print_media;

EMPTY_BLOB EMPTY_BLOB()

CREATE OR REPLACE PROCEDURE load_blob (filein IN VARCHAR2) IS

       src_file BFILE;

       dst_file BLOB;

       lgh_file BINARY_INTEGER;

      BEGIN

        src_file := bfilename('CTEMP', filein);

      

        dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

        lgh_file := dbms_lob.getlength(src_file);

      

        FOR i IN 1..3 LOOP

          INSERT INTO reg_tab

          (rid, bcol)

          VALUES

          (i, EMPTY_BLOB())

          RETURNING bcol INTO dst_file;

      

          SELECT bcol

          INTO dst_file

          FROM reg_tab

          WHERE rid = i

          FOR UPDATE;

      

          dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

      

          UPDATE reg_tab

          SET bcol = dst_file

          WHERE rid = i;

      

          INSERT INTO sec_tab_kd

          (rid, bcol)

          VALUES

          (i, EMPTY_BLOB())

          RETURNING bcol INTO dst_file;

      

          SELECT bcol

          INTO dst_file

          FROM sec_tab_kd

          WHERE rid = i

          FOR UPDATE;

      

          dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

      

          UPDATE sec_tab_kd

          SET bcol = dst_file

          WHERE rid = i;

      

          INSERT INTO sec_tab_dd

          (rid, bcol)

          VALUES

          (i, EMPTY_BLOB())

          RETURNING bcol INTO dst_file;

      

          SELECT bcol

          INTO dst_file

          FROM sec_tab_dd

          WHERE rid = i

          FOR UPDATE;

      

          dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

      

          UPDATE sec_tab_dd

          SET bcol = dst_file

          WHERE rid = i;

        END LOOP;

        COMMIT;

      

        dbms_lob.fileclose(src_file);

      END load_blob;

      /