--dbms_lock使用
---安装并赋权
$ORACLE_HOME/rdbms/admin/dbmslock.sql
grant execute on dbms_lock to username;
---安装并赋权
$ORACLE_HOME/rdbms/admin/dbmslock.sql
grant execute on dbms_lock to username;
--db版本
SQL> select * from v$version;
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
--sessionA:
---在scoot用户下创建存储过程zyp并执行
SQL> conn scott/123;
Connected.
SQL> create or replace procedure zyp
2 as
3 begin
4 dbms_lock.sleep(100000);
5 end;
6 /
---在scoot用户下创建存储过程zyp并执行
SQL> conn scott/123;
Connected.
SQL> create or replace procedure zyp
2 as
3 begin
4 dbms_lock.sleep(100000);
5 end;
6 /
Procedure created.
SQL> exec zyp;
--sessionB:
---在sys用户下重新编译存储过程scott.zyp
SQL> show user
USER is "SYS"
SQL> create or replace procedure scott.zyp
2 as
3 begin
4 dbms_lock.sleep(100000);
5 end;
6 /
---在sys用户下重新编译存储过程scott.zyp
SQL> show user
USER is "SYS"
SQL> create or replace procedure scott.zyp
2 as
3 begin
4 dbms_lock.sleep(100000);
5 end;
6 /
--sessionC:
---查询library cache pin等待事件的相关会话
SQL> set linesize 300
SQL> select saddr,sid,username,event,p1raw from v$session where event='library cache pin';
SADDR SID USERNAME EVENT P1RAW
-------- ---------- ------------------------------ ---------------------------------------------------------------- ----------------
3D2C0984 145 SYS library cache pin 000000003D5B1884
-------- ---------- ------------------------------ ---------------------------------------------------------------- ----------------
3D2C0984 145 SYS library cache pin 000000003D5B1884
---找到持有library cache pin以及等待library cache pin的session
SQL> SELECT s.sid,s.username, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
2 FROM v$session_wait w, x$kglpn p, v$session s, v$process o
3 WHERE p.kglpnuse = s.saddr
4 AND kglpnhdl = '&p1raw'
5 and w.event like '%library cache pin%'
6 and s.paddr = o.addr;
Enter value for p1raw: 3D5B1884
old 4: AND kglpnhdl = '&p1raw'
new 4: AND kglpnhdl = '3D5B1884'
SQL> SELECT s.sid,s.username, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
2 FROM v$session_wait w, x$kglpn p, v$session s, v$process o
3 WHERE p.kglpnuse = s.saddr
4 AND kglpnhdl = '&p1raw'
5 and w.event like '%library cache pin%'
6 and s.paddr = o.addr;
Enter value for p1raw: 3D5B1884
old 4: AND kglpnhdl = '&p1raw'
new 4: AND kglpnhdl = '3D5B1884'
SID USERNAME Mode Req OS Process
---------- ------------------------------ ---------- ---------- ------------------------
145 SYS 0 3 12884
143 SCOTT 2 0 12823
重新编译存储过程scott.zyp的session是145,从上述结果里我们可以看出来145现在想以Exclusive模式(即Req=3)去持有library cache pin,同时现在持有上述library cache pin的是session 143,且143的持有模式是Share(即Mode=2),也就是说143阻塞了145
---获取sid为143的SQL_HASH_VALUE(间隔10s多执行几次)
SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=143;
---------- ------------------------------ ---------- ---------- ------------------------
145 SYS 0 3 12884
143 SCOTT 2 0 12823
重新编译存储过程scott.zyp的session是145,从上述结果里我们可以看出来145现在想以Exclusive模式(即Req=3)去持有library cache pin,同时现在持有上述library cache pin的是session 143,且143的持有模式是Share(即Mode=2),也就是说143阻塞了145
---获取sid为143的SQL_HASH_VALUE(间隔10s多执行几次)
SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=143;
DECODE(SQL_HASH_VALUE,0,PREV_HASH_VALUE,SQL_HASH_VALUE)
-------------------------------------------------------
3192006346
-------------------------------------------------------
3192006346
SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=143;
DECODE(SQL_HASH_VALUE,0,PREV_HASH_VALUE,SQL_HASH_VALUE)
-------------------------------------------------------
3192006346
-------------------------------------------------------
3192006346
SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=143;
DECODE(SQL_HASH_VALUE,0,PREV_HASH_VALUE,SQL_HASH_VALUE)
-------------------------------------------------------
3192006346
---根据获得的SQL_HASH_VALUE获得对应的sqltext
SQL> select sql_text from v$sqltext where hash_value='&SQL_HASH_VALUE' order by piece;
Enter value for sql_hash_value: 3192006346
old 1: select sql_text from v$sqltext where hash_value='&SQL_HASH_VALUE' order by piece
new 1: select sql_text from v$sqltext where hash_value='3192006346' order by piece
-------------------------------------------------------
3192006346
---根据获得的SQL_HASH_VALUE获得对应的sqltext
SQL> select sql_text from v$sqltext where hash_value='&SQL_HASH_VALUE' order by piece;
Enter value for sql_hash_value: 3192006346
old 1: select sql_text from v$sqltext where hash_value='&SQL_HASH_VALUE' order by piece
new 1: select sql_text from v$sqltext where hash_value='3192006346' order by piece
SQL_TEXT
----------------------------------------------------------------
BEGIN zyp; END;
----------------------------------------------------------------
BEGIN zyp; END;
---kill持有锁的session
SQL> !
[oracle@ora11 ~]$ kill -9 12823
[oracle@ora11 ~]$ exit
exit
SQL> !
[oracle@ora11 ~]$ kill -9 12823
[oracle@ora11 ~]$ exit
exit
---再次查询library cache pin等待消失
SQL> select saddr,sid,username,event,p1raw from v$session where event='library cache pin';
SQL> select saddr,sid,username,event,p1raw from v$session where event='library cache pin';
no rows selected
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28278387/viewspace-749192/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28278387/viewspace-749192/