ORA-02049 Timeout: Distributed Transaction Waiting for Lock Solution needed

Question

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.

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