This content has been marked as final. Show 7 replies
Thanks for quick reply. The table space consists of 48 data files each one is around 32GB or less. Most of the tables are partitioned tables so I am wondering if I can start moving them using dynamic scripts to different tablespaces(if possible)...First those tables will not be accessible during that time period secondly i have to re-build all the indexes.
Count of table is very high so time constraint is another factor which is stopping me.
If you can get an outage for a full expdp/impdp i'd go that route. Drop and recreate the same tablespace name after the export has finished and then reimport back into the empty space (you might want to shrink the datafiles before you do that or you could do it afterwards).
Before you sart though - Is the schema the only schema owner using that tablespace and are all objects it owns in that tablespace?
If you've not done the process before check in a test environment first just in case there are any strange issues.....
Harry , if you look at below snapshot , you will see 100% space free in table space and this table space is currently being used by 91 distinct schemas.
Tablespace Used MB Free MB Total MB Pct. Free
---------------------- ----------- ----------- ----------- ----------
SYSTEM 8,447 2,553 11,000 23
APPS_TS_ARCHIVE 7,416 1,529,321 1,536,737 100
full export and import is not possible as db sizeis 4.4 TB.