OracleConnections

Oracle Community Network

How to free tablespace after delete command.?

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?

0 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by Teddy Blanchette on June 19, 2013 at 12:29pm

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.