This article gives you a comprehensive understanding of Java object serialization and deserialization>>>

ORA-00054:resource busy and acquire with NOWAIT specified or timeout expired

Cold embarrassment

2019-04-09 21:46:18

six thousand eight hundred and eighty-one

Collection

one

1、 Fault description:

I got a question from a developer in the morning, truncate table t_ USER_ In the label table, an error is reported: ora-00054: resource busy and acquire with nowait specified or timeout expired, as shown in the figure below. Literally, resources are busy and occupied. Processing idea: find out who is occupying the resource, and kill the session occupying the resource. The possible reasons are: 1. The lock generated when creating index 2. The lock generated by DML statement 3. The lock generated when adding keyword online when creating index. Solutions: 1. Wait for other sessions to release resources. 2. Find the session that occupies resources and delete it. 3. Restart the database. Of course, only the second one is more suitable

error in PL SQL:

Error in sqlplus:

REPORTUSER@test > truncate table t_ user_ label;

truncate table t_ user_ label
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

2、 Fault handling
1. Check which user occupies the resource_ USER_ LABEL

SYS@test > select l.session_ id,o.owner,o.object_ name from v$locked_ object l,dba_ objects o where l.object_ id=o.object_ id;

SESSION_ ID OWNER OBJECT_ NAME
———- —————————— ————————————————–
2205 REPORTUSER T_ USER_ LABEL
76 REPORTUSER T_ USER_ LABEL
1849 REPORTUSER T_ TEMPORARY_ CHANNEL_ Customer
2_ USER_ Session of label_ ID to find out more detailed information
Click (here) to collapse or open

SYS@test > SELECT sid, serial#, username, oSUSEr, terminal,program ,action, prev_ exec_ start FROM v$session where sid = 2205;

SID SERIAL# USERNAME OSUSE TERMINAL PROGRAM ACTION PREV_ EXEC_ START
———- ———- ———— —– ———- ————— —————————— ——————-
2205 3045 REPORTUSER huhw PTYY-003 plsqldev.exe – procedure P_ USER_ LABE 2016-06-29 11:31:02

SYS@test > SELECT sid, serial#, username, osuser, terminal,program ,action, prev_ exec_ start FROM v$session where sid = 76;

SID SERIAL# USERNAME OSUSE TERMINAL PROGRAM ACTION PREV_ EXEC_ START
———- ———- ———— —– ———- ————— —————————— ——————-
76 1677 REPORTUSER huhw PTYY-003 plsqldev.exe SQL – ? 2016-06-29 14:00:54

3. According to the SID found above, serial?Kill the occupied resource t_ USER_ Two sessions of label

SYS@test > alter system kill session ‘2205,3045’;

System altered.

SYS@test > alter system kill session ‘76,1677’;

System altered.

4. Run truncate table again successfully

REPORTUSER@test > truncate table T_ USER_ LABEL;

Third, summarize the problem review. According to the above query information, two sessions occupy t_ USER_ Label table resources, and you can see that it is executing a procedure with the name P_ USER_ Labe. After viewing the procedure, the statement inside queries other table information, and finally inserts t_ USER_ The data in the label table was forcibly interrupted when the procedure was executed at noon yesterday after the parties were asked. If the transaction is not committed or rolled back, the resource will be occupied all the time. In this case, learn how to use V $locked_ Object and DBA_ Objects to find out which session occupies an object, and use V $session to find out the specific information of the session, including SID, serial #, how to connect, what is being executed, and so on. Finally, alter system kill session “SID, serial” is used; To kill the session that occupies resources