How do I move a whole schema?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How do I move a whole schema?

  1. #1
    Join Date
    Sep 2002
    Posts
    11

    How do I move a whole schema?

    Dudes:

    I screwed up and created a schema without specifying the default and temp tablespaces - how do I get the whole thing out of SYSTEM?

    Urgent!

    Thanks - rev

  2. #2
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282
    alter user your_schema default tablespace your_tablespace
    temporary tablespace your_temp;


    F

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    alter table tablename move tablespace ts_name;

    Code:
    SQL> create user dbaf identified by dbaf;
    
    User created.
    
    SQL> grant connect, resource to dbaf;
    
    Grant succeeded.
    
    SQL> conn dbaf/dbaf
    Connected.
    SQL> create table dbforums (id number);
    
    Table created.
    
    SQL> select table_name, tablespace_name from user_tables;
    
    TABLE_NAME                     TABLESPACE_NAME
    ------------------------------ ---------------------------
    DBFORUMS                       SYSTEM
    
    SQL> conn system/manager
    Connected.
    
    SQL> alter user dbaf 
      2  default tablespace users
      3  temporary tablespace temp;
    
    User altered.
    
    SQL> conn dbaf/dbaf
    Connected.
    
    SQL> alter table dbforums move tablespace users;
    
    Table altered.
    
    SQL> select table_name, tablespace_name from user_tables;
    
    TABLE_NAME                     TABLESPACE_NAME
    ------------------------------ ---------------------------
    DBFORUMS                       USERS

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by Mnemonical
    alter user your_schema default tablespace your_tablespace
    temporary tablespace your_temp;


    F
    For future objects created, does not take care of the problem at hand - need to move the table out of system TS.

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    To move all the tables from wherever they are now to one tablespace:

    spool move_schema.sql

    select 'alter table '||table_name||
    ' move tablespace users;'
    from user_tables;


    SQL> conn scott/tiger
    Connected.
    SQL> /

    'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACEUSERS;'
    -------------------------------------------------
    alter table BONUS move tablespace users;
    alter table DEPT move tablespace users;
    alter table EMP move tablespace users;
    alter table SALGRADE move tablespace users;

    spool off

    Edit the spool file, save it, run it.

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    You could also do "drop user username cascade;" and re-run your data load script or whatever you used to create the schema.

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by stecal
    You could also do "drop user username cascade;" and re-run your data load script or whatever you used to create the schema.
    This is the way I'd choose. It's the "tidiest". If you are going to move the tables manually, remember to rebuilt associated indexes in appropriate tablespaces to.


    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

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