| 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 4000
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 4000
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.
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. |
| 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. |
| NOCACHE |
D |
| C |
| NOLOGGING |
D |
| C |
| 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 |
D |
| C |
| SECUREFILE |
D |
| C |
| STORAGE IN ROW |
D |
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 |
D |
| C |
| |
| 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> |
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
-- does not refresh in 10.2 / this is an unfixed bug
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; |
