There are some situations where you need to move all objects in a schema to a tablespace different to where they are stored. I have faced this when restoring a database coming from a development environment into a production environment.
The steps below are from a 10g Oracle database running on a Solaris 10 SPARC box. I have successfully done the exact same thing in an INTEL box.
In the example below I want to move all objects in schema USER to a tablespace called TBLSPC. All you have to do is create three set of statements that do the magic. I did it under a user with DBA access to the database:
1.- Create “alter table” statements to move all tables owned by USER to tablespace TBLSPC:
SELECT 'ALTER TABLE USER.' || OBJECT_NAME ||' MOVE TABLESPACE '||' TBLSPC; ' FROM ALL_OBJECTS WHERE OWNER = 'USER' AND OBJECT_TYPE = 'TABLE';
This will generate a set of statements that look like this:
ALTER TABLE USER.TABLE_NAME MOVE TABLESPACE TBLSPC;
2.- Create “alter index” statements to move all indexes owned by USER to tablespace TBLSPC:
SELECT 'ALTER INDEX USER.'||INDEX_NAME||' REBUILD TABLESPACE TBLSPC;' FROM ALL_INDEXES WHERE OWNER = 'USER';
You should get a set of statements like the following:
ALTER INDEX USER.INDEX_NAME REBUILD TABLESPACE TBLSPC;
3.- Create “alter table” statements to move all LOBs owned by USER to tablespace TBLSPC:
SELECT 'ALTER TABLE USER.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE TBLSPC);' FROM DBA_TAB_COLS WHERE OWNER = 'USER' AND DATA_TYPE like '%LOB%';
This will create statements like:
ALTER TABLE USER.TABLE_NAME MOVE LOB(COLUMN_NAME) STORE AS (TABLESPACE TBLSPC);
4.- Run the three set of statements. You can use a program like Toad or Aqua to run them as a script or create a text file and run it from sqlplus.
That’s it! After running the three set of statements you should have all the objects owned by the user in the desired tablespace.