Big Tables Purging

All posts relating to Oracle database administration.

Moderator: Tim...

Posts: 3
Joined: Tue Mar 13, 2012 5:25 am

Big Tables Purging

Postby mahi626 » Fri Sep 14, 2012 6:38 am

Hi Tim,

In my Production database 3 big tables are have millions of records . One of the table inserts per day around 50k+ records into table and presently its hold the 300 days data, and its take ~4 mins to delete 1 day data. These tables are without partition and having one date column. Need to enable purge policy as daily basis and maintain 30 days datain all 3 tables. DB should be 100% availability no down time. Please suggest me better and safe solution.

Thanks in Advance.

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Big Tables Purging

Postby Tim... » Fri Sep 14, 2012 7:47 am


You really don't have many options.

1) Switch to partitioning so you can drop a partition.
2) Use DBMS_REDEFINITION to do an online table redefinition, effectively creating a new table as a select of the rows you want to keep.

That's about it.

Large scale deletes/updates are really inefficient.


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 2 guests