SQL> truncate table sys.login_log; truncate table sys.login_log * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired |
Why am I getting ora-00054 error when I truncate a table?
The table i truncate is login_log,which record the information of each session connect to database.for example, session A connect to database, the table login_log should be updated.
So,when I was trying to truncate login_log,it was already locked by some session or some query by "select for update" and have not yet committed/rollbacked.
Why the ddl operation have to wait the dml lock?
oracle said that "DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.
If a lock is not acquired before the timeout period expires, then an error is returned.
"
What can we do?
Look up the sql,username,machine,port information and get to the actual process which holds the connection, and then kill them.
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM, S.USERNAME, S.MACHINE, S.PORT, S.LOGON_TIME, SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS; |
Maybe you are going to wait some minutes, The session that was killed was in the middle of a transaction and updated lots of records. These records have to be rollbacked and some background process is taking care of that.
Another word,When you killed the session, the session hangs around for a while in "KILLED" status while oracle cleans up after it.
If urgent,you can also kill the os process as well (look up the process id from v$process.spid), which would release any locks it was holding on to.
From the internet,i find a script that could take this error just one step
BEGIN FOR C IN (SELECT S.SID SID, S.SERIAL# SERIAL FROM V$LOCKED_OBJECT L, V$SESSION S WHERE L.SESSION_ID = S.SID) LOOP EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || C.SID || ',' || C.SERIAL || ''''); END LOOP; END; |
