OraFAQ Forum: Server Administration » how to move a table's tablespace

Home » RDBMS Server » Server Administration » how to move a table's tablespace

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

how to move a table's tablespace [message #58405]

Mon, 25 August 2003 00:51

iret
Messages: 2
Registered: August 2003

Junior Member

I just want move a table's tablespace.
example: a table named 'Ta' on table space system, how can move it to tablespace user?

Report message to a moderator

Re: how to move a table's tablespace [message #58406 is a reply to message #58405]

Mon, 25 August 2003 01:43

LT
Messages: 29
Registered: August 2003

Junior Member

I would suggest simply recreating the table in the appropriate table space using the sqlplus syntax.

'create table tablename as select * from ta'

Obviously, in your create table script set the appropriate tablespace and other table creation points.
Doing so will recreate your table and all of the data in the appropriate tablespace. You can then check that everything with the new table is correct and then you can simply drop the old table that is in the wrong tablespace.

Report message to a moderator

Re: how to move a table's tablespace [message #58408 is a reply to message #58406]

Mon, 25 August 2003 01:57

iret
Messages: 2
Registered: August 2003

Junior Member

It's not a good sulotion. I have thounds of tables and have complex relationships between this tables...
my god............

Report message to a moderator

Re: how to move a table's tablespace [message #58409 is a reply to message #58408]

Mon, 25 August 2003 04:46

Vikas Gupta
Messages: 115
Registered: February 2002

Senior Member

Well,

I have not tried this out but someone had adviced me like this:

1. Export the USERS tables.
2. Change the default tablespace of the USER.
3. Ensure that the USER does not have rights on the previous tablespace.
4. Import the tables.

Please try this in your test database only at first.

Regards,

Vikas.

Report message to a moderator