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 production with the partitioning and real application clusters options jserver release 9.2.0.4.0 - production
create table csnoz629926699966 as select * from csnoz62992266cs where mid not in ( select mid from [email protected] where servid='020999011964' and status in ('a','b','s'));
exit; $ $ $ $
解决过程： [email protected]_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 production with the partitioning and real application clusters options jserver release 9.2.0.4.0 - production
sql> select * from v$lock where block=1;
no rows selected
sql> /
no rows selected
sql> /
no rows selected
sql> 我们看到目前没有锁的信息
sql> select xidusn, object_id, session_id, locked_mode from v$locked_object;
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 production with the partitioning and real application clusters options jserver 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 ------------------------------------ ----------- ------------------------------ background_core_dump string partial background_dump_dest string /ora9i/app/oracle/admin/csmisc /bdump core_dump_dest string /ora9i/app/oracle/admin/csmisc /cdump max_dump_file_size string unlimited shadow_core_dump string partial user_dump_dest string /ora9i/app/oracle/admin/csmisc /udump sql>
再看看sid为37的会话，产生的跟踪文件（csmisc2_ora_20552.trc）为的主要内容是： oracle9i enterprise edition release 9.2.0.4.0 - 64bit production with the partitioning and real application clusters options jserver release 9.2.0.4.0 - production oracle_home = /ora9i/app/oracle/product/920 system name: hp-ux node name: cs_dc02 release: b.11.11 version: u machine: 9000/800 instance name: csmisc2 redo thread mounted by this instance: 2 oracle process number: 26 unix process pid: 20552, image: [email protected]_dc02 (tns v1-v3)
问题的成因已经基本上明确了，这里推荐两种解决问题的方法： 方法1，根据 c000000122e2a6d8 地址，我们可以得到当前在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,kglnaobj sql> /
这个例子中我们在上面的跟踪文件已经找到了该会话对应的操作系统进程(spid)，如果在其他情况下，我们如何找到这种状态为'killed' 的操作系统进程号（spid）呢？ 下面给出了一个方法，可以借鉴： 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=37 sql> /
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=37 sql> /
no rows selected
sql>
回到刚才hang住的会话，它已经恢复了正常操作， 并且我们已经得到了'ora-04043: object csnoz629926699966 does not exist'这个正常的信息： sql> desc csnoz629926699966
error: ora-04043: object csnoz629926699966 does not exist
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 production with the partitioning and real application clusters options jserver release 9.2.0.4.0 - production
sql> set timing on sql> desc csnoz629926699966 error: ora-04043: object csnoz629926699966 does not exist
sql> 当发出命令'desc csnoz629926699966'的时候，我们看到系统立刻返回了ora-04043: object csnoz629926699966 does not exist'信息，问题就此解决了。
这里，简单的介绍一下x$kgllk，这个基表保存了库缓存中对象的锁的信息，它对于解决这类问题特别有用，其名称的含义如下： [k]ernel layer [g]eneric layer [l]ibrary cache manager ( defined and mapped from kqlf ) object locks x$kgllk - object [l]oc[k]s
