Alter table exchange partition by holding lock on the table.
Hi Everyone,
I have one table which is partitioned on date. From my application I want to delete 90% of data from some selected partitions and insert some more data into those partitions. Since delete takes longer time I am creating a temporary table same as main table without partitioning. I will insert 10% of the data from main table which is not required to be deleted and load new data into temp table. Then I am exchanging the partition of main table with temp table.
But here i might have a situation, while i am doing exchange partition there might be some data load happening on the main table and on the same partition. That time if I try to exchange partition some times it will through below given error
ORA-00054: resource busy and acquire with NOWAIT specified
I know I can synchronize data load and my exchange partition using lock from the application but I don't want to do that. Without doing any changes in data load part of my application I want to handle this using some oracle provided solution.
If you know any other solution please suggest me.
Thank you in advance.