Tuesday, January 13, 2009

How to move LOB data to another tablespace

We know with the ALTER TABLE .. MOVE clause we can relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment.

If you want to make no other changes to the table other than rebuilding it then your statement is simply,
SQL>ALTER TABLE table_name MOVE;

Or if you want to move it to another tablespace then specify,
SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name;

With this statement it does not affect any of the lob segments associated with the lob columns in the table.

If you want to move only lob segment to a new tablespace then your command will be,

ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name);

Along with the log segment you can also move the table as well as storage attribute of table and log by following query,

ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lobcol) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));


If you want to move all the lobs contained in a tablespace of a particular user then you can follow .

Let's have a look lob column_name and table_name of the specified tablespace of owner SHAIK.

SQL> col COLUMN_NAME format a20
SQL> col TABLE_NAME format a20
SQL> select owner, table_name, column_name from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name='USERS' and segment_type='LOBSEGMENT' and owner='SHAIK');


OWNER TABLE_NAME COLUMN_NAME
------------------------------ -------------------- --------------------
SHAIK TEST_LONG_LOB B
SHAIK LOB_TAB COL2_LOB
SHAIK LOB_TAB2 COL3
SHAIK LOB_TAB2 COL2_LOB

set pagesize 0
set heading off
set lines 130
set feedback off
set verify off
set echo off
set termout off
spool move_table.scr
select 'alter table '||owner||'.'||table_name ||' move lob (' ||column_name||')' ||
'store as (tablespace DATA02);' from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name='USERS' and owner='SHAIK' and segment_type='LOBSEGMENT');
spool off


Now execute the script move_table.scr after modifying it.
SQL>@move_table.scr

No comments:

Post a Comment