Home > DBA > Move tables from one tablespace to another

Move tables from one tablespace to another

 

Today I had to change default tablespace for user X from USERS to XDATA together with all of its tables.

 

1. Change the default tablespace for the user which will impact all objects created in future but not the existing:

ALTER USER X default tablespace XDATA;

 

2. Move tables from tablespace USERS to the new XDATA tablespace

I wrote a script about this which will first move the X’ tables and then rebuild the indexes as they will become invalid right after the move.

 

2.1 Move operation

set heading off;

set feedback off;

set echo off;

set lines 900;

spool run_move.sql

 

select ‘ALTER TABLE.X ‘ || rtrim(ltrim(segment_name)) || ‘ MOVE TABLESPACE XDATA;’

from dba_segments

where owner = ‘X’

and segment_type = ‘TABLE’;

 

spool off;

set heading on;

set feedback on;

set echo on;

 

2.2 Rebuild indexes

set heading off;

set feedback off;

set echo off;

set lines 900;

spool run_rebuild.sql

 

select ‘ALTER INDEX X.’ || rtrim(ltrim(segment_name)) || ‘ REBUILD;’

from dba_segments

where tablespace_name =’USERS’

and segment_type = ‘INDEX’;

 

spool off;

set heading on;

set feedback on;

set echo on;

 

2.3 Eventually we can choose to rebuild and move indexes to the new tablespace

ALTER INDEX X.IND1 REBUILD TABLESPACE XDATA;

 

All this is possible if tables have no LONG columns.

 

Cheers,

Maria

 

Advertisements
Categories: DBA
  1. BranKo
    August 30, 2012 at 9:13 am

    Nice script but one mistake
    select ‘ALTER TABLE.X ‘ || rtrim(ltrim(segment_name)) || ‘ MOVE TABLESPACE XDATA;’
    is right
    select ‘ALTER TABLE X.‘ || rtrim(ltrim(segment_name)) || ‘ MOVE TABLESPACE XDATA;’

    • August 30, 2012 at 9:48 am

      Correct , punctuation mistake 🙂
      Thanks for notification!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: