INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Deleting millions of records

Deleting millions of records

(OP)
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 knight
philippines

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

RE: Deleting millions of records

2
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.

RE: Deleting millions of records

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

Bill
Lead Application Developer
New York State, USA

RE: Deleting millions of records

(OP)
Great. Thanks. A star for you. thumbsup2

kilroy knight
philippines

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

RE: Deleting millions of records

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.

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

RE: Deleting millions of records

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

RE: Deleting millions of records

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close