Move a schema from one tablespace to another
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Move a schema from one tablespace to another

  1. #1
    Join Date
    Nov 2003
    Posts
    37

    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)


  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  3. #3
    Join Date
    Nov 2003
    Posts
    37
    Almost the same. We have data for several departments in single tablespace. Now we like to create different tablespace for different departments.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2003
    Posts
    37
    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.

  6. #6
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    how about scripts?

  7. #7
    Join Date
    Feb 2004
    Posts
    2
    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

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
    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
  •  



Click Here to Expand Forum to Full Width