We help IT Professionals succeed at work.

purge deleted record

glogin asked

How to purge the deleted record in an oracle table?
(I want to purge all deleted records and release the space but retain the un-delete records)
Watch Question

Say that your table is called x_tab.

create table c_x_tab as
select * from x_tab
truncate table x_tab
insert into x_tab
(select * from c_x_tab);

This works well if you have no foreign keys referring to x_tab. If you happen to have any foreign keys referring to x_tab, then you should first disable them, and enable them after the insert.
This is using plain SQL.
Or, you may export your table (using exp on some Unix or exp80.exe in Windows), then truncate, then import what you exported (using imp or imp80.exe on Windows). There will a bigger problem if you have foreign keys referring to that table.


is there any oracle command or sql can purge the deleted record directly?
Mark GeerlingsDatabase Administrator

That depends on what you mean by "purge".  The Oracle delete command does actually remove the deleted data from the data blocks and make the space available for new records to be added in that table, so in a sense there is nothing left to purge.

If your concern is how to recover that free space, and use it for a different table, or to just reduce the total size of the table where you did the deletes, then you have to follow the suggestions that bogdincescu gave you.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.