If table is having LOB objects below prodedure need to be followed.
======================================================================
Whenever we create a table with a CLOB column, Oracle implicitly creates a LOB segment and a LOB index for the CLOB column.
By default, LOB segment and index are created in the same tablespace as of the table. During Creation of table,
we have an option to specify storage for LOB column. We can also specify whether we want to store it within the table or outside the table.
By default storage is outside the table. We can also give specific name to segment and index instead of name being generated by Oracle.
If segment name or index name is not defined, normally segments start with SYS_LOBxxxx and indexes start with SYS_ILxxxx.
col COLUMN_NAME for a30
set linesize 200
set pagesize 30000
select TABLE_NAME , COLUMN_NAME, SEGMENT_NAME , TABLESPACE_NAME , INDEX_NAME from dba_lobs where owner='TEST' ;
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------------------------ ------------------------- ------------------------------ ------------------------------ ------------------------------
TEST_TABLE_LOB TEST_LOB1 SYS_LOB0000055439C00011$$ USERS SYS_IL0000055439C00011$$
TEST_TABLE_LOB TEST_LOB2 SYS_LOB0000055439C00009$$ USERS SYS_IL0000055439C00009$$
TEST_TABLE_LOB TEST_LOB3 SYS_LOB0000055439C00010$$ USERS SYS_IL0000055439C00010$$
After LOB movement to new tablespace.
=====================================
alter table TEST.TEST_TABLE_LOB MOVE LOB(TEST_LOB1,TEST_LOB2,TEST_LOB2) STORE AS (tablespace NEW_TABLESPACE );
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------------------------ ------------------------- ------------------------------ ------------------------------ ------------------------------
TEST_TABLE_LOB TEST_LOB1 SYS_LOB0000055439C00011$$ NEW_TABLESPACE SYS_IL0000055439C00011$$
TEST_TABLE_LOB TEST_LOB2 SYS_LOB0000055439C00009$$ NEW_TABLESPACE SYS_IL0000055439C00009$$
TEST_TABLE_LOB TEST_LOB3 SYS_LOB0000055439C00010$$ NEW_TABLESPACE SYS_IL0000055439C00010$$
======================================================================
Whenever we create a table with a CLOB column, Oracle implicitly creates a LOB segment and a LOB index for the CLOB column.
By default, LOB segment and index are created in the same tablespace as of the table. During Creation of table,
we have an option to specify storage for LOB column. We can also specify whether we want to store it within the table or outside the table.
By default storage is outside the table. We can also give specific name to segment and index instead of name being generated by Oracle.
If segment name or index name is not defined, normally segments start with SYS_LOBxxxx and indexes start with SYS_ILxxxx.
col COLUMN_NAME for a30
set linesize 200
set pagesize 30000
select TABLE_NAME , COLUMN_NAME, SEGMENT_NAME , TABLESPACE_NAME , INDEX_NAME from dba_lobs where owner='TEST' ;
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------------------------ ------------------------- ------------------------------ ------------------------------ ------------------------------
TEST_TABLE_LOB TEST_LOB1 SYS_LOB0000055439C00011$$ USERS SYS_IL0000055439C00011$$
TEST_TABLE_LOB TEST_LOB2 SYS_LOB0000055439C00009$$ USERS SYS_IL0000055439C00009$$
TEST_TABLE_LOB TEST_LOB3 SYS_LOB0000055439C00010$$ USERS SYS_IL0000055439C00010$$
After LOB movement to new tablespace.
=====================================
alter table TEST.TEST_TABLE_LOB MOVE LOB(TEST_LOB1,TEST_LOB2,TEST_LOB2) STORE AS (tablespace NEW_TABLESPACE );
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------------------------ ------------------------- ------------------------------ ------------------------------ ------------------------------
TEST_TABLE_LOB TEST_LOB1 SYS_LOB0000055439C00011$$ NEW_TABLESPACE SYS_IL0000055439C00011$$
TEST_TABLE_LOB TEST_LOB2 SYS_LOB0000055439C00009$$ NEW_TABLESPACE SYS_IL0000055439C00009$$
TEST_TABLE_LOB TEST_LOB3 SYS_LOB0000055439C00010$$ NEW_TABLESPACE SYS_IL0000055439C00010$$
No comments:
Post a Comment