1. Go away and have a cup of coffee, then try again. If you are lucky, you can do this time.
2. Kill the session that locks database objects you want to modify. You do not have to wait but users may not be too happy about it.
3. Quiesce database by “ALTER SYSTEM QUIESCE RESTRICTED”. Wait until all active transactions are completed. So you can modify objects as DBA. However, you may wait a long time for changing database into quiesce state if some long transaction is running. Additional, it will block all user sessions, not just one that locks your objects.
4. You can brute force the DDL on locked objects by simple PL/SQL, which I found from DBA Tools web site (http://www.dbatools.net/experience/oracle_resource_busy.html).
declare
resource_busy exception;
pragma exception_init (resource_busy,-54);
begin
loop
begin
execute immediate 'ddl sql …';
exit;
exception
when resource_busy then
dbms_lock.sleep(0.01);
end;
end loop;
end;
It works pretty well for me. Have a spare CPU and a cup of hot coffee before you run the script.
For 11g database, you can make ddl to wait by setting ddl_lock_timeout to number of seconds to wait before getting ORA-054 error. During the waiting time, Oracle tries to acquire the object lock needed until time out. No PL/SQL coding is necessary.
