OraFAQ Forum: Server Utilities » Move Tables to Correct user(schema)

Home » RDBMS Server » Server Utilities » Move Tables to Correct user(schema)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Move Tables to Correct user(schema) [message #71932]

Thu, 06 February 2003 13:39

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

Report message to a moderator

Re: Move Tables to Correct user(schema) [message #71933 is a reply to message #71932]

Thu, 06 February 2003 14:14

Mahesh Rajendran
Messages: 10707
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 >

Report message to a moderator

This is not changing the schema but the TABLESPACE [message #71944 is a reply to message #71933]

Mon, 10 February 2003 03:24

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

Report message to a moderator

Re: This is not changing the schema but the TABLESPACE [message #71946 is a reply to message #71933]

Mon, 10 February 2003 05:52

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

thanks for pointing...
that was my mistake.~

Report message to a moderator

Previous Topic:	svrmgrl utility manual
Next Topic:	Using LIKE operator in DECODE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 10:00:03 CDT 2024