I wanted to know that If I archive old data on the basis of date coulmn and remove the old data from the concern tables then it would be possible to re-claim the space or not.(archive/drop/export/import)

When I remove these records then it would provide free space at the object level and in the beginning of file but still remain there (in the datafile in the beginning of datafile)

I will use the export and import for extracting required (remaining) data and populating back data to the concern tables to re-claim the space and re-organise table including index.

I will re-size option of datafile also by specifying the follwing statement

ALTER DATABASE DATAFILE FILENAME RESIZE SIZE

I will be doing this exercise for few tables as a first step. There are so many other tables are availbale in the schema.

I wish to know about the follwing comment also.

To guarantee to be able to shrink the datafiles would be to re-organise ALL the objects left in the tablespace after the archiving exercise so that their data is moved to the beginning of the datafiles so they can be shrunk OR move ALL the objects left in the tablespace after the archiving exercise into another new tablespace so the old one can be dropped.

ALTER TABLE ALTER TABLE tablename MOVE…., ALTER INDEX indexname REBUILD,etc., type commands

Please comment it also on the following statement too.

This will be required for ALL remaining objects in the old tablespace along with a re-ANALYZE of any objects with statistics.

My main concern is to re-claim the space and improve performance by shrinking database.

I will appreciate if you can send your suggestion/comment/opinion about it.

Please let me know if thing could not describe in a proper way.