An example of Oracle lock table fault handling

A developer directly on the database server to do the delete table operating system, from the time of the peak operation, resulting in a long time to execute unresponsive, and then shut down the Sqlplus client, run the DELETE statement again, causing the database to run slowly.
Ask the operation process to know is caused by the lock table, the final processing; This article just simulates the lock table process, the next time you encounter the same problem can be quickly processed.

1. View the database's Wait events

If you don't know why, you can analyze it by waiting for the event first.

sql> Select Sid,event from v$session_wait where wait_class<> ' Idle ';

       SID EVENT   
--------------------------------------------------------------------------  &NBSP;
       158 sql*net message to client   &NBSP;
       159 Enq:tx-row lock contention     
SQL>

2. See if the database has locks

Sql> SELECT * from V$lock where block=1;

3. Search lock lock to see who locks who

Description: The row of Block 1 indicates that the resource is locked by it. Request indicates the need for this lock.

sql> SELECT Sid, Id1, Id2, Lmode, block,request, type
From V$lock
where Id1 in (SELECT id1 from v$lock WHERE lmode = 0)
ORDER by ID1, request;

SID ID1 ID2 lmode BLOCK REQUEST TY
---------- ---------- ---------- ---------- ---------- ---------- --
153 655397 308 6 1 0 TX
157 655397 308 0 0 6 TX

4. Query the sid,serial# of the lock table, the user, the start time, and then you can kill the process.

Sql> Select T1.sid,t1.serial#,t1.username,t1.logon_time from v$session t1,v$locked_object t2 where t1.sid = t2.session_id ORDER by T1.logon_time;

SID serial# USERNAME Logon_time
---------- ---------- ------------------------------ -------------------
153 ABC 2014-11-17 09:19:33
157 106 ABC 2014-11-17 09:54:24

5. Can further query the sid,spid and other information

sql> Select S.username, S.osuser, S.sid, s.serial#, P.spid, S.PROGRAM,S.STATUS&NBSP;&NBSP;
from v$session s,v$process p  
where s.paddr = p.addr and S.username is not null; 

USERNAME osuser SID serial# SPID Program STATUS
------------------------------ ------------------------------ ---------- ---------- ------------ ------------------- ----------------------------- --------
ABC Oracle 153 4290 [email protected] (TNS V1 -V3) INACTIVE
SYS Oracle 4417 [email protected] (TNS V1 -V3) ACTIVE
ABC Oracle 157 119 4830 [email protected] (TNS V1 -V3) ACTIVE

Either kill it in Oracle, kill it under the operating system, and, for a statement with status killed, apply the OS kill command in order to get the most out of it.

Alter system kill session ' 153,12 ';
Alter system kill session ' 157,119 ';

Or in the operating system

Kill-9 4290
Kill-9 4830

6. Querying the SIDs of locked tables and related resources

Description: Out of the locked table and SQL, or to combine the above query for manual processing, you can also find the SQL statement through the SID.

Set pagesize 999;
Set Linesize 200;
Col program for A25;
Col TERMINAL for A10;

Sql> Select S.status,s.sid,s.serial#,p.spid,
S.last_call_et as exec_seconds,t.sql_text as Curr_sql
From Gv$session s,v$process p,v$instance i,v$lock K, V$sqltext t
where s.paddr = P.addr
and s.type! = ' BACKGROUND '
and s.lockwait = K.kaddr
and S.sql_hash_value = T.hash_value
And s.username is not null;
STATUS SID serial# SPID exec_seconds curr_sql
-------- ---------- ---------- ------------ ------------ ----------------------------------------------------------- -----
ACTIVE 159 4072 1285 update test_lock set name= ' AA4 ' WHERE id = 1

Sql>

An example of Oracle lock table fault handling