How to move all objects in a schema to a different tablespace

OracleThere 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.

Posted in Databases and tagged . Bookmark the permalink. RSS feed for this post. Leave a trackback.

Swedish Greys - a WordPress theme from Nordic Themepark.