We have an Oracle RAC cluster with about 100 tables that continually receive data. All tables have a timestamp field and the data is partitioned by date. Stopping the receipt of the data is not an option.
We have a job that runs every few hours, figures out what partitions correspond to old data that can be deleted and runs
alter table SCH.DATA_TABLE drop partition SYS_P1026632 ;
The good news is most of the time, we can drop the partitions and delete the required data. The bad news is occasionally, the alter table happens at the same time as we are receiving new data and we get an error:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
I have been experimenting with checking to see if there are any locks on the table before running the drop partition but looping, waiting until the table is unlocked does not seem like a very good solution. Plus there is no guarantee that I will actually get in before another process gets the lock.
I have considered requesting a lock on the table, something like
lock table SCH.DATA_TABLE in EXCLUSIVE mode;
and when I get the lock, dropping the partition and releasing the lock but that does not seem like a very good solution either.
I was kind of hoping for an
alter table wait for lock
or something but that does not exist (alter table locks but I need it to wait for the lock rather than just giving up right away.)
Is there a good way to go about this? Is my best bet just to lock/alter/release?
Thank you for any input.