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
This entry was posted in Scripts, Space. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>