Oracle Community Network
I have table with the huge number of records, but after i delete some records in the table the table space used by that table remains the same.
Please let me know how we can free the table space after delete command to increase the performance?
Its a very common problem and bread n butter stuff for the DBAs :)
Generally in OLAP environment where DML operations are performed in BULK.
Its requires tuning of the tablespace at datafile level.
In you scenario you can perform following steps to free up the unused space from the tablespace.
1. ALTER table <tablename> move;
2. Rebuilt all indexes on that table;
By performing this you will see that there is spike in the free space for that tablespace.
You can check the dictionary view dba_free_space before and after the above operation for your tablespace.
Alternatively in case you are deleting all the records from the table I would advice you to go use Truncate command rather than DELETE statement.
MOVE is not a good idea: massive downtime. ALTER TABLE...SHRINK SPACE is the way to do it.