-
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
-
alter user your_schema default tablespace your_tablespace
temporary tablespace your_temp;
F
-
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
-
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.
-
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.
-
You could also do "drop user username cascade;" and re-run your data load script or whatever you used to create the schema.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|