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.
Since you didn't specify your version and platform, I'm assuming 126.96.36.199 and Solaris.
ALTER TABLE abc.xyz MOVE TABLESPACE new_ts
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'
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
Enterprise DBA - Networked &
Distributed DBA Specialist
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
Click Here to Expand Forum to Full Width