How do I move a whole schema?

[revnice]

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

[Mnemonical]

alter user your_schema default tablespace your_tablespace
temporary tablespace your_temp;


F

[stecal]

alter table tablename move tablespace ts_name;

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

[stecal]

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.

[stecal]

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.

[stecal]

You could also do "drop user username cascade;" and re-run your data load script or whatever you used to create the schema.

[grjohnson]

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,