Oracle Large Objects (LOBS)

Oracle Large Objects Version 11.2.0.3

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 which should always be 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, then the database uses a default of FREEPOOLS 1 if the database is in automatic undo management mode and a default of FREELIST GROUPS 1 if the database is in manual undo management mode.

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."

CREATE TABLE clob_retain(
recid INTEGER,
clobcol CLOB)
LOB (clobcol) STORE AS BASICFILE 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

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

SELECT LENGTH(ad_composite)
FROM print_media;