Problem Description: Received the application of the application, saying that in any of the operations of the table CSNOZ629926699966, HANG, including DESC CSNOZ629926699966, for example:
ORA9i @ cs_dc02: / ora9i> SQLPLUS Pubuser / Pubuser
SQL * Plus: Release 9.2.0.4.0 - Production ON MON JAN 10 10:11:06 2005
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Productionwith The Partitioning and Real Application Clusters OptionsJServer Release 9.2.0.4.0 - Production
SQL> CONN Pubuser / PubuserConnected.sql> Desc csnoz629926699966
. . .
This process hang
. . .
Asked for a special operation, the business person said that the script has been performed a long time, but the education is running for a long time, and then he quits the session, and then there is a situation. The script is as follows: $ cat csnoz629926699966.sh #! / Bin / shssqlplus publicuser / public @ csmisc << Eof # use your username / password
create table CSNOZ629926699966 as select * from CSNOZ62992266cswhere mid not in (select mid from pubuser.SUBSCRIPTION_BAK_200412@newdb where servid = '020999011964' and status in ( 'A', 'B', 'S'));
Exit; $$$?
Solution process: ora9i @ cs_dc02: / ora9i> SQLPlus "/ as sysdba"
SQL * Plus: Release 9.2.0.4.0 - Production ON MON JAN 10 10:19:13 2005
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Productionwith The Partitioning and Real Application Clusters OptionsJServer Release 9.2.0.4.0 - Production
SQL> SELECT * FROM V $ loc WHERE block = 1;
No rows selected
SQL> /
No rows selected
SQL> /
No rows selected
SQL> We see information that is currently unlocking
SQL> SELECT Xidusn, Object_ID, Session_ID, Locked_Mode from V $ locked_OBJECT
. . .
Xidusn Object_ID session_id lock_mode ---------- ------------------ ----------- 14 18 37 3. . .
SQL> /
. . .
Xidusn Object_ID session_id lock_mode ------------------ ---------- ----------- 14 18 37 3
. . .
SQL> /
. . .
Xidusn Object_ID session_id lock_mode ------------------ ---------- ----------- 14 18 37 3
. . .
SQL> Find v $ locked_Object, we discovered a suspicious session, SID 37:
SQL> Select Object_name, Owner, Object_Type from DBA_Objects Where Object_ID = 18;
. . . . . .
Object_name owner object_type ------------------------------------------------- ---------------------------- Obj $ sys table
. . . . . .
SQL>
How has it always have this lock? ? Preliminary guess is due to the SID 37 session executing the above DDL statement and exits before the statement is not completed, causing all the processes that access to the object involved in the DDL statement) HANG.
Let's take a look at the wait event: SQL> SELECT EVENT, SID, P1, P2, P3 from V $ session_wait where event not like 'sql *%' and Event not Like 'RDBMS%'
Event P1 P2 SID ---------------------------------------------- -------------------------------------------- PMON TIMER 300 0 1GCS Remote Message 64 0 5GCS Remote Message 64 0 7SMON TIMER 300 0 19Library Cache Lock 1.3835E 19 1.3835E 19 30wakeup Time Manager 0 0 227 Rows SELECTED.
SQL> /
Event P1 P2 SID ---------------------------------------------- -------------------------------------------- PMON TIMER 300 0 1GCS Remote Message 64 0 5GCS Remote Message 64 0 7SMON TIMER 300 0 19Library Cache Lock 1.3835E 19 1.3835E 19 30wakeup Time Manager 0 0 227 Rows SELECTED.
SQL> /
Event P1 P2 SID ---------------------------------------------- -------------------------------------------- PMON TIMER 300 0 1GCS Remote Message 64 0 5GCS Remote Message 64 0 7SMON TIMER 300 0 19Library Cache Lock 1.3835E 19 1.3835E 19 30wakeup Time Manager 0 0 227 Rows SELECTED.
SQL> /
Event P1 P2 SID ---------------------------------------------- -------------------------------------------- PMON TIMER 300 0 1GCS Remote Message 64 0 5GCS Remote Message 64 0 7SMON TIMER 300 0 19Library Cache Lock 1.3835E 19 1.3835E 19 30wakeup Time Manager 0 0 227 Rows SELECTED.
SQL>
We noticed the following events: Event P1 P2 SID --------------------------------------- ------------------------------------- ----. . .
Library Cache Lock 1.3835E 19 1.3835E 19 30
. . .
P1 is a handle address, which is an address that occurs 'Library Cache Lock'. P2 is a status object, here, indicating the address of the lock loaded on the object (Lock Address). Both P1 and P2 are the number of 10-encened numbers represented by scientific counts.
This information confirms the above speculation, SID 37 blocks SID 30.
Find these two suspicious processes SID and Serial, then set up 10046 events to them: SQL> Select Sid, Serial # from v $ session where sid in (30, 37);
SID Serial # ---------- ---------- 30 24167 37 2707
SQL> EXEC DBMS_SYSTEM.SET_EV (30, 24167, 10046, 12, ');
PL / SQL Procedure SuccessFully Completed.
SQL> EXEC DBMS_SYSTEM.SEC DBMS_SYSTEM.SET_EV (37, 2707, 10046, 12, '); PL / SQL Procedure SuccessFully Completed.
SQL>
We tested again during tracking, see if there is any other clue.
Newly opened a process, find out their SID, SERIAL and SPID: ORA9I @ cs_dc01: / ora9i> SQLPlus Pubuser / Pubuser
SQL * Plus: Release 9.2.0.4.0 - Production ON MON JAN 10 11:36:25 2005
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Productionwith The Partitioning and Real Application Clusters OptionsJServer Release 9.2.0.4.0 - Production
SQL> SELECT DISTINCT SID FROM V $ MyStat;
SID ---------- 33
SQL> SELECT SID, Serial # from v $ session where sid = 33;
SID SERIAL # ---------- ---------- 33 6639
SQL> SELECT SPID, PID from V $ process where addr = (SELECT PADDR FROM V $ session where sid = 37);
Spid pid ---------------------- 20552 26
SQL> SELECT SPID, PID from V $ process where addr = (SELECT PADDR from V $ session where sid = 30);
Spid pid ---------------------- 22580 28
SQL> Show Parameter Dump
Name Type Value --------------------------------- ------------------------------------------------------------------------------------------------------------------- app / oracle / admin / csmisc / cdumpmax_dump_file_size string UNLIMITEDshadow_core_dump string partialuser_dump_dest string / ora9i / app / oracle / admin / csmisc / udumpSQL> then, try to CSNOZ629926699966 operating table SQL> desc CSNOZ629926699966
. . .
Still hang.
So this operation (Ctrl C):
SQL> DESC CSNOZ629926699966ERROR: ORA-01013: User Requested Cancel of current Operation
SQL> SELECT TNAME from Tab Where TName = 'CSNOZ629926699966';
No rows selected
SQL> View this table under the Pubuser user, there is no existence! !
Further confirmed the previous guess, that is, the session 37 blocked the session of all other operational tables CSNOZ629926699966, causing the HANG of the process, of course, including the above SID 30 and SID 33 DDL statements
Now, we end 10046 Event Tracking: SQL> EXEC DBMS_SYSTEM.SET_EV (30, 24167, 0, 0, ');
PL / SQL Procedure SuccessFully Completed.
SQL> EXEC DBMS_SYSTEM.SET_EV (37, 2707, 0, 0, ');
PL / SQL Procedure SuccessFully Completed.
SQL>
According to the information recorded above, we know that the tracking information generated by these two sessions is: SID 30 session, the generated tracking file is: /ora9i/app/oracle/admin/csmisc/udump/csmisc2_ora_22580.trcsid is 37 Session, the generated tracking file is: /ora9i/app/oracle/admin/csmisc/udump/csmisc2_ora_20552.trc
Take a look at the trace file: ora9i @ cs_dc02: / ora9i> cd / ora9i / app / oracle / admin / csmisc / udumpora9i @ cs_dc02: / ora9i / app / oracle / admin / csmisc / udump> ll -tlctotal 4432-rw-r- ---- 1 ORA9I DBA 332995 JAN 10 12:00 CSMISC2_ORA_22580.TRC-RW-R ----- 1 ORA9I DBA 3168 Jan 10 11:59 csmisc2_ora_20552.TRC-RW-R ----- 1 ORA9I DBA 407133 Jan 7 15:10 CSMISC2_ORA_2708.TRC-RW-R ----- 1 ORA9I DBA 640 Jan 7 14:48 csmisc2_ora_835.TRC-RW-R ---- 1 ORA9I DBA 1590 DEC 30 22:50 csmisc2_ora_16244.trc -rw-r ----- 1 ORA9I DBA 1308403 DEC 30 22:44 csmisc2_ora_16033.TRC-RW-R ----- 1 ORA9I DBA 616 DEC 28 14:16 CSMISC2_ORA_2176.TRC-RW-r ---- - 1 ora9i dba 644 Dec 8 18:22 csmisc2_ora_21083.trcora9i@cs_dc02: / ora9i / app / oracle / admin / csmisc / udump> mailx -s "csmisc2_ora_22580.trc" zhangdp@aspire-tech.com
We see the session of the SID 30, the main content of the trace file (CSMISC2_ORA_22580.TRC) is: /ora9i/app/oracle/admin/csmisc/udump/csmisc2_ora_22580.trcoracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionWith the Partitioning and Real Application Clusters optionsJServer Release 9.2.0.4.0 - ProductionORACLE_HOME = / ora9i / app / oracle / product / 920System name: HP-UXNode name: cs_dc02Release: B.11.11Version: UMachine: 9000 / 800Instance name: csmisc2Redo thread MOUNTED by this instance: 2Oracle process Number: 28Unix Process Pid: 22580, Image: Oracle @ CS_DC02 (TNS V1-V3)
*** 2005-01-10 11: 31: 49.416 *** Session ID: (30.24167) 2005-01-10 11: 31: 49.354Wait # 0: nam = 'library cache lock' ELA = 507258 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ela = 505686 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ela = 507678 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ela = 507595 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ela = 507880 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ela = 507317 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ela = 507703 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ela = 507683 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ela = 508265 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'L ibrary cache lock 'ela = 507100 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam =' library cache lock 'ela = 507684 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam =' library cache lock 'ela = 505889 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam =' library cache lock 'ela = 507731 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 =
1301WAIT # 0: nam = 'library cache lock' ela = 507650 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ela = 507604 p1 = -4611686013547141416 p2 = -4611686013691716064 p3 = 1301WAIT # 0: nam = 'library cache lock' ELA = 507698 P1 = - 4611686013547141416 P2 = -461686013691716064 P3 = 1301. . . . . .
We see the Waiting event in the SID 30 trace file is the 'library cache lock "seen in V $ session_wait.
Then look at the session of the SID 37, the main content of the trace file (CSMISC2_ORA_20552.TRC) is: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Productionwith The Partitioning and Real Application Clusters OptionsJServer Release 9.2.0.4.0 - ProductionORACLE_HOME = / ora9i / app / oracle / product / 920System name: HP-UXNode name: cs_dc02Release: B.11.11Version: UMachine: 9000 / 800Instance name: csmisc2Redo thread mounted by this instance: 2Oracle process number: 26Unix process pid: 20552, Image: Oracle @ CS_DC02 (TNS V1-V3)
*** 2005-01-10 11: 33: 22.702 *** Session ID: (37.2707) 2005-01-10 11: 33: 22.690Wait # 1: nam = 'sql * net message to dblink' ELA = 4 P1 = 675562835 p2 = 1 p3 = 0 *** 2005-01-10 11: 35: 07.452Wait # 1: nam = 'sql * net message from dblink' ELA = 102293555 p1 = 1 p3 = 0wait # 1: Nam = 'sql * net message to dblink' ELA = 3 p1 = 675562835 P2 = 1 p3 = 0 *** 2005-01-10 11: 36: 55.980Wait # 1: Nam = 'SQL * Net Message from DBLINK' ELA = 105969709 p1 = 675562835 p2 = 1 p3 = 0Wait # 1: nam = 'sql * net message to dblink' ELA = 4 p1 = 675562835 P2 = 1 p3 = 0 *** 2005-01-10 11: 39: 05.416Wait # 1: nam = 'sql * net message from dblink' ELA = 126390826 P1 = 675562835 P2 = 1 p3 = 0wait # 1: nam = 'sql * net message to dblink' ELA = 4 p1 = 1 p3 = 0 *** 2005-01-10 11: 41: 12.878Wait # 1: nam = 'sql * net message from dblink' ELA = 124461520 P1 = 675562835 P2 = 1 p3 = 0wait # 1: nam = 'sql * net message to DBLINK 'ELA = 4 p1 = 675562835 P2 = 1 p3 = 0 *** 2005-01-10 11: 43: 01.285Wait # 1: nam =' sql * net message from dblink 'ELA = 105859385 P1 = 675562835 P2 = 1 P3 = 0wait # 1: nam = 'sql * net message to dblink' ELA = 4 p1 = 675562835 P2 = 1 p3 = 0 *** 2005-01-10 11: 44: 48.200Wait # 1: nam = 'sql * net message from dblink' ELA = 104397696 P1 = 675562835 P2 = 1 p3 = 0wait # 1: nam = 'sql * net message to dblink' ELA = 4 p1 = 675562835 P2 = 1 p3 = 0. . . . . .
Now let's come to Dump SystemState to see more detailed information.
First give a brief introduction to Event SystemState. Many people understand the information of all the processes in the system that occurred in DUMP. This is an error concept. In fact, the trace file generated by the dump system state starts from DUMP to DUMP task completion. Information in all processes in the system in the event. The tracking file generated by Dump SystemState contains information such as process status of all processes in the system. Each process corresponds to a piece of content in the file, reflects the status information of the process, including process information, session information, enqueues information (mainly LOCK information), and the information of the buffer and the process held in the SGA area. HELD) The status of the object and other information.
So what is usually in the case of using SystemState? Several cases of using the SystemState events recommended by Oracle are:
Database HANG lived a database very slow process is in the HANG database, some error resource contentions
Dump SystemState's syntax for: Alter Session Set Events 'Immediate Trace Name SystemState Level 10';
You can also use ORADEBUG to implement this feature Oradebug Dump SystemState Level 10
If you want to have some error in the database unless the systemState event, an Event parameter can be set in the parameter file (SPFILE or PFILE), for example, dump systemState: Event = "60 Trace when the system is deadlock (ORA-00060 error) Name SystemState Level 10 "
Master-class, our Dump system status: SQL> Alter session set events 'immediate trace name systemState level Level 8';
Session altered.
SQL> Hostora9i @ cs_dc02: / ora9i> cd / ora9i / app / oracle / admin / csmisc / udumpora9i @ cs_dc02: / ora9i / app / oracle / admin / csmisc / udump> ll -ctl-rw-r ----- 1 ORA9I DBA 1070863 JAN 10 13:02 csmisc2_ora_22580.TRC-RW-R ----- 1 ORA9I DBA 1345368 JAN 10 13:01 CSMISC2_ORA_22568.TRC-RWXRWXRWX 1 ORA9I DBA 44114 Jan 10 12:52 ass1015.awk-rw- R ----- 1 ORA9I DBA 407133 JAN 7 15:10 CSMISC2_ORA_2708.TRC-RW-R ----- 1 ORA9I DBA 640 Jan 7 14:48 csmisc2_ora_835.TRC-RW-R ----- 1 ORA9I DBA 1590 DEC 30 22:50 CSMISC2_ORA_16244.TRC-RW-R ---- 1 ORA9I DBA 1308403 DEC 30 22:44 csmisc2_ora_16033.TRC-RW-R ----- 1 ORA9I DBA 616 DEC 28 14:16 csmisc2_ora_2176 .TRC-RW-R ----- 1 ORA9I DBA 644 DEC 8 18:22 csmisc2_ora_21083.trcora9i@cs_dc02: / ora9i / app / oracle / admin / csmisc / udump> ORA9I @ cs_dc02: / ora9i / app / oracle / Admin / CSMISC / UDUMP> Mailx -s "22568" zhangdp@aspire-tech.com First, by looking for strings "Waiting for 'Library Cache Lock'" in the trace file, we found information about the blocked process: Process 28: -------------- is blocked Oracle process, here Process 28 corresponds to the value of PID in V $ Process, that is, we can in v. $ Process and V $ Session Find information about blocked sessions ----------------------------------- --- SO: C000000109C83BF0, TYPE: 2, OWNER: 00000000000000000000, Flag: init / - / - / 0x00 (Process) Oracle PID = 28, Calls Cur / Top: C00000010B277890 / C00000010B277890, Flag: (0) - int error: 0, Call Error: 0, Sess Error: 0 (POST INFO) Last Post Received: 17 24 6 Last Post Received-Location: Ksusig Last Process To Post Me: C000000109C840F8 25 0 Last Post SENT: 0 0 15 Last post sent-location: ksasnd last process posted by me: c000000109c7ff90 1 6 (latch info) wait_event = 0 bits = 0 Process Group: DEFAULT, pseudo proc: c000000109eefda0 O / S info: user: ora9i, term: pts / th, ospid : 22580 ---------------- The process of operating system processes, corresponding to SPID OSD PID INFO in V $ Process: Unix Process Pid: 22580, Image: Oracle @ CS_DC02 ( TNS V1-V3) -------------------------------------- - SO: C000000109F02C68, TYPE: 4, OWNER: C000000109C83BF0, Flag: init / - / - / 0x00 (session) Trans: 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008, Creation: C000000109C83BF0, FLAG: (100041) USR / - BSY / - / - / - / - / - DID: 0002-001C-00000192, Short-Term Did: 0000-0000-00000000 TXN Branch: 0000000000000000 OCT: 0, PRV: 0, SQL: C00000011F8EA068, PSQL: C00000011F8EA068, User: 50 / Pubuser O / S info : user: ora9i, term:, ospid: 22536, machine: cs_dc02 program: sqlplus @ cs_dc02 (TNS V1-V3) application name: SQL * Plus, hash value = 3669949024 waiting for 'library cache lock' blocking sess = 0x0 seq = 18589 WAIT_TIME = 0 Handle Address = C000000122E2A6D8, LOCK ADDRESS = C00000011A449E20, 100 * MODE NAMESPACE = 515. . . . . . SO: C00000010B277890, TYPE: 3, OWNER: C000000109C83BF0, FLAG: INIT / - / - / 0X00 (CALL) SESS: CUR C000000109F02C68, REC 0, USR C00000000109F02C68; DEPTH: 0 -------------- ---------------------------- SO: C00000011A449E20, TYPE: 51, OWNER: C00000010B277890, FLAG: INIT / - / - / 0x00 Library OBJECT LOCK: lock = c00000011a449e20 handle = c000000122e2a6d8 request = S call pin = 0000000000000000 session pin = 0000000000000000 htl = c00000011a449e90 [c00000011a4bc350, c00000011a4bc350] htb = c00000011a4bc350 user = c000000109f02c68 session = c000000109f02c68 count = 0 flags = [00] savepoint = 463 the rest of the object WAS Already Dumped . . . . . . Please pay attention to the following information: waiting for 'library cache lock' blocking sess = 0x0 seq = 18589 wait_time = 0 Handle Address = C000000122E2A6D8, LOCK Address = C00000011A449E20, 100 * Mode Namespace = 515 This information tells us that Oracle PID is 28 process (Process 28), waiting for 'library cache lock', through 'Handle Address = C000000122E2A6D8' we can find Oracle PID information that blocks its session. Note also that this piece of information: LIBRARY OBJECT LOCK: lock = c00000011a449e20 handle = c000000122e2a6d8 request = S call pin = 0000000000000000 session pin = 0000000000000000 htl = c00000011a449e90 [c00000011a4bc350, c00000011a4bc350] htb = c00000011a4bc350 user = c000000109f02c68 session = c000000109f02c68 count = 0 flags = [00] savepoint = 463 Here is information that blocks the session of the Process 28 process. Simplely remembering this basis is: The value of Waiting session 'Handle Address' corresponds to the value of the 'Handle' of the Blocking Session. Turning back, look at this value, it should be on the value of P1 and P2 in V $ session_wait: SQL> SELECT TOXXXXXXXXXXXXXXXXXXXXXXXXXXX '] from Dual; TO_NUMBER (' C000000122E2A6D8 ', 'Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------ 1.3835E 19 SQL> The cause of the problem is basically clear. It is recommended to solve problems here: Method 1, according to C00000012E2A6D8 address, we can get the current lock information in Library Cache: SQL> L 1 SELECT INST_ID, USER_NAME, KGLNAOBJ, KGLLKSNM , Kgllkuse, Kgllkses, Kgllkmod, Kgllkreq, Kgllkpns, Kgllkhdl 2 * from x $ kgllk where kgllkhdl = 'c000000122e2a6d8' Order by kgllksnm, kglnaobjsql> / INST_ID User_name Kglnaobj Kgllksnm Kgllkuse Kgllkses Kgllkmod Kgllkreq Kgllkpns Kgllkhdl ------------------------------------------------------------------------------------------ ------------------------------------------ --- ------- ---------- ---------------- ---------------- 2 Pubuser CSNOZ629926699966 30 C000000109F02C68 0 2 00 C000000122A6D8 2 Pubuser CSNOZ629926699966 37 C000000108C99E28 3 0 00 C00000012E2A6D8 SQL> According to Oracle recommended practices, we should now use the 'Alter System Kill Session' command kill to drop SID 37, resulting in ORA-00031 error: SQL> ALTER SYSTEM KILL session '37, 2707'; Alter System Kill Session '37, 2707 * Error At line 1: ORA-00031: Session Marked for Kill SQL> Check the status of SID 37: SQL> Set Linesize 150SQL> Col g f = 5 = = s s;; = = 37; SID Serial # status username program ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------ -------------- 37 2707 Killed Pubuser SQLPlus @ cs_dc02 (TNS V1-V3) SQL> Remove our initial idea - someone is executing a long-standing DDL ( Most is the low statement efficiency, of course, does not exclude the possible possible), and then don't have an abnormality to exit the session. In this example, we have found the operating system process (SPID) corresponding to the session, if we find this state's operating system process number (SPID) in other cases? A method is given below, you can learn from: SQL> L 1 Select S.Username, S.Status, 2 x.addr, x.ksllapsc, x.ksllapsn, x.ksllaspo, x.ksllid1r, x.ksllrtyp, 3 Decode (Bitand (x.ksuprflg, 2), 0, null, 1) 4 from x $ ksupr x, v $ session s 5 where s.paddr ( ) = x.addr 6 and Bitand (ksspaflg, 1)! = 0 7 * and s.SID = 37sql> / UserName Status Addr Ksllapsc Ksllapsn Ksllaspo Ksllid1r Ks D ------------------------------------ --- ----------------------------------- ----- - -pubuser killed c000000109c831e0 41 15 16243 17 SQL> The value of the x $ kSUPR.Addr column corresponds to the value of the ADDR in V $ Process, knows the address of this SPID, find this operating system process (spid) is simple, for example: SQL> SELECT SPID, PID from V $ Process WHERE AddR = 'C00000000109C831E0'; Spid pid ---------------------- 20552 26 SQL> Now, we only need the Kill operating system process 20552 on the operating system: ora9i @ cs_dc02: / ora9i> ps -ef | grep 20552 ora9i 20552 1 0 JAN 8? 0:01 oraclecsmisc2 (local = no) ORA9I 14742 14740 0 17:19:02 PTS / TI 0:00 Grep 20552ra9i @ cs_dc02: / ora9i> kill -9 20552 ora9i @ cs_dc02: / ora9i> ps -ef | grep 20552 ora9i 14966 14964 0 17:40:01 PTS / TI 0 : 00 GREP 20552RA9I @ cs_dc02: / ora9i> Take Check the SID 37 information, we see this session is really killed, ora9i @ cs_dc02: / ora9i> EXIT SQL> SELECT SID, Serial #, Status, Username, Program from V $ Session Where SID = 37; No rows selected SQL> L 1 Select S.username, S.Status, 2 x.Addr, x.ksllapsc, x.ksllapsn, x.ksllaspo, x.ksllid1r, x.ksllrtyp, 3 decode (Bitand (x.ksuprflg, 2), 0, NULL, 1) 4 from x $ ksupr x, v $ session s 5 where s.paddr ( ) = x.addr 6 and bitand (ksspaflg, 1)! = 0 7 * and s.SID = 37sql> / No rows selected SQL> Go back to the session that had just lived, it has resumed normal operation, and we have got 'Ora-04043: Object csnoz629926699966 does not exist' This normal information: SQL> Desc csnoz62992669966 Error: ORA-04043: Object Csnoz629926699966 Does not exist SQL> Open a session, test one: ora9i @ cs_dc02: / ora9i> SQLPlus Pubuser / Pubuser SQL * Plus: Release 9.2.0.4.0 - Production ON MON JAN 10 17:42:16 2005 CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Productionwith The Partitioning and Real Application Clusters OptionsJServer Release 9.2.0.4.0 - Production SQL> SET TIMING ONSQL> DESC CSNOZ629926699966ERROR: ORA-04043: Object csnoz629926699966 does not exist SQL> When you issue a command 'DESC CSNOZ629926699966', we see the system immediately returned ORA-04043: Object csnoz629926699966 does not exist 'information, the problem is solved. Here, a simple introduction to x $ kgllk, this base table holds information about the lock in the library cache, which is especially useful for solving such problems, the meaning of its name is as follows: [K] ENEL LAYER [G] ENERIC LAYER [ L] Ibrary Cache Manager (Defined and Mapped from KQLF) Object Locks X $ KGLLK - Object [L] OC [K] S The kglnaobj column contains the first 80 characters of the statement that executes the command on the object in Librarky Cac (in fact, we can also significantly narrow the range) x $ kgllk.kgllkuse and x $ kgllk.kgllkses correspond to Owner in the trace file The value x $ kgllk.kgllkadr x $ kgllk.kgllkhdl corresponds to the value of the handle in the trace file (Handle = C000000122E2A6D8), is the address of 'library cache lock' x $ kgllk.kgllkpns corresponds to the session pin in the trace file Value x $ kgllk.kgllkspn corresponds to the value of SavePoint in the trace file Let's take a more comprehensive information: SQL> Set linesize 2000sql> Select * from x $ kgllk where kgllkhdl = 'c00000012E2A6D8' Order by kgllksnm, kglnaobj 2 / ADDR INDX INST_ID KGLLKADR KGLLKUSE KGLLKSES KGLLKSNM KGLLKHDL KGLLKPNC KGLLKPNS KGLLKCNT KGLLKMOD KGLLKREQ KGLLKFLG KGLLKSPN KGLLKHTB KGLNAHSH KGLHDPAR KGLHDNSP USER_NAME KGLNAOBJ ---------------- ---------- --- ----------------------------------- ------------------------------------------ --- ----------------------------------- --- ---------- ----------------------------------- ----- ------------------------------------------- -------------------------------------------------- ----- 800003FB0007E4D0 33 2 C00000011A449E20 C000000109F02C68 C000000109F02C68 30 C000000122E2A6D8 00 00 0 0 2 0 463 C00000011A4BC350 3990848181 C000000122E2A6D8 1 pUBUSER CSNOZ629926699966800003FB0007E5B0 34 2 C0 0000011A44A150 C000000108C99E28 C000000108C99E28 37 C000000122E2A6D8 00 00 1 3 0 0 179 C00000011A4BB328 3990848181 C000000122E2A6D8 1 PUBUSER CSNOZ629926699966SQL> set linesize 100SQL> l 1 * select * from X $ KGLLK where KGLLKHDL = 'C000000122E2A6D8' order by KGLLKSNM, KGLNAOBJSQL> / Addr Indx Inst_id Kgllkadr Kgllkuse Kgllkses Kgllksnm ----------------------------------------- ----------------------------------- -KGLLKHDL KGLLKPNC KGLLKPNS KGLLKCNT KGLLKMOD KGLLKREQ KGLLKFLG -------------------------------------------------------------------------------------------------------------------------------------- ------ ---------------------------- ---------- KGLLKSPN KGLLKHTB KGLNAHSH KGLHDPAR KGLHDNSP User_Name ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- ----------------------------- kglnaobj ----- -------------------------------------------------- ----- 800003FB0007E4D0 33 2 C00000011A449E20 C000000109F02C68 C000000109F02C68 30C000000122E2A6D8 00 00 0 0 2 0 463 C00000011A4BC350 3990848181 C000000122E2A6D8 1 PUBUSERCSNOZ629926699966800003FB0007E5B0 34 2 C00000011A44A150 C000000108C99E28 C000000108C99 E28 37C000000122E2A6D8 00 00 1 3 0 0 179 C00000011A4BB328 3990848181 C000000122E2A6D8 1 Pubusercsnoz62992669966 SQL> ----to be continued----
