When adding column in out system, I always get resource busy (ORA-00054) errors. If I cannot add it after a lot of retries (we have script to do this). I will try to find out the lock owner, and get to know what's the session doing.
ASQL> ALTER TABLE T1 ADD COL5 VARCHAR2(10);
ORA-00054: resource busy and acquire with NOWAIT specified
I prefer to use AnySQL to do this task, because there are embeded command to do it, first step is to get the object id.
ASQL> list object t1
TYPE ID OWNER OBJECT_NAME CREATED MODIFIED STATUS
----- ----- ----- ----------- ---------- ------------------- ------
TABLE 11896 SH T1 2007/12/14 2007/12/14 20:54:13 VALID
1 rows returned.
Then get the lock owners (sessions) that have lock on this table.
ASQL> ora hold 11896
SID SERIAL# SPID USERNAME MACHINE STATUS PROGRAM
--- ------- ---- -------- ---------------- -------- -----------
44 5 2064 SH CORP\L-SHC-FLOU2 INACTIVE sqlplus.exe
1 rows returned.
Usually it's a long run data purging process blocked my session. So I just kill it.
ASQL> ALTER SYSTEM DISCONNECT SESSION '44,5' IMMEDIATE;
Alter System Succeed.
Before I start to create index on some hot table, I will check how many sessions have locks on it first, if too much sessions hold locks on the target table, I will just ask for a database bounce to create it to avoid get ORA-08104 errors.