Scenario 1:
If the jobs are running too frequently, it is possible the queries are not releasing the lock and resulting in the error: “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.
DDL statements must obtain the library cache lock in order to perform DDL, so an explicit DDL lock timeout should be set for the DDL to wait for the object to become available.
sqlplus "/ as sysdba"
alter system set ddl_lock_timeout=60 scope=both sid='*';
60 is a starting value, one can go higher depending on how long the ddl statements are taking to complete in the environment.
Note: Test in your development environment before moving to production enviornment.
Scenario 2:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
This is a database level warning that some objects are already locked by other process when the process started, so once the lock it released it should be fine to continue.
Below query run as a DBA user from SQL*Plus or SQL*Developer may pinpoint the resource it is waiting on:
select s.sid, s.serial#, p.spid
from
v$session s,
v$process p
where
s.paddr = p.addr
and
s.sid in (select SESSION_ID from v$locked_object);
This may be helpful to find the holder:
Select o.object_id, o.owner, o.object_type
from dba_objects o
where object_name = 'TEMP_INT_ACCS_SUPTOS38543';
select l.inst_id id, l.sid, s.serial#, s.username, s.machine, S.program, l.id1,
decode(l.lmode, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', l.lmode) lmode,
s.sql_id, s.prev_sql_id, s.event, s.last_call_et
from gv$lock l, sys.all_objects o, gv$session s
where o.object_id(+) = l.id1 and l.sid = s.sid and l.inst_id = s.inst_id
and o.object_id = 632729
order by object_name, lmode;
set long 500000
Select inst_id, sql_fulltext
from gv$sqlarea
where sql_id = 'ajzrm2ru58fsc';
select T.status t_status, T.start_time, T.recursive "Recur", S.inst_id id, S.sid, S.serial#, S.username,
substr(s.osuser, 1, 15) osuser, substr(s.machine, 1, 15) machine, substr(s.program, 1, 15) program, s.status s_status,
T.used_urec "Urec", T.used_ublk "Ublk", s.sql_id
from gv$transaction T, gv$session S
where T.ses_addr = S.saddr and T.inst_id=S.inst_id
and s.serial# = 65073
order by T.start_time;
Solutions and Workarounds:
Typically, you need to determine what is holding the lock that blocks the process that raises the ORA-00054, then establish whether it should be holding the lock or not. Equally, look at whether it is necessary to be requesting the lock at that time.
1) Avoid performing DDL at the same time as DML is occurring.
2) Is it possible for the DML to wait for the resource to be freed. In other words, check if it is necessary to use NOWAIT when executing the DML?
3) If the timeout is being exceeded, why is the resource being held so long? Is this realistic? Is there something wrong with the process holding the resource?
