Home » RDBMS Server » Server Utilities » Move Tables to Correct user(schema)
Move Tables to Correct user(schema) [message #71932] Thu, 06 February 2003 13:39 Go to next message
dadba
Messages: 34
Registered: February 2003
Member
hi everyone

I need to move several tables that were inadvertently
created under user='SYSTEM'.

I know I can drop the tables under System and re-create
them under the correct user....however I would
prefer to simply move the tables from system to
correct user.

any advice on how do this?
thx
Re: Move Tables to Correct user(schema) [message #71933 is a reply to message #71932] Thu, 06 February 2003 14:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
mag@itloaner1_local > select table_name,tablespace_name from dba_tables where table_name='EMP';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP SYSTEM

mag@itloaner1_local > alter table emp move tablespace users;

Table altered.

mag@itloaner1_local > select table_name,tablespace_name from dba_tables where table_name='EMP';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            USERS

mag@itloaner1_local > 

This is not changing the schema but the TABLESPACE [message #71944 is a reply to message #71933] Mon, 10 February 2003 03:24 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
The given answer is moving your tables to a different tablespace but NOT to another schema.

As far as I know the only way to change the schema is to drop and recreat either by using export/import or create as select.

HTH
Mike
Re: This is not changing the schema but the TABLESPACE [message #71946 is a reply to message #71933] Mon, 10 February 2003 05:52 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
thanks for pointing...
that was my mistake.~
Previous Topic: svrmgrl utility manual
Next Topic: Using LIKE operator in DECODE
Goto Forum:
  


Current Time: Tue Dec 24 06:54:36 CST 2024