library cache lock解决方法

       今天上午，运维告知在创建一个表的时候hang住。出现问题前操作是，在通过dblink来迁移表的时候，由于时间等待太长，所以ctl+c终止掉该操作。后续在进行创建该表的时候就一直hang在那里。

 

SQL> conn cdcmcms/cdcmcms
Connected.
SQL> alter session set tracefile_identifier='abc';

Session altered.

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> create table cms_subcontenttext(a int);

hang 住

。。。。终止

alter session set events '10046 trace name context off';

 

oracle@linux-qfcb:~/admin/orcl/udump> more orcl_ora_7751_abc.trc
/opt/oracle/admin/orcl/udump/orcl_ora_7751_abc.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/10.2/db_1
System name:    Linux
Node name:      linux-qfcb
Release:        2.6.16.60-0.21-bigsmp
Version:        #1 SMP Tue May 6 12:41:02 UTC 2008
Machine:        i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 74
Unix process pid: 7751, image: oracle@linux-qfcb (TNS V1-V3)

*** 2012-02-03 11:02:40.789
*** ACTION NAME:() 2012-02-03 11:02:40.789
*** MODULE NAME:(SQL*Plus) 2012-02-03 11:02:40.789
*** SERVICE NAME:(SYS$USERS) 2012-02-03 11:02:40.789
*** SESSION ID:(72.2141) 2012-02-03 11:02:40.789
WAIT #12: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=69968 tim=1297107578896299
*** 2012-02-03 11:03:14.303
WAIT #12: nam='SQL*Net message from client' ela= 32727724 driver id=1650815232 #bytes=1 p3=0 obj#=69968 tim=1297107611624192
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #13 len=108 dep=1 uid=0 ct=3 lid=0 tim=1297107611625198 hv=1873661484 ad='ad563544'
select cc.intcol# from cdef$ c, ccol$ cc where c.obj#=cc.obj# and c.con#=cc.con# and c.type#=7 and c.obj#=:1
END OF STMT
PARSE #13:c=0,e=335,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1297107611625193
BINDS #13:
kkscoacd
 Bind#0
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=00 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7009fe8  bln=22  avl=04  flg=05
  value=69881
EXEC #13:c=4000,e=1225,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1297107611626487
FETCH #13:c=0,e=55,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=4,tim=1297107611626566
FETCH #13:c=0,e=8,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1297107611626591
STAT #13 id=1 cnt=1 pid=0 pos=1 bj=0 p='NESTED LOOPS  (cr=8 pr=0 pw=0 time=58 us)'
STAT #13 id=2 cnt=1 pid=1 pos=1 bj=31 p='TABLE ACCESS CLUSTER CDEF$ (cr=4 pr=0 pw=0 time=49 us)'
STAT #13 id=3 cnt=1 pid=2 pos=1 bj=30 p='INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=21 us)'
STAT #13 id=4 cnt=1 pid=1 pos=2 bj=32 p='TABLE ACCESS CLUSTER CCOL$ (cr=4 pr=0 pw=0 time=8 us)'
STAT #13 id=5 cnt=1 pid=4 pos=1 bj=30 p='INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=3 us)'
=====================
PARSING IN CURSOR #7 len=68 dep=0 uid=74 ct=1 lid=74 tim=1297107611626969 hv=1931983999 ad='982f5ba0'
create table cms_subcontenttext as select * from cms_subcontenttext1
END OF STMT
PARSE #7:c=4000,e=2657,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,tim=1297107611626965
BINDS #7:
=====================
PARSING IN CURSOR #11 len=94 dep=1 uid=47 ct=3 lid=47 tim=1297107611629209 hv=1451648271 ad='ad3b2250'
select dummy from dual where  ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'
END OF STMT
PARSE #11:c=0,e=2127,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1297107611629206
BINDS #11:
EXEC #11:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1297107611629373
FETCH #11:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1297107611629391
STAT #11 id=1 cnt=0 pid=0 pos=1 bj=0 p='FILTER  (cr=0 pr=0 pw=0 time=99 us)'
STAT #11 id=2 cnt=0 pid=1 pos=1 bj=258 p='TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us)'
WAIT #7: nam='library cache lock' ela= 2930506 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
 tim=1297107614560010  WAIT #7: nam='library cache lock' ela= 2929260 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
 tim=1297107617489332
WAIT #7: nam='library cache lock' ela= 2930012 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
 tim=1297107620419406
*** 2012-02-03 11:03:26.313
WAIT #7: nam='library cache lock' ela= 2933264 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
 tim=1297107623352713
