Oracle, Is there a way to request a lock with alter table drop partition

ddloracle

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.

Best Answer

Just user the ddl_lock_timout feature/parameter (11g+):

alter session                set ddl_lock_timeout=1000000; -- long long time
alter table SCH.DATA_TABLE   drop partition SYS_P1026632 ;

The value for ddl_lock_timeout means seconds to wait before the ORA-00054 is raised or the command completes (which is desirable...). Te default is 0 (which means no wait).

DDL With the WAIT Option (DDL_LOCK_TIMEOUT)