RA-00054 of oracle: resource busy and acquire with NOWAIT
1. Truncate report ORA-00054, indicating that a transaction is operating the table.
SQL > truncate table alldm.DM_XQKD_YUJING_D
Truncate table alldm.DM_XQKD_YUJING_D
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2. Find the session and sql that are operating the table
SQL > set linesize 400
SQL > set pagesize 400
SQL > col object_name for A40
SQL > select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects where object_name='DM_XQKD_YUJING_D'
OWNER OBJECT_NAME OBJECT_ID
-
ALLDM DM_XQKD_YUJING_D 7525915
SQL > select SESSION_ID,OBJECT_ID from v$locked_object where OBJECT_ID = '7525915'
SESSION_ID OBJECT_ID
--
2226 7525915
SQL > set linesize 400
SQL > set pagesize 400
SQL >
Select sql_text from v$session a minute vandalism sqltextbook withdrawing newlines b
Where DECODE (a.sql_hash_value, 0, prev_hash_value, sql_hash_value) = b.hash_value
And a.sid=&sid order by piece
Enter value for sid: 2226
Old 3: and a.sid=&sid order by piece
New 3: and a.sid=2226 order by piece
SQL_TEXT
INSERT / * + APPEND*/ INTO DM_XQKD_YUJING_D SELECT / * + ORDERED*/: B2
, A.AREA_NO, A.CITY_NO, A.XIAOQU_NO PLOT_ID, A.XIAOQU_NAME PLOT
3. Find the os process of the session
Select a.username
A.sid
A.serial#
B.spid "OS Process"
To_char (a. Logoncake time.mlxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (to_char) "Logon time"
A.osuser
A.program
A.status
From v$session a, v$process b
Where a.sid = & sid
And a.paddr = b.addr
/
USERNAME SID SERIAL# OS Process Logon time OSUSER PROGRAM STATUS
-
ALLDM 2226 28311 76949 13/01/2018 06:04:24 bca JDBC Thin Client ACTIVE
4 、 kill session
SQL > alter system kill session '2226,28311'
System altered.
5. Verify whether the process has been kill at the os level
Oracle@hbdw1:/oratmp$ps-ef | grep 76949
Oracle 11057 116412 0 16:21 pts/2 00:00:00 grep 76949
6. Truncate is successful again
SQL > truncate table alldm.DM_XQKD_YUJING_D
Table truncated.