WAIT #7: nam='library cache lock' ela= 2929612 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
 tim=1297107626282408
WAIT #7: nam='library cache lock' ela= 2929699 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
 tim=1297107629212157
WAIT #7: nam='library cache lock' ela= 2930019 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
 tim=1297107632142249
*** 2012-02-03 11:03:38.313
WAIT #7: nam='library cache lock' ela= 2929272 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
 tim=1297107635071576
WAIT #7: nam='library cache lock' ela= 2930007 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
 tim=1297107638001654
WAIT #7: nam='library cache lock' ela= 2929304 handle address=-1729105932 lock address=-1433139128 100*mode+namespace=301 obj#=69968
--More--(17%)

。。。。。。。。。。。。

 

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

no rows selected

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

    XIDUSN  OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
         4         18         46           3

SQL> select object_name,owner,object_type from dba_objects
  2  where object_id=18;

OBJECT_NAME
--------------------------------------------------------------------------------
OWNER                          OBJECT_TYPE
------------------------------ -------------------
OBJ$
SYS                            TABLE

SQL> col event for a35
SQL> select event,sid,p1,p2,p3 from v$session_wait
  2  where event not like 'SQL*%' and event not like 'rdbms%';

EVENT                                      SID         P1         P2         P3
----------------------------------- ---------- ---------- ---------- ----------
jobq slave wait                            119          0          0          0
Streams AQ: qmn slave idle wait            145          0          0          0
Streams AQ: waiting for time manage        147          0          0          0
ment or cleanup tasks

Streams AQ: qmn coordinator idle wa        154          0          0          0
it

smon timer                                 164        300          0          0
pmon timer                                 170        300          0          0

6 rows selected.

SQL> /

EVENT                                      SID         P1         P2         P3
----------------------------------- ---------- ---------- ---------- ----------
jobq slave wait                            119          0          0          0
library cache lock                         121 2565861364 2858189648        301
Streams AQ: qmn slave idle wait            145          0          0          0
Streams AQ: waiting for time manage        147          0          0          0
ment or cleanup tasks

Streams AQ: qmn coordinator idle wa        154          0          0          0
it

smon timer                                 164        300          0          0
pmon timer                                 170        300          0          0

 

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

no rows selected

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

    XIDUSN  OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
         4         18         46           3

SQL> select object_name,owner,object_type from dba_objects
  2  where object_id=18;

OBJECT_NAME
--------------------------------------------------------------------------------
OWNER                          OBJECT_TYPE
------------------------------ -------------------
OBJ$
SYS                            TABLE

SQL> col event for a35
SQL> select event,sid,p1,p2,p3 from v$session_wait
  2  where event not like 'SQL*%' and event not like 'rdbms%';

EVENT                                      SID         P1         P2         P3
----------------------------------- ---------- ---------- ---------- ----------
jobq slave wait                            119          0          0          0
library cache lock                         121 2565861364 2858518596        201
Streams AQ: qmn slave idle wait            145          0          0          0
Streams AQ: waiting for time manage        147          0          0          0
ment or cleanup tasks

Streams AQ: qmn coordinator idle wa        154          0          0          0
it

smon timer                                 164        300          0          0

EVENT                                      SID         P1         P2         P3
----------------------------------- ---------- ---------- ---------- ----------
pmon timer                                 170        300          0          0

8 rows selected.

SQL> select sid,serial# from v$session where sid in (46,121);

       SID    SERIAL#
---------- ----------
        46        211
       121      10016

SQL>  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=121
  8  ;

USERNAME                       STATUS   ADDR       KSLLAPSC   KSLLAPSN
------------------------------ -------- -------- ---------- ----------
KSLLASPO       KSLLID1R KS D
------------ ---------- -- -
CDCMCMS                        ACTIVE   AE2CF658          0          0
                      0

SQL>  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=46;

USERNAME                       STATUS   ADDR       KSLLAPSC   KSLLAPSN
------------------------------ -------- -------- ---------- ----------
KSLLASPO       KSLLID1R KS D
------------ ---------- -- -
CDCMCMS                        ACTIVE   AE2D912C          4         24
11390               109 EV

SQL> select spid,pid from v$processes where addr='AE2D912C';
select spid,pid from v$processes where addr='AE2D912C'
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select spid,pid from v$process where addr='AE2D912C';

SPID                PID
------------ ----------
28447                97

SQL> select spid,pid from v$process where addr='AE2CF658';

SPID                PID
------------ ----------
10159                70

kill掉进程，重新创建成功！！

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22779291/viewspace-715591/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22779291/viewspace-715591/