How to move LOB data to another tablespace



Last updated on July 17th, 2015 at 06:05 pm

If you just want to move the table without impacting LOB,then use below

SQL>ALTER TABLE table_name MOVE;

Or
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.

To move log segments to different tablespace use below command.But it is important to mention that it will also rebuild the table in its own tablespace

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


Leave a Reply