Posts
如何找到正在运行的存储过程
10.2.0.3版本以后v$session等视图增加了PLSQL_*_ID字段, 可以显示出会话正在运行的存储过程
10.2.0.3以前,可以查看x$表得到运行的存储过程名
10.2.0.3 中 v$session,v$active_session_history,dba_hist_active_sess_history增加了以下字段
PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID,
PLSQL_OBJECT_ID,PLSQL_SUBPROGRAM_ID
用来查看会话正在运行哪个存储过程
11g文档上的解释
PLSQL_ENTRY_OBJECT_ID NUMBER Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack PLSQL_ENTRY_SUBPROGRAM_ID NUMBER Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack PLSQL_OBJECT_ID NUMBER Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL PLSQL_SUBPROGRAM_ID NUMBER Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL
关联all_procedures的字段object_id和subprogram_id可以查出存储过程名
1.
建存储过程p_test3, 调用p_test2, 调用p_test, 调用dbms_lock.sleep
conn / as sysdba grant execute on dbms_lock to a; conn a/a set pages 50000 line 160 set serveroutput on create or replace procedure p_test is begin dbms_lock.sleep(60); end; / show err create or replace procedure p_test2 is begin p_test; end; / show err create or replace procedure p_test3 is begin p_test2; end; / show err exec p_test3
SQL> conn / as sysdba Connected. SQL> grant execute on dbms_lock to a; Grant succeeded. SQL> conn a/a Connected. SQL> set pages 50000 line 160 SQL> set serveroutput on SQL> create or replace procedure p_test is begin dbms_lock.sleep(60); end; / show err 2 3 4 5 6 Procedure created. SQL> No errors. SQL> create or replace procedure p_test2 is begin p_test; end; / show err 2 3 4 5 6 Procedure created. SQL> No errors. SQL> create or replace procedure p_test3 is begin p_test2; end; / show err 2 3 4 5 6 Procedure created. SQL> No errors. SQL> exec p_test3
赶紧打开一新的会话
conn / as sysdba
set pages 500 linesize 160
col calling_code for a30
col username for a20
col sqltext for a40
select s.sid, s.username,
p1.object_name ||' '|| p1.procedure_name || ' ' ||
p2.object_name ||' '|| p2.procedure_name
"calling_code",
s.sql_id,
substr(st.sql_text,1,40) sqltext
from v$session s,
all_procedures p1,
all_procedures p2,
v$sql st
where s.plsql_entry_object_id = p1.object_id (+)
and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
and s.plsql_object_id = p2.object_id (+)
and s.plsql_subprogram_id = p2.subprogram_id (+)
and s.sql_id = st.sql_id(+)
order by 1,2
/
SQL> conn / as sysdba
Connected.
SQL> set pages 500 linesize 160
SQL> col calling_code for a30
SQL> col username for a20
SQL> col sqltext for a40
SQL> select s.sid, s.username,
2 p1.object_name ||' '|| p1.procedure_name || ' ' ||
3 p2.object_name ||' '|| p2.procedure_name
4 "calling_code",
5 s.sql_id,
6 substr(st.sql_text,1,40) sqltext
7 from v$session s,
8 all_procedures p1,
9 all_procedures p2,
10 v$sql st
11 where s.plsql_entry_object_id = p1.object_id (+)
12 and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
13 and s.plsql_object_id = p2.object_id (+)
14 and s.plsql_subprogram_id = p2.subprogram_id (+)
15 and s.sql_id = st.sql_id(+)
16 order by 1,2
17 /
SID USERNAME calling_code SQL_ID SQLTEXT
---------- -------------------- ------------------------------ ------------- ----------------------------------------
1623 A P_TEST3 DBMS_LOCK SLEEP 0hp250x22fzbz BEGIN p_test3; END;
1627 SYS 11rw4hjaa25xf select s.sid, s.username, p1.obje
1633
1635 4gd6b1r53yt88
1636
1640
1641
1645
1646 4gd6b1r53yt88
1647
1648
1649
1650
1651
1652
1653
1654
1655
18 rows selected.
SQL>
存储过程是嵌套调用的, 第一级调用的是P_TEST2, 最后一级是DBMS_LOCK.SLEEP2. 查询ASH
查询活动会话历史调用了什么存储过程
conn / as sysdba
set pages 500 linesize 160
col calling_code for a70
select p1.object_name ||' '|| p1.procedure_name || ' ' ||
p2.object_name ||' '|| p2.procedure_name
"calling_code",
s.sql_id,
count(*)
from v$active_session_history s,
all_procedures p1,
all_procedures p2,
v$sql st
where s.plsql_entry_object_id = p1.object_id (+)
and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
and s.plsql_object_id = p2.object_id (+)
and s.plsql_subprogram_id = p2.subprogram_id (+)
and s.sql_id = st.sql_id(+)
and s.sample_time > sysdate - &minutes/(60*24)
group by p1.object_name, p1.procedure_name,
p2.object_name, p2.procedure_name,
s.sql_id
order by count(*)
/
SQL> conn / as sysdba
Connected.
SQL> set pages 500 linesize 160
SQL> col calling_code for a70
SQL> select p1.object_name ||' '|| p1.procedure_name || ' ' ||
2 p2.object_name ||' '|| p2.procedure_name
3 "calling_code",
4 s.sql_id,
5 count(*)
6 from v$active_session_history s,
7 all_procedures p1,
8 all_procedures p2,
9 v$sql st
10 where s.plsql_entry_object_id = p1.object_id (+)
11 and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
12 and s.plsql_object_id = p2.object_id (+)
13 and s.plsql_subprogram_id = p2.subprogram_id (+)
14 and s.sql_id = st.sql_id(+)
15 and s.sample_time > sysdate - &minutes/(60*24)
16 group by p1.object_name, p1.procedure_name,
17 p2.object_name, p2.procedure_name,
18 s.sql_id
19 order by count(*)
20 /
Enter value for minutes: 60*24
old 15: and s.sample_time > sysdate - &minutes/(60*24)
new 15: and s.sample_time > sysdate - 60*24/(60*24)
calling_code SQL_ID COUNT(*)
---------------------------------------------------------------------- ------------- ----------
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC cqjwytk1ghamg 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 4y1y43113gv8f 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 11zqjqwdjb6tp 1
b2kf2cxf30jh3 1
DBMS_SPACE AUTO_SPACE_ADVISOR_JOB_PROC DBMS_SPACE OBJECT_GROWTH_TREND_ 3h4d1uux6kd0x 1
CURTAB
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC b6usrg82hwsa3 1
fskdwb6ppw164 1
DBMS_SPACE AUTO_SPACE_ADVISOR_JOB_PROC ffrxyztt4415k 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 1fp87jmavgnvs 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 402690djv4v60 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC g5kzvfybhsr96 1
P_TEST2 dvt1n1r9p84vj 1
DBMS_SPACE AUTO_SPACE_ADVISOR_JOB_PROC 0jrz5kc6a3jry 1
DBMS_SPACE AUTO_SPACE_ADVISOR_JOB_PROC cvn54b7yz0s8u 1
32hbap2vtmf53 1
9zmwy5hkhq8h7 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC DBMS_SYS_SQL PARSE 3j1qd2tnzd26w 1
PRVT_HDM AUTO_EXECUTE 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 8wpwh54q0y4ky 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC STANDARD SYSDATE b6usrg82hwsa3 1
MGMT_CONFIG COLLECT_CONFIG cvn54b7yz0s8u 1
PRVT_ADVISOR DELETE_EXPIRED_TASKS 1
9babjv8yq8ru3 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC b4anb2n74m7rv 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 5ps3p5ma94bkh 2
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 0ybwd63u2any5 2
bh0cvm22fks6k 2
4gd6b1r53yt88 3
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC cydgw456rs7z0 3
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 8a1pvy4cy8hgv 7
3vaa5k7us1n4g 8
0syfh8fyz2x2g 122
P_TEST2 P_TEST dvt1n1r9p84vj 232
930
34 rows selected.
SQL>
3. 用触发器捕获存储过程名
conn / as sysdba
grant select on sys.v_$session to a;
grant select on sys.v_$sql to a;
conn a/a
drop table t1;
create table t1 (a int);
create or replace procedure p_test
is
begin
insert into t1 values (1);
end;
/
show err
create or replace procedure p_test2
is
begin
p_test;
commit;
end;
/
show err
create or replace trigger tr_t1
after insert or update on t1
for each row
declare
v_sid int;
v_usr varchar2(30);
v_po1 varchar2(30);
v_ps1 varchar2(30);
v_po2 varchar2(30);
v_ps2 varchar2(30);
v_sql varchar2(40);
begin
select s.sid, s.username,
p1.object_name, p1.procedure_name,
p2.object_name, p2.procedure_name,
substr(sql.sql_text,1,40)
into v_sid,v_usr,v_po1,v_ps1,v_po2,v_ps2,v_sql
from v$session s, all_procedures p1, all_procedures p2, v$sql sql
where s.sid = userenv('sid')
and s.plsql_entry_object_id = p1.object_id(+)
and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
and s.plsql_object_id = p2.object_id(+)
and s.plsql_subprogram_id = p2.subprogram_id(+)
and s.sql_id = sql.sql_id
;
dbms_output.put_line('sid:'||v_sid);
dbms_output.put_line('usr:'||v_usr);
dbms_output.put_line('po1:'||v_po1);
dbms_output.put_line('ps1:'||v_ps1);
dbms_output.put_line('po2:'||v_po2);
dbms_output.put_line('ps2:'||v_ps2);
dbms_output.put_line('sql:'||v_sql);
end;
/
show err
set serveroutput on
exec p_test2
SQL> conn / as sysdba
Connected.
SQL> grant select on sys.v_$session to a;
Grant succeeded.
SQL> grant select on sys.v_$sql to a;
Grant succeeded.
SQL> conn a/a
Connected.
SQL> drop table t1;
Table dropped.
SQL> create table t1 (a int);
Table created.
SQL> create or replace procedure p_test
is
begin
insert into t1 values (1);
end;
/
show err
2 3 4 5 6
Procedure created.
SQL> No errors.
SQL> create or replace procedure p_test2
is
begin
p_test;
commit;
end;
/
show err
2 3 4 5 6 7
Procedure created.
SQL> No errors.
SQL> create or replace trigger tr_t1
2 after insert or update on t1
3 for each row
4 declare
5 v_sid int;
6 v_usr varchar2(30);
7 v_po1 varchar2(30);
8 v_ps1 varchar2(30);
9 v_po2 varchar2(30);
10 v_ps2 varchar2(30);
11 v_sql varchar2(40);
12 begin
13 select s.sid, s.username,
14 p1.object_name, p1.procedure_name,
15 p2.object_name, p2.procedure_name,
16 substr(sql.sql_text,1,40)
17 into v_sid,v_usr,v_po1,v_ps1,v_po2,v_ps2,v_sql
18 from v$session s, all_procedures p1, all_procedures p2, v$sql sql
19 where s.sid = userenv('sid')
20 and s.plsql_entry_object_id = p1.object_id(+)
21 and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
22 and s.plsql_object_id = p2.object_id(+)
23 and s.plsql_subprogram_id = p2.subprogram_id(+)
24 and s.sql_id = sql.sql_id
25 ;
26 dbms_output.put_line('sid:'||v_sid);
27 dbms_output.put_line('usr:'||v_usr);
28 dbms_output.put_line('po1:'||v_po1);
29 dbms_output.put_line('ps1:'||v_ps1);
30 dbms_output.put_line('po2:'||v_po2);
31 dbms_output.put_line('ps2:'||v_ps2);
32 dbms_output.put_line('sql:'||v_sql);
33 end;
34 /
show err
Trigger created.
SQL> No errors.
SQL> set serveroutput on
SQL> exec p_test2
sid:1623
usr:A
po1:P_TEST2
ps1:
po2:
ps2:
sql:SELECT S.SID, S.USERNAME, P1.OBJECT_NAME
PL/SQL procedure successfully completed.
SQL>
4.例子:用SYSTEM用户触发器记录存储过程名
conn / as sysdba
revoke select on sys.v_$session from a;
revoke select on sys.v_$sql from a;
conn a/a
drop table t1;
create table t1 (a int);
create or replace procedure p_test
is
begin
insert into t1 values (1);
end;
/
show err
create or replace procedure p_test2
is
begin
p_test;
commit;
end;
/
show err
drop trigger tr_t1;
conn / as sysdba
grant select on sys.v_$session to system;
grant select on dba_procedures to system;
grant select on sys.v_$sql to system;
conn system/manager
drop table t_a;
create table t_a (
sid int not null,
username varchar2(30),
plsql_entry_obj_name varchar2(40),
plsql_entry_sub_name varchar2(40),
plsql_obj_name varchar2(40),
plsql_sub_name varchar2(40),
sqltext varchar2(100),
presqltext varchar2(100),
timestamp date default sysdate not null
)
/
create or replace trigger tr_t1
before insert or update on a.t1
for each row
declare
begin
insert into t_a (
sid, username,
plsql_entry_obj_name, plsql_entry_sub_name,
plsql_obj_name, plsql_sub_name, sqltext, presqltext)
select s.sid, s.username
,p1.object_name, p1.procedure_name
,p2.object_name, p2.procedure_name
,substr(sql.sql_text,1,100)
,substr(presql.sql_text,1,100)
from v$session s
,dba_procedures p1, dba_procedures p2
,v$sql sql, v$sql presql
where s.sid = userenv('sid')
and s.plsql_entry_object_id = p1.object_id(+)
and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
and s.plsql_object_id = p2.object_id(+)
and s.plsql_subprogram_id = p2.subprogram_id(+)
and s.sql_id = sql.sql_id(+)
and s.prev_sql_id = presql.sql_id(+);
end;
/
show err
SQL> conn / as sysdba
Connected.
SQL> grant select on sys.v_$session to system;
Grant succeeded.
SQL> grant select on dba_procedures to system;
Grant succeeded.
SQL> grant select on sys.v_$sql to system;
Grant succeeded.
SQL> conn system/manager
Connected.
SQL> drop table t_a;
Table dropped.
SQL> create table t_a (
2 sid int not null,
3 username varchar2(30),
4 plsql_entry_obj_name varchar2(40),
5 plsql_entry_sub_name varchar2(40),
6 plsql_obj_name varchar2(40),
7 plsql_sub_name varchar2(40),
8 sqltext varchar2(100),
9 presqltext varchar2(100),
10 timestamp date default sysdate not null
11 )
12 /
Table created.
SQL> create or replace trigger tr_t1
2 before insert or update on a.t1
3 for each row
4 declare
5 begin
6 insert into t_a (
7 sid, username,
8 plsql_entry_obj_name, plsql_entry_sub_name,
9 plsql_obj_name, plsql_sub_name, sqltext, presqltext)
10 select s.sid, s.username
11 ,p1.object_name, p1.procedure_name
12 ,p2.object_name, p2.procedure_name
13 ,substr(sql.sql_text,1,100)
14 ,substr(presql.sql_text,1,100)
15 from v$session s
16 ,dba_procedures p1, dba_procedures p2
17 ,v$sql sql, v$sql presql
18 where s.sid = userenv('sid')
19 and s.plsql_entry_object_id = p1.object_id(+)
20 and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
21 and s.plsql_object_id = p2.object_id(+)
22 and s.plsql_subprogram_id = p2.subprogram_id(+)
23 and s.sql_id = sql.sql_id(+)
24 and s.prev_sql_id = presql.sql_id(+);
25 end;
26 /
show err
Trigger created.
SQL> No errors.
SQL>
用户A运行p_test2和insert语句
conn a/a exec p_test2 insert into t1 values (2); commit;
SQL> conn a/a Connected. SQL> exec p_test2 PL/SQL procedure successfully completed. SQL> insert into t1 values (2); commit; 1 row created. SQL> Commit complete. SQL>
SYSTEM查询
conn system/manager select * from t_a;
SQL> select * from t_a;
SID USERNAME PLSQL_ENTRY_OBJ_NAME PLSQL_ENTRY_SUB_NAME
------- ---------- ---------------------------------------- ----------------------------------------
PLSQL_OBJ_NAME PLSQL_SUB_NAME SQLTEXT
---------------------------------------- ---------------------------------------- ----------------------------------------
PRESQLTEXT TIMESTAMP
---------------------------------------------------------------------------------------------------- ------------------
1623 A P_TEST2
INSERT INTO T_A ( SID, USERNAME, PLSQL_E
NTRY_OBJ_NAME, PLSQL_ENTRY_SUB_NAME, PLS
QL_OBJ_NAME, PLSQL_S
SELECT DECODE('A','A','1','2') FROM DUAL 18-FEB-09
1623 A TR_T1
insert into t1 values (2)
BEGIN p_test2; END; 18-FEB-09
SQL>
第1条记录: 调用存储过程插表，触发了SYSTEM的触发器, 存储过程名是P_TEST2第2条记录: 运行SQL语句插表，触发了SYSTEM的触发器, 存储过程名是触发器名TR_T1
5. 使用dbms_utility.format_call_stack显示PL/SQL调用栈的信息
修改4
conn a/a
drop table t1;
create table t1 (a int);
create or replace procedure p_test
is
begin
insert into t1 values (1);
end;
/
show err
create or replace procedure p_test2
is
begin
p_test;
commit;
end;
/
show err
conn / as sysdba
grant select on sys.v_$session to system;
grant select on dba_procedures to system;
grant select on sys.v_$sql to system;
conn system/manager
drop table t_a;
create table t_a (
sid int not null,
audsid int not null,
username varchar2(30),
plsql_entry_obj_name varchar2(40),
plsql_entry_sub_name varchar2(40),
plsql_obj_name varchar2(40),
plsql_sub_name varchar2(40),
sqltext varchar2(100),
presqltext varchar2(100),
call_stack varchar2(2000),
timestamp date default sysdate not null
)
/
create or replace trigger tr_t1
before insert or update on a.t1
for each row
declare
begin
insert into t_a (
sid, audsid ,username
,plsql_entry_obj_name, plsql_entry_sub_name
,plsql_obj_name, plsql_sub_name, sqltext, presqltext
,call_stack)
select s.sid, s.audsid, s.username
,p1.object_name, p1.procedure_name
,p2.object_name, p2.procedure_name
,substr(sql.sql_text,1,100)
,substr(presql.sql_text,1,100)
,substr(dbms_utility.format_call_stack,1,2000)
from v$session s
,dba_procedures p1, dba_procedures p2
,v$sql sql, v$sql presql
where s.audsid = userenv('sessionid')
and s.plsql_entry_object_id = p1.object_id(+)
and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
and s.plsql_object_id = p2.object_id(+)
and s.plsql_subprogram_id = p2.subprogram_id(+)
and s.sql_id = sql.sql_id(+)
and s.prev_sql_id = presql.sql_id(+);
end;
/
show err
conn a/a
exec p_test2
insert into t1 values (2);
commit;
conn system/manager
select * from t_a;
SQL> select * from t_a;
SID AUDSID USERNAME PLSQL_ENTRY_OBJ_NAME PLSQL_ENTRY_SUB_NAME
------- ---------- ---------- ---------------------------------------- ----------------------------------------
PLSQL_OBJ_NAME PLSQL_SUB_NAME
---------------------------------------- ----------------------------------------
SQLTEXT
----------------------------------------------------------------------------------------------------
PRESQLTEXT
----------------------------------------------------------------------------------------------------
CALL_STACK
--------------------------------------------------------------------------------------------------------------------------------------------
TIMESTAMP
------------------
1627 400061 A P_TEST2
TR_T1
INSERT INTO T1 VALUES (1)
----- PL/SQL Call Stack -----
object line object
handle number name
0xcfef6008 1 anonymous block
0xcff032f0 3 SYSTEM.TR_T1
0xde5c57f0 4 procedure A.P_TEST
0xde5ac390 4 procedure A.P_TEST2
0xde5a3000 1 anonymous block
18-FEB-09
1627 400061 A TR_T1
insert into t1 values (2)
BEGIN p_test2; END;
----- PL/SQL Call Stack -----
object line object
handle number name
0xcfef6008 1 anonymous block
0xcff032f0 3 SYSTEM.TR_T1
18-FEB-09
SQL>
6. 查询x$表得到正在运行的存储过程名
conn / as sysdba
grant execute on dbms_lock to a;
conn a/a
set pages 50000 line 160
set serveroutput on
create or replace procedure p_test
is
begin
dbms_lock.sleep(60);
end;
/
show err
create or replace procedure p_test2
is
begin
p_test;
end;
/
show err
create or replace procedure p_test3
is
begin
p_test2;
end;
/
show err
drop table t1;
create table t1 (a int);
create or replace trigger tr_t1
before insert on t1
for each row
declare
begin
p_test3;
end;
/
show err
insert into t1 values (1);
conn / as sysdba
set pages 50000 line 140
col owner for a10
col name for a20
col sid for 999999
col username for a10
col program for a20
col module for a20
col action for a20
col client_info for a20
select decode(o.kglobtyp,
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
12, 'TRIGGER',
13, 'CLASS') "TYPE",
substr(o.kglnaown,1,20) "OWNER",
substr(o.kglnaobj,1,35) "NAME",
s.indx "SID",
s.ksuseser "SERIAL",
s.ksuudnam "USERNAME",
s.ksuseapp "PROGRAM",
x.app "MODULE",
x.act "ACTION",
x.clinfo "CLIENT_INFO"
from sys.x$kglob o,
sys.x$kglpn p,
sys.x$ksuse s,
sys.x$ksusex x
where o.inst_id = userenv('Instance')
and p.inst_id = userenv('Instance')
and s.inst_id = userenv('Instance')
and o.kglhdpmd = 2
and o.kglobtyp in (7, 8, 9, 12, 13)
and p.kglpnhdl = o.kglhdadr
and s.addr = p.kglpnses
and x.inst_id = userenv('Instance')
and x.sid = s.indx
and x.serial = s.ksuseser
order by 1,2,3
/
SQL> set pages 50000 line 140
SQL> col owner for a10
SQL> col name for a20
SQL> col sid for 999999
SQL> col username for a10
SQL> col program for a20
SQL> col module for a20
SQL> col action for a20
SQL> col client_info for a20
SQL> select decode(o.kglobtyp,
2 7, 'PROCEDURE',
3 8, 'FUNCTION',
4 9, 'PACKAGE',
5 12, 'TRIGGER',
6 13, 'CLASS') "TYPE",
7 substr(o.kglnaown,1,20) "OWNER",
8 substr(o.kglnaobj,1,35) "NAME",
9 s.indx "SID",
10 s.ksuseser "SERIAL",
11 s.ksuudnam "USERNAME",
12 s.ksuseapp "PROGRAM",
13 x.app "MODULE",
14 x.act "ACTION",
x.clinfo "CLIENT_INFO"
15 16 from sys.x$kglob o,
17 sys.x$kglpn p,
18 sys.x$ksuse s,
19 sys.x$ksusex x
20 where o.inst_id = userenv('Instance')
21 and p.inst_id = userenv('Instance')
22 and s.inst_id = userenv('Instance')
23 and o.kglhdpmd = 2
24 and o.kglobtyp in (7, 8, 9, 12, 13)
25 and p.kglpnhdl = o.kglhdadr
26 and s.addr = p.kglpnses
27 and x.inst_id = userenv('Instance')
28 and x.sid = s.indx
29 and x.serial = s.ksuseser
30 order by 1,2,3
31 /
TYPE OWNER NAME SID SERIAL USERNAME PROGRAM MODULE ACTION
--------- ---------- -------------------- ------- ---------- ---------- -------------------- -------------------- --------------------
CLIENT_INFO
--------------------
PACKAGE SYS DBMS_LOCK 1627 271 A SQL*Plus SQL*Plus
PROCEDURE A P_TEST 1627 271 A SQL*Plus SQL*Plus
PROCEDURE A P_TEST2 1627 271 A SQL*Plus SQL*Plus
PROCEDURE A P_TEST3 1627 271 A SQL*Plus SQL*Plus
TRIGGER A TR_T1 1627 271 A SQL*Plus SQL*Plus
SQL>
7. 查询x$表得到运行过的SQL和存储过程的对应关系
见
what package/procedure did SQL come from?
Relationship between SQL statements in shared pool
SCRIPT: HOW TO IDENTIFY what packages are in the shared_pool and how many times have they been executed
外部链接:
-----
10.2.0.3新增字段
ASH – Active Session History Feel the Power
Action, Module, Program ID and V$SQL...
V$SESSION
V$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY
-----
X$表
How can I tell if a procedure/package is running?, 2
executing_packages.sql
How can I track the execution of PL/SQL and SQL?
该文也介绍了用dbms_application_info跟踪PLSQL的运行
Relationship between SQL statements in shared pool
SCRIPT: HOW TO IDENTIFY what packages are in the shared_pool and how many times have they been executed
Troubleshooting and Diagnosing ORA-4031 Error
解读X$表
Oracle X$ Tables
ORA-600 Lookup Error Categories
-----
PLSQL调用栈
FORMAT_CALL_STACK Function
How Can I find out who called me or what my name is
-fin-
No comments:
Post a Comment