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