服务器端直接用sqlplus连接:
sqlpluu /nolog
conn DMRPT/password
.....一直在等待
然后看在等待什么
SQL> select inst_id,sid,serial# from gv$session
2 where program like '%sqlplus@hostname%' and status='ACTIVE'
3 ;
INST_ID SID SERIAL#
---------- ---------- ----------
1 1531 46367
等待事件是:library cache lock username是空 还没连接成功 一直等待
SQL> SELECT sid, username, event, p1text, p1, p2text, p2, p3text, p3, seconds_in_wait
2 FROM gv$session
3 WHERE sid=1531;
SID USERNAME EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 SECONDS_IN_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- ---------- ---------------
1531 library cache lock handle address 2728834360 lock address 1843387668 100*mode+namespace 5177346 1760
1531 library cache lock handle address 2719665146 lock address 2718725940 100*mode+namespace 5177346 8251
没有对应的session
SQL> select inst_id,sid,SERIAL#,status,machine,program from gv$session
2 where username='DMRPT' and program like '%sqlplus@hostname%'
3 order by inst_id;
no rows
等待很久后:
username不为空:
SQL> SELECT sid, username, event, p1text, p1, p2text, p2, p3text, p3, seconds_in_wait
2 FROM gv$session
3 WHERE sid=1531;
SID USERNAME EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 SECONDS_IN_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- ---------- ---------------
1531 DMRPT library cache lock handle address 2728834360 lock address 1843387668 100*mode+namespace 5177346 1760
1531 library cache lock handle address 2719665146 lock address 2718725940 100*mode+namespace 5177346 8251
出现session:
SQL> select inst_id,sid,SERIAL#,status,machine,program from gv$session
2 where username='DMRPT' and program like '%sqlplus@hostname%'
3 order by inst_id;
INST_ID SID SERIAL# STATUS MACHINE PROGRAM
---------- ---------- ---------- -------- ---------------------------------------------------------------- ------------------------------------------------
1 1531 46367 ACTIVE hostname sqlplus@hostname (TNS V1-V3)
conn DMRPT/password
.....还是在等待 没有出现连接成功连接信息