Move a schema from one tablespace to another

[sbasak1]

What is the easiest way to move a schema from one tablespace to another? I can't think other than export/import. But in that case the schema name would be different (or do it twice). If there are 10,000 tables in the schema is there any better way to do it?

(Oracle 9i)

[SANJAY_G]

What is the purpose of moving fron one tablespace to another? Or are you have have some users with all it's objects in SYSTEM tbs?

[sbasak1]

Almost the same. We have data for several departments in single tablespace. Now we like to create different tablespace for different departments.

[slimdave]

The safest method would be to "alter table my_table move tablespace my_ts" for each table, following each one with a rebuild of it's indexes.

[sbasak1]

But man, I have almost 10,000 tables. I can't do it manually. Even if I do a PL/SQL function reading data from data dictionary, it would still too involving task.

I think ultimately I need to go for import/export anyway.

[rajabalachandra]

how about scripts?

[reidlai]

Just login using SQL*Plus and with your schema user id. Run the following script:

set pagesize 0
set linesize 132
set trimspool on
set feedback off
spool temp.sql
select 'alter table ' || table_name || ' move tablespace your_target_tablespace_name ;'
spool off
@temp.sql


You should be aware that storage clause. I suggested you to consider carefully if you move all tables at the same time because each table may have different storage requirement.

Cheers

[julian]

And remember that indexes get invalidated after moving the table to the new tablespace. Rebuild them afterwards.

[davey23uk]

that script wont work

set pagesize 0
set linesize 132
set trimspool on
set feedback off
spool temp.sql
select 'alter table ' || table_name || ' move tablespace your_target_tablespace_name ;' from user_tables;
spool off
@temp.sql

[julian]

Originally posted by davey23uk

that script wont work

set pagesize 0
set linesize 132
set trimspool on
set feedback off
spool temp.sql
select 'alter table ' || table_name || ' move tablespace your_target_tablespace_name ;' from user_tables;
spool off
@temp.sql

Well, add the index rebuild stuff too. But such a script should be run when there is no heavy access to the database. Locking might cause you several problems. Experience :-))