In the afternoon, business staff reported that any operations related to the ZZSS03201281CS_NO table will live, including DESC ZZSS03201281CS_NO, will also hang there.
The first feel is to lock, so I look at the lock
SQL> SELECT * FROM V $ loc WHERE block = 1;
No rows selected
SQL> SQL> SELECT * FROM GV $ LOCK where block = 1;
No rows selected
SQL>
Look at the wait event:
SQL> Col Event for A30SQL> L 1 * SELECT, 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, how so many library cache lock?
SQL> Show useruser is "SYS" SQL> EXEC DBMS_SYSTEM.SET_EV (32, 27506, 10046, 12, ');
PL / SQL Procedure SuccessFully Completed.
ELAPSED: 00: 00: 00-10SQL> 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 V $ MyStat M, V $ Session S, V $ Process P 9 where m.statistic # = 1 and s.SID = M.SID AND P. Addr = spaddr) 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') DSQL> / TRACE_FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------- / ORA9I / App / Oracle / Admin / CSMISC / UDUMP / CSMISC2_ORA_2708.TRC
Elapsed: 00: 00: 00.10sql>
SQL> SELECT Xidusn, Object_ID, Session_ID, Locked_Mode from V $ locked_OBJECT
Xidusn Object_ID session_id lock_mode ---------- ------------------ ----------- 14 35202 31 3 15 18 30 3
SQL> Col Object_name Format A30SQL> SELECT OWNER, Object_Name, Status from DBA_Objects Where Object_ID = 35202;
Owner -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------ Status ------- Sysplan_TableValid
SQL>
This object is obviously not our attention.
SQL> L / 1 * SELECT OWNER, Object_Name, Status from dba_Objects where object_id = 18sql>
Owner object_name status --------------------------------- ------------- ------- sys obj $ valid is this object, it is estimated that developers have abnormal exits some process
SQL> C / 18/30 1 * SELECT Serial #, Username, Command, Lockwait, Status, Schemaname, OSuser, Machine, Terminal, Program, Module from v $ session where sid = 30sql> /
Serial # username Command LockWait Status -------------------------------------- ---- ------ ---------------- -------- Schemaname Osuser ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------- Terminal Program --------------------------------------- ---------------------------------------------------- ------------------------------------ 17921 Pubuser 0 ActivePubuser Report16CS_DC02
Serial # username Command LockWait Status -------------------------------------- ---- ------ ---------------- -------- Schemaname Osuser ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------- Terminal Program --------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ SQLPLUS @ CS_DC02 (TNS V1-V3) SQL * PLUS
SQL> Select B.username Username, B.Terminal Terminal, B.Program Program, B.SPID 2 from V $ sessions a, v $ process bwhere a.paddr = b.addr and a.sid = '& sID'; 3 Enter Value for SID: 30OLD 3: WHERE A.PADDR = B.Addr and a.sid = '& Sid'new 3: Where a.paddr = B.Addr and a.sid =' 30'Username Terminal -------- ------------------------------------------------- ------------------------------------- -ora9i unknownoracle @ CS_DC02 (TNS V1-V3) 835
Obviously, since the Report16 user performs some DDL operations, then abnormally exits, cause system lock (estimated and bug related, to be certified) SQL> Hostora9i @ cs_dc02: / ora9i / app / oracle / product / 920 / rdbms / admin> ps -ef | grep 835 ora9i 4619 4617 1 14:48:18 PTS / TE 0:00 Grep 835 ora9i 835 1 0 JAN 5? 0:01 oraclecsmisc2 (local = no) ora9i @ cs_dc02: / ora9i / app / oracle / product / 920 / rdbms / admin> kill 835ora9i @ 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 lock_mode ---------- ---------- ---------- ----------- 14 35202 31 3
SQL>
After Kill dropped this process, the problem was solved. (Unfortunately, forget to see the guy's SQL, huh, huh) SQL> Desc zzss03201281cs_noerror: ora-04043: Object zzss03201281cs_no does not exist
SQL> DESC ZZSS03201281CS_NOERROR: ORA-04043: Object zzss03201281cs_no does NOT EXIST
SQL>
SQL> EXEC DBMS_SYSTEM.SET_EV (32, 27506, 0, 0, ');
PL / SQL Procedure SuccessFully Completed.
SQL>
View trace files,:
Sure enough WAIT:
