Andrew Fraser DBA

Move objects including lobs, xml to new tablespace with dynamic SQL

Here objects are moved from tablespace users to tablespace users1:

set pages 9999 lines 132
spool m2.sql
select 'alter table '||owner||'.'||table_name||' move lob('||column_name||') store as ( tablespace users1);'
from dba_lobs where tablespace_name = 'USERS' order by 1
/
select 'alter table '||owner||'.'||segment_name||' move tablespace users1;'
from dba_segments where tablespace_name = 'USERS' and segment_type = 'TABLE' order by 1
/
select 'alter index '||owner||'.'||index_name||' rebuild tablespace users1;'
from dba_indexes where tablespace_name = 'USERS' order by 1
/
spool off
ed m2