We are cleaning up the last year's transactions from our primary database. We use public database link to delete the records a day by day from another database. Each days records are around 30-40k, but somedays transaction peak to 1 million records. When the delete procedure faces such days, it locks the primary database and causing lock. What would be the ideal solution for clearing the records? We also tried to use alter table truncate partition solution. However, the lock situation is worse in this one. As we clear the database, the new records are being written to the database in real-time. So I cannot restart or increase the distributed_lock_timeout parameter. Any solution is appreciated.

  • What do you mean by locks the primary database?
    – atokpas
    May 13, 2016 at 4:10
  • It means that new records being inserted cannot be inserted, and waits for the delete procedure to finish.
    – babuuz
    May 18, 2016 at 8:55

1 Answer 1


First of all do not use database links for larger transactions. This can cause too many problems with blocking sessions. Another hint is to keep transactions in a good size (not to small, not to big).

If your table is not partitioned then write a piece of code to remove just 1000 rows, commit and delete the next 1000 rows.

You said something about alter table <table_name> truncate partition <partition_name>; Is this table partitioned?

  • Why do you want to delete data from a partitioned table on daily basis? Is the table partitioned the same way?

To archive an entire partition you best exchange it with an empty table of the same structure. Afterwards you can export/backup the table and then drop it. If your partition is in a dedicated tablespace you can also mark it as read only and skip it in the daily backup (enable backup optimization in RMAN).

  • Yes. The table is partitioned based on each month. If I exchange the partition, would it not cause a blocking sessions? The table needs to be writable 24/7, and not block write session.
    – babuuz
    May 19, 2016 at 2:08
  • The table has a dedicated tablespace but cannot make it read only. You said to create an empty table with same structure and exchange partition. When I drop it, will it not affect the other "main" table?
    – babuuz
    May 19, 2016 at 3:00
  • These operations affect only the partition not the entire table. So Oracle does not need to lock the entire table. Please note that all indexes should be partitioned according to the table. Global indexes will be invalid after exchanging the partition. Read more at: docs.oracle.com/database/121/SQLRF/… and oracle-base.com/articles/misc/…
    – o0x258
    May 19, 2016 at 10:33
  • I did as you said. However, global indexes became unusable and the application failed to select from database using global indexes. So i did rebuild of global indexes, everything went to normal again.
    – babuuz
    May 19, 2016 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.