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.
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.
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.....