ITPub博客

首页 > 应用开发 > IT综合 > to see what package is running in DB

to see what package is running in DB

原创 IT综合 作者:littledan 时间:2007-01-22 11:55:16 0 删除 编辑
Another possible idea : the following script was adapted from the original idea & research from
Steve Adams (), 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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: showsql.sql
请登录后发表评论 登录
全部评论

注册时间:2008-01-13

  • 博文量
    31
  • 访问量
    133654