Move tables/objects to different tablespace

[goodhealth]

Hi,

Anyone have scripts or instructions on how to easily move tables/objects from say SYSTEM tablespace to a different tablespace?

We have some objects in SYSTEM tablespace that belong to non-system user. I have read some notes from Metalink but it is sort of complex to do.

Thanks

[marist89]

Since you didn't specify your version and platform, I'm assuming 8.1.7.2 and Solaris.

Use:

ALTER TABLE abc.xyz MOVE TABLESPACE new_ts

and

ALTER INDEX abc.xyz_pk REBUILD TABLESPACE new_ts

To make this easier, you could write some SQL that generates a SQL script for you. Something like:
SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' MOVE TABLESPACE New_Ts;' FROM DBA_TABLES
WHERE OWNER = 'THE_OWNER_OF_THE_OBJECT'

[jcullas]

Hi Friend:
I agree with the option ALTER TABLE MOVE, but with it we can not work if the table has one field type LONG.

In this case you must make an export, then create the table in the tablespace ( without data) and then import the data

[pando]

the problem is not we cant work if we move a table with long column, the problem is if you try to move a table with long column you get a ORA- error

The other option (for LONG column) would be create an empty table in the tablespace you want and use sql*plus copy command and copy over the data from old table to new table since this command does support LONG