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;
/ |