Deleting millions of records 4

[torturedmind]

Good day to all.

We have a problem with one of our tables. It contains millions of records because it acts as a form of audit trail. We already have a back-up of some of the old records and now we want them to be deleted to free up space. The problem is we can't delete these records because one of the undo tablespaces tend to get full as we do so. Even if we try to delete by interval of hours (using the audit time stamp field), it still gets full. There are currently two (2) undo tablespaces in our 10g DB. One is 16GB and the other is 32GB but only the 16GB gets full and the deletion won't proceed. Question is, how can we effectively delete these by utilizing the two undo tablespaces?

TIA

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[taupirho]

create new_audit_table as select the_records_you_want_to_keep from old_audit_table

drop old_audit_table

rename new_audit_table to old_audit_table

recreate indexes etc... on old_audit_table

In order to understand recursion, you must first understand recursion.

 

[Beilstwh]

You beat me to it. Exactly what I would suggest.

Bill
Lead Application Developer
New York State, USA

 

[torturedmind]

Great. Thanks. A star for you.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[SantaMufasa]

The problem that I see with the solution above, is that if you have any GRANTs on the old_audit_table, they disappear coincident with the DROP statement.

You can solve this issue by either doing a TRUNCATE <table> instead of a DROP <table>, followed by an INSERT from the new_audit_table, or preserve the GRANTs on the table (in a script) prior to running the script, above, then re-GRANT after the script, above, completes.

Mufasa
(aka Dave of Sandy, Utah, USA)
ôPeople may forget what you say, but they will never forget how you made them feel.

 

[garani]

Hi

Just a note for speeding up the process

1. When creating a new table you can give UNRECOVERABLE OPTION to bypass the redologs

I am assuming the DBA's has export dump of the old_audit_table

create new_audit_table as select the_records_you_want_to_keep from old_audit_table UNRECOVERABLE


2. Similarly when inserting into new table after truncate use APPEND HINT and PARALLEL option

INSERT INTO /*+ APPEND */new_audit_table SELECT /*+ PARALLEL(old_audit_table,4) FROM old_audit_table

garan

 

[Beilstwh]

An additional change you can make for the future. If you change the audit table to a partitioned table then to get rid of old records, you would simply drop the old partition and they are immediately gone.

Bill
Lead Application Developer
New York State, USA