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

Question

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.

Answer 1

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)

Answer 2

In this case, requesting a table/partition lock appears the best solution to me, as long as you ensure that the lock is released immediately after, i.e. execute some DDL, commit or rollback immediately when the lock is acquired. The risk is that lingering locks will unduly block other users wanting to use the resource, which is minimized when all commands are gathered in one script that is executed as a whole. You can also wrap them up in a PL/SQL block and execute that.

The preferred option would be to use the LOCK command with the WAIT option to lock the partition that you want to drop. This does not impact processing in other partitions than the one you are working on. You need to specify a time (in seconds) to wait.

lock table xyz partition( a_part ) in exclusive mode wait 9999;
alter table xyz drop partition a_part;

Since you are dropping a partition, presumably the new data is stored in a different partition, so the partition lock will not incur a wait at all.

If you have global indexes on the table, the story changes as they are invalidated and must be rebuilt after the drop. The wait may happen on aquiring a lock on the index, rather than the partition and you will need to take out a lock on the entire table.

lock table xyz in exclusive mode wait 9999;
alter table xyz drop partition a_part;

As the ALTER TABLE command is DDL and hence closes the transaction, any locks should be automatically released after the ALTER TABLE finishes, so you will need to take out a lock for each partition that you need to drop. There is no need to manually release the lock.

I'm not sure from the question if the ORA-54 is only encountered when dropping the partition, or also when attempting to issue DELETE statements. Since the rows to delete are identified in a (presumably) scripted process, the easiest way to lock those rows is to add "FOR UPDATE OF <column-name> WAIT" to the end of the select statement that identifies those rows. This will lock the rows in question.

To complete the story, the commit statement also accepts a WAIT clause. Be aware that all locks are released upon commit, including those set by the SELECT ... FOR UPDATE statement.