Oracle Large Objects
Version 12.1.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

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;

Related Topics
BLOB Data Type
CLOB Data Type
DBMS_LOB
DBMS_LOBUTIL
Heap Tables
Long to CLOB
Long Raw to CLOB
Partitioned Tables
Secure Files (Deduplication, Compression, Encryption)
Segments
UTL_COMPRESS LOB Compression

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved