This content has been marked as final. Show 7 replies
Why not ALTER TABLE ..tablename.. MOVE and ALTER INDEX ..indexname.. REBUILD commands ? If the current segments are at the datafile (HWM) boundaries and space below them is free, the MOVE and REBUILD commands should try to reuse the free space below.
Hemant K Chitale
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.
You could use DBMS_REDEFINITION to move the table "online". However, you would lose the time required to rebuild (i.e. recreate) the indexes.
Hemant K Chitale
I am thinking about Export/Import For a complete reorganization and space reclamation.
Production Tablespace Name : PROD_ARCH1 with 48 datafiles.
If I create another tablespace PROD_ARCH2 and take export of PROD_ARCH1 and import into PROD_ARCH2. Would it help?
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.
Then either follow Hemant's suggestion from earlier on for alter table move followed by an index rebuild - you can build a script easily to do that.
or build up a datapump query that only datapumps out objects in that particular tablespace and leaves everything else alone?