to see what package is running in DB

最新推荐文章于 2024-11-12 14:38:53 发布

ctpyfkdu018623

最新推荐文章于 2024-11-12 14:38:53 发布

阅读量232

点赞数

文章标签：开发工具

本文介绍了一种监控Oracle数据库中执行包的方法，通过运行特定的SQL脚本来获取当前正在执行的过程、函数、触发器等信息。该脚本由Steve Adams提供，并在Personal8ir3环境中进行了验证。

摘要由CSDN通过智能技术生成

Another possible idea : the following script was adapted from the original idea & research from
Steve Adams (www.ixora.com.au), I tested in Personal 8ir3 and it worked as desired :

step a) in a quiet database, run the script :

sys@PO8IR3:SQL>@v8_running_sqls
**** Currently Executing Packages ****

no rows selected

sys@PO8IR3:SQL>get v8_running_sqls
1 prompt **** Currently Executing Packages ****
2 SELECT
3 substr(DECODE(o.kglobtyp,
4 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,
5 'CLASS'),1,15) "TYPE",
6 substr(o.kglnaown,1,30) "OWNER",
7 substr(o.kglnaobj,1,30) "NAME",
8 s.indx "SID",
9 s.ksuseser "SERIAL"
10 FROM
11 sys.X$KGLOB o,
12 sys.X$KGLPN p,
13 sys.X$KSUSE s
14 WHERE
15 o.inst_id = USERENV('Instance') AND
16 p.inst_id = USERENV('Instance') AND
17 s.inst_id = USERENV('Instance') AND
18 o.kglhdpmd = 2 AND
19 o.kglobtyp IN (7, 8, 9, 12, 13) AND
20 p.kglpnhdl = o.kglhdadr AND
21 s.addr = p.kglpnses
22* ORDER BY 1, 2, 3

step b) in another session (not showing) I'm running a procedure who call DBMS_LOCK.SLEEP, and with
the other session running the proc :

sys@PO8IR3:SQL>@v8_running_sqls
**** Currently Executing Packages ****

TYPE OWNER NAME SID SERIAL
------- ------ ------------ --- ----------
PACKAGE SYS DBMS_LOCK 12 3807
PROCEDURE SCOTT PROC_RUN 12 3807

step c) after the execution (BUT with the other session still conected/alive, just idle) :

sys@PO8IR3:SQL>@v8_running_sqls
**** Currently Executing Packages ****

no rows selected

Regards,

Chiappa[@more@]

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

转载于:http://blog.itpub.net/412/viewspace-892302/