Hi,
I am using Oracle Database 11.2.0.4.
After read some documentation on how to prevent unwanted DDL statement to table,
I found a way to do so by by issuing 'alter table <table_name> disable table lock'.
The disable does works, however I cannot re-enable it.
The error message is :
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
I want to enable the lock because I need to add new columns to that table.
I have been digging on several threads which have same issue as mine. But none of them give any acceptable solution.
Can't enable table lock after disablng it
alter table enable table lock hang
However one of the poster reply by quoting the documentation which says : (note : I cannot find which doc says so)
"Specify ENABLE TABLE LOCK to enable table locks, thereby allowing DDL operations on the table. All currently executing transactions must commit or roll back before Oracle Database enables the table lock."
By the way :
1. The table is mine and nobody knows it exists (therefore it would be only me who likely do any DML to it, and I did not).
2. I disabled the table lock months ago and tried to re-enable it because I want to add new column to the table.
Does it means I need to wait until "ALL currently executing transactions" happening on the current schema being committed or rolled back?
The table itself is located on production schema on which transactions occur continuously and never stops.
Is it true that Oracle waits all executing transactions? Is it known bug?
Why would Oracle waits every transactions which does not have anything to do with the underlying table?
Is there any workaround to resolved this?
Note :
I have tried disable - enable table lock on development database (on which has less transaction) and it works just fine.
Regards,
Buntoro