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,

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,

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.


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.