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

Related Documents

Large Object (LOB) Datatypes with Example.

Advantages of LOB against LONG- Feature of LOB datatype

How to Insert Blob data(image, video) into oracle BLOB size

How to Convert Long data type to Lob