I have one Database running on 10.2.0.4 EE. This DB has grown very fast in last few months because of loads of loads of data being populated into it.
Few weeks back we purged some big tables from a tablespace roughy 700GB data was purged. We did truncate of some few tables. After this we tried to recover the space from tablespace which is reflecting it has 100% free space but when we check how much we can reclaim we saw most of the objects /segments are lying just before HWM. Due to this we are not able to recover the space from datafiles and as well from file system. File systems are giving alerts .
Please help to fix this issue.
I would be happy some one can start providing solutions as I have tried almost everything but no success.
Waiting for some quick responses.
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.
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?