CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Large Objects (LOBs)
Version 11.1
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 lobtab (
recid  NUMBER(5),
lobcol BLOB)
LOB (lobcol) STORE AS (lobseg 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;

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>
 

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
]...
)
]
 
 
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');

 
 
DROP

Drop LOB segment
 
ALTER TABLE lobtab DROP COLUMN lobcol;

Drop Table
 
DROP TABLE lobtab PURGE;
 
Related Topics
BLOB Data Type
CLOB Data Type
DBMS_LOB
Long to CLOB
Secure Files (Deduplication, Compression, Encryption)
Heap Tables
Secure Files
Segments
UTL_COMPRESS LOB Compression
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [115 users online]    © 2010 psoug.org