Library cache Lock solution case

In the afternoon, the business personnel reported that any operation related to the zzss03201281cs_no table will be executed by Hang, including DESC zzss03201281cs_no, where the Hang will also be executed.

The first thought was the lock. So, let's look at the lock.

SQL> select * from V $ lock where block = 1;

No rows selected

SQL>
SQL> select * From GV $ lock where block = 1;

No rows selected

SQL>

 

Let's take a look at the waiting event:

SQL> Col event for A30
SQL> L
1 * select event, P1, P2, Sid from V $ session_wait where event = 'library cache lock'
SQL>/

Event P1 P2 Sid
------------------------------------------------------------
Library cache lock 1.3835e + 19 1.3835e + 19 32

SQL>/

Event P1 P2 Sid
------------------------------------------------------------
Library cache lock 1.3835e + 19 1.3835e + 19 32

SQL>/

Event P1 P2 Sid
------------------------------------------------------------
Library cache lock 1.3835e + 19 1.3835e + 19 32

...

 

Strange, why are so many library cache locks?

SQL> show user
User is "sys"
SQL> exec dbms_system.set_ev (32,27506, 10046,12 ,'');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00. 10
SQL> L
1 select D. Value
2 | '/'
3 | Lower (rtrim (I. instance, CHR (0 )))
4 | '_ ora _'
5 | P. spid
6 | '. trc' trace_file_name
7 from (select P. spid
8 from V $ mystat M, V $ session S, V $ PROCESS p
9 where M. Statistic # = 1 and S. Sid = M. Sid and P. ADDR = S. paddr) P,
10 (select T. Instance
11 from V $ thread t, V $ parameter V
12 where v. Name = 'thread'
13 and (v. value = 0 or T. Thread # = to_number (V. Value) I,
14 (select Value
15 from V $ Parameter
16 * Where name = 'user _ dump_dest ') d
SQL>/

Trace_file_name
--------------------------------------------------------------------------------
/Ora9i/APP/Oracle/admin/csmisc/udump/csmisc2_ora_2708.trc

Elapsed: 00:00:00. 10
SQL>

 

SQL> select xidusn, object_id, session_id, locked_mode from V $ locked_object;

Xidusn object_id session_id locked_mode
-----------------------------------------
14 35202 31 3
15 18 30 3

SQL> Col object_name format A30
SQL> select owner, object_name, status from dba_objects where object_id = 35202;

Owner
------------------------------
Object_name
--------------------------------------------------------------------------------
Status
-------
Sys
Plan_table
Valid

SQL>

This object is obviously not our concern.

SQL> L
/1 * select owner, object_name, status from dba_objects where object_id = 18
SQL>

Owner object_name status
-------------------------------------------------------------------
Sys OBJ $ valid

This object is involved. It is estimated that the developer unexpectedly exits some processes.

SQL> C/18/30
1 * select serial #, username, command, lockwait, status, schemaname, osuser, machine, terminal, program, module from V $ session where Sid = 30
SQL>/

Serial # username command lockwait status
--------------------------------------------------------------------------
Schemaname osuser
------------------------------------------------------------
Machine
----------------------------------------------------------------
Terminal Program
------------------------------------------------------------------------------
Module
------------------------------------------------
17921 pubuser 0 active
Pubuser report16
Cs_dc02

Serial # username command lockwait status
--------------------------------------------------------------------------
Schemaname osuser
------------------------------------------------------------
Machine
----------------------------------------------------------------
Terminal Program
------------------------------------------------------------------------------
Module
------------------------------------------------
Sqlplus @ cs_dc02 (TNS V1-V3)
SQL * Plus

SQL> select B. Username username, B. Terminal terminal, B. Program program, B. spid
2 from V $ session A, V $ process B
Where a. paddr = B. ADDR and A. Sid = '& SID ';
3 enter value for Sid: 30
Old 3: Where a. paddr = B. ADDR and A. Sid = '& SID'
New 3: Where a. paddr = B. ADDR and A. Sid = '30'

Username Terminal
---------------------------------------------
Program spid
------------------------------------------------------------
Ora9i unknown
Oracle @ cs_dc02 (TNS V1-V3) 835

Apparently, the report16 user performed some DDL operations and then exited abnormally, causing the system lock (it is estimated that it is related to the bug and remains to be verified)
SQL> host
Ora9i @ cs_dc02:/ora9i/APP/Oracle/product/920/rdbms/admin> PS-Ef | grep 835
Ora9i 4619 4617 1 14:48:18 pts/te grep 835
Ora9i 835 1 0 Jan 5? 0: 01 oraclecsmisc2 (local = No)
Ora9i @ cs_dc02:/ora9i/APP/Oracle/product/920/rdbms/admin> kill 835
Ora9i @ cs_dc02:/ora9i/APP/Oracle/product/920/rdbms/admin> exit

SQL> select xidusn, object_id, session_id, locked_mode from V $ locked_object;

Xidusn object_id session_id locked_mode
-----------------------------------------
14 35202 31 3

SQL>

After kill the process, the problem is solved. (Unfortunately, I forgot to check the SQL statement executed by this guy)
SQL> DESC zzss03201281cs_no
Error:
ORA-04043: Object zzss03201281cs_no does not exist

SQL> DESC zzss03201281cs_no
Error:
ORA-04043: Object zzss03201281cs_no does not exist

SQL>

SQL> exec dbms_system.set_ev ,'');

PL/SQL procedure successfully completed.

SQL>

View the trace file ,:

A lot of wait:

Wait #1: Nam = 'library cache lock' Ela = 316 p1 =-4611686013647472824 P2 =-4611686013691747544 P3 = 1301
Wait #1: Nam = 'library cache lock' Ela = 326 p1 =-4611686013647472824 P2 =-4611686013691747544 P3 = 1301
Wait #1: Nam = 'library cache lock' Ela = 398 p1 =-4611686013647483736 P2 =-4611686013691747816 P3 = 1301
Wait #1: Nam = 'library cache lock' Ela = 552 p1 =-4611686013647483736 P2 =-4611686013691747816 P3 = 1301
Wait #1: Nam = 'library cache lock' Ela = 330 p1 =-4611686013649700264 P2 =-4611686013691715248 P3 = 1301
Wait #1: Nam = 'library cache lock' Ela = 141 p1 =-4611686013649700264 P2 =-4611686013691715248 P3 = 1301
Wait #1: Nam = 'library cache lock' Ela = 223 p1 =-4611686013647485472 P2 =-4611686013691762016 P3 = 1301
Wait #1: Nam = 'library cache lock' Ela = 93 p1 =-4611686013647485472 P2 =-4611686013691762016 P3 = 1301
Wait #1: Nam = 'library cache lock' Ela = 223 p1 =-4611686013595934816 P2 =-4611686013642107320 P3 = 1301