| move tables out of system [message #59212] |
Tue, 04 November 2003 19:54  |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
how can i move tables belonging to a particular user from system tablespace to another TS ,who was by mistake assigned system as the default tablespace?
will that free up any space and any changes in HWM?
|
|
|
|
| Re: move tables out of system [message #59214 is a reply to message #59212] |
Tue, 04 November 2003 20:56  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
U can use ALTER TABLE MOVE command, Export /Import utility and CREATE TABLE AS command to move ur table from one TS to another.
Out of them ALTER TABLE MOVE command, Export /Import utility will reset the HWM.
|
|
|
|
| Re: move tables out of system [message #59216 is a reply to message #59214] |
Tue, 04 November 2003 23:03 |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
i got nearly 300 tables and 400 indexes.
can anyone suggest which is the best method so that i dont have to recreate all the objects depending n these tables?
|
|
|
|
| Re: move tables out of system [message #59218 is a reply to message #59212] |
Tue, 04 November 2003 23:44 |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
So u want to say that u have around 300 tables in ur system TS and now u want to move all of them to some another tablspace. First of all tell me why u have created this no. of tables in system tablespace. It's really not a good approach.
Well now first of all i want to know that r these tables belong to same schema or not?
If these tables r in same schema then just expor the whole schema and import it where ever u want. If not then u have to use table option in exp utility properly.
|
|
|
|
|
|
|
|
| Going through this right now [message #59236 is a reply to message #59212] |
Wed, 05 November 2003 13:16 |
Mark K
Messages: 18
Registered: October 2003
|
Junior Member |
|
|
I'm in the process of a big restructuring job, move objects from one tablespace to another, consolidating, dropping, adding....
Best solution for moving tables between tablespaces: ALTER TABLE ... MOVE...
For indexes:
ALTER INDEX REBUILD ... TABLESPACE .... (and might as well as COMPUTE STATISTICS while you're doing this -- low overhead)
-- Mark K.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
