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