-
Move a schema from one tablespace to another
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)
-
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?
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Almost the same. We have data for several departments in single tablespace. Now we like to create different tablespace for different departments.
-
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.
-
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.
-
-
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
-
And remember that indexes get invalidated after moving the table to the new tablespace. Rebuild them afterwards.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Code:
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
-
Originally posted by davey23uk
Code:
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 :-))
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|