HOW TO TRACE PLSQL SUBPROGRAMS
conn sys as sysdba--execute the script to create the required tables
@?/rdbms/admin/tracetab.sql
select owner ,object_name,object_type
from dba_objects where object_name like 'PLSQL%'
ORDER by 2,1
/
set linesize 180
CREATE PUBLIC SYNONYM plsql_trace_runs FOR plsql_trace_runs;
CREATE PUBLIC SYNONYM plsql_trace_events FOR plsql_trace_events;
CREATE PUBLIC SYNONYM plsql_trace_runnumber FOR plsql_trace_runnumber;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_runs TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_events TO PUBLIC;
GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;
conn scott/tiger
set serveroutput on
ALTER SESSION SET PLSQL_DEBUG=TRUE;
CREATE OR REPLACE PROCEDURE trace_test (p_loops IN NUMBER) AS
l_dummy NUMBER := 0;
BEGIN
FOR i IN 1 .. p_loops LOOP
SELECT l_dummy + 1
INTO l_dummy
FROM dual;
l_dummy := TO_NUMBER(TO_CHAR(l_dummy -1));
END LOOP;
END;
/
SHOW ERRORS
DECLARE
l_result BINARY_INTEGER;
BEGIN
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);
trace_test(p_loops => 100);
DBMS_TRACE.clear_plsql_trace;
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_sql);
trace_test(p_loops => 100);
DBMS_TRACE.clear_plsql_trace;
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_lines);
trace_test(p_loops => 100);
DBMS_TRACE.clear_plsql_trace;
END;
/
ALTER SESSION SET PLSQL_DEBUG=FALSE
/
select runid, run_owner
from sys.plsql_trace_runs
/
COLUMN run_date FORMAT A20
SELECT r.runid,
TO_CHAR(r.run_date, 'DD-MON-YYYY HH24:MI:SS') AS run_date,
r.run_owner
FROM plsql_trace_runs r
ORDER BY r.runid;
SET LINESIZE 200
SET TRIMOUT ON
COLUMN runid FORMAT 99999
COLUMN event_seq FORMAT 99999
COLUMN event_unit_owner FORMAT A20
COLUMN event_unit FORMAT A20
COLUMN event_unit_kind FORMAT A20
COLUMN event_comment FORMAT A30
SELECT e.runid,
e.event_seq,
TO_CHAR(e.event_time, 'DD-MON-YYYY HH24:MI:SS') AS event_time,
e.event_unit_owner,
e.event_unit,
e.event_unit_kind,
e.proc_line,
e.event_comment
FROM plsql_trace_events e
WHERE e.runid = &1
ORDER BY e.runid, e.event_seq;
-- when executed it prompt for value -->Enter value for 1: 1 The result will be like this
/*
RUNID EVENT_SEQ EVENT_TIME EVENT_UNIT_OWNER EVENT_UNIT EVENT_UNIT_KIND PROC_LINE EVENT_COMMENT
------ --------- -------------------- -------------------- -------------------- -------------------- ---------- ------------------------------
1 1 05-JUN-2017 07:47:12 PL/SQL Trace Tool started
1 2 05-JUN-2017 07:47:12 Trace flags changed
1 3 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY 75 Return from procedure call
1 4 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY 81 Return from procedure call
1 5 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY 5 Return from procedure call
1 6 05-JUN-2017 07:47:12 <anonymous> ANONYMOUS BLOCK 1 Procedure Call
.......
1 12 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY 66 Return from procedure call
1 13 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY 72 Return from procedure call
1 14 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY 21 Procedure Call
1 15 05-JUN-2017 07:47:12 PL/SQL trace stopped
15 rows selected.
*/
--Run second time and Enter value for 1: 2 The result will be like this
/*
old 10: WHERE e.runid = &1
new 10: WHERE e.runid = 2
RUNID EVENT_SEQ EVENT_TIME EVENT_UNIT_OWNER EVENT_UNIT EVENT_UNIT_KIND PROC_LINE EVENT_COMMENT
------ --------- -------------------- -------------------- -------------------- -------------------- ---------- ------------------------------
2 45 05-JUN-2017 07:47:12 SCOTT TRACE_TEST PROCEDURE SELECT :B1 + 1 FROM DUAL
2 46 05-JUN-2017 07:47:12 SCOTT TRACE_TEST PROCEDURE SELECT :B1 + 1 FROM DUAL
2 47 05-JUN-2017 07:47:12 SCOTT TRACE_TEST PROCEDURE SELECT :B1 + 1 FROM DUAL
2 48 05-JUN-2017 07:47:12 SCOTT TRACE_TEST PROCEDURE SELECT :B1 + 1 FROM DUAL
2 49 05-JUN-2017 07:47:12 SCOTT TRACE_TEST PROCEDURE SELECT :B1 + 1 FROM DUAL
*/ PL/SQL trace stopped
--Run it for the third time and Enter value for 1: 3: The result will be like this
/*
old 10: WHERE e.runid = &1
new 10: WHERE e.runid = 3
RUNID EVENT_SEQ EVENT_TIME EVENT_UNIT_OWNER EVENT_UNIT EVENT_UNIT_KIND PROC_LINE EVENT_COMMENT
------ --------- -------------------- -------------------- -------------------- -------------------- ---------- ------------------------------
3 1 05-JUN-2017 07:47:12 PL/SQL Trace Tool started
3 2 05-JUN-2017 07:47:12 Trace flags changed
3 3 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY New line executed
3 4 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY New line executed
3 5 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY New line executed
......
........
3 320 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY New line executed
3 321 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY New line executed
3 322 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY New line executed
3 323 05-JUN-2017 07:47:12 SYS DBMS_TRACE PACKAGE BODY New line executed
3 324 05-JUN-2017 07:47:12 PL/SQL trace stopped
*/
DOWNLOAD FROM HERE
No comments:
Post a Comment