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.

  • I am surprised I did not add in the original question that we are adding the masses of data using sqlldr. I discovered that I could lock the tables for the purging of old data but that meant that sqlldr failed and requeuing the data is a big pain for our environment. My solution is to use Unix semaphores to coordinate access to the tables. It is painful and requires external software but it means things work when they did not before. – user1683793 Nov 11 '19 at 17:56

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


| improve this answer | |
  • A bit of an update... except it does not seem to work. I have a sequence of sqlldr jobs running on one RAC node at the same time as my partition cleanup is trying to happen on another. I put in "alter session set ddl_lock_timeout=900" before the alter table and the alter table waits 900 seconds before aborting while the sqlldr jobs run one after the other without slowing down. Any ideas? – user1683793 Aug 7 '18 at 22:51

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.

| improve this answer | |
  • I have this running with a combination of this solution: "lock table xyz partition( a_part ) in exclusive mode wait 900;" and the other solution. I expect I don't need the other, I just have not removed it as yet. Now my loads are breaking if they start while the lock request is waiting but that is another problem. – user1683793 Aug 8 '18 at 16:59
  • No, if you are acquiring an exclusive lock first, then there won't be a wait for the ddl lock and the timeout is of no consequence. – Tony Aug 10 '18 at 0:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.