I have legacy code that does the following:
ALTER TABLE A RENAME TO B;
ALTER TABLE C RENAME TO A;
ALTER TABLE B RENAME TO C;
It replaces 2 tables A and C.
Problem: The third DDL change table throws an error:
ORA-00054 RESOURCE BUSY
I donβt understand how is it possible that after the first two DDLs there is a lock? At this point, each transaction should already be completed.
This happens quite often, but not always - sometimes it works, sometimes not.
There is no chance that some other session changed this table data during the swap - firstly, this is a very short operation, secondly, only one table is really used (table A), the second is larger than the archive, so no one does not execute any DML on it. And even if we assume that the unlikely scenario that someone really managed to bind and block is such a short time that I could understand if this happened once, but it happens after every 2-3 swaps.
I have no clue. Is it possible that after renaming a table, old locks are still active?
thank
source
share