Sunday, September 14, 2008

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

OWNER                          TABLE_NAME           COLUMN_NAME
------------------------------ -------------------- --------------------
ARJU                           TEST_LONG_LOB        B
ARJU                           LOB_TAB              COL2_LOB
ARJU                           LOB_TAB2             COL3
ARJU                           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='ARJU' and segment_type='LOBSEGMENT');
spool off


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

2 comments:

Anonymous said...

Muy buena respuesta. Es muy clara y precisa.

Saludos, Ariel.

Anonymous said...

I found your script to be Very helpful.

Still some segments can prove immovable, such as IOTs or ones built on VARRAYS without resorting to Oracle packages.