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

---

Quote:

---

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.

Quote:

---

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,