-
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
goodhealth
-
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'
Jeff Hunter
-
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
Javier Cullas
Master Oracle
Enterprise DBA - Networked &
Distributed DBA Specialist
http://education.oracle.com/latinamerica/masters_cert/mt.names.html
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|