Few comments inline:
> 1. Easier, but requires a bounce : Add the
following event into init.ora
>
> event="1652 trace name processstate level 10"
>
> event="1652 trace name processstate level 10"
You can use alter system and dbms_system.set_ev in
combination to force an event for all new and existing sessions, without a
bounce (alter system does not set events for already existing
sessions).
> And you can even add the following to capture
1555 and 4031 errors
>
> event="1555 trace name errorstack level 3"
> event="4031 trace name errorstack level 3"
>
> **BUT**, keep _all_ 'event' lines together in the file (just as with
> utl_file_dir entries)
>
> event="1555 trace name errorstack level 3"
> event="4031 trace name errorstack level 3"
>
> **BUT**, keep _all_ 'event' lines together in the file (just as with
> utl_file_dir entries)
Btw, you can have all needed events set with only
one line as well, for example:
alter session set events '1555 trace name
processstate level 10; name errorstack level 3:4031 trace name errorstack
level 3';
This sets both processtate AND errorstack handlers
(actions) for event 1555 AND an errorstack handler for event
4031.
The colon separates events, and semicolons separate
handlers for a specific event.
>
> 2. Harder (requires coding/testing), but better control and options:
>
> Create a System-level ON SERVERERROR trigger and check for 1652 (among
> others) and record all the details into either alert.log (via
> dbms_system.ksdwrt call), database table, utl_file etc.
> 2. Harder (requires coding/testing), but better control and options:
>
> Create a System-level ON SERVERERROR trigger and check for 1652 (among
> others) and record all the details into either alert.log (via
> dbms_system.ksdwrt call), database table, utl_file etc.
Yep, that's a good idea. Some help for finding out
where exactly did the error occur, can be got by using dbms_systems
functions format_call_stack and format_error_stack.
Here's a simple example of usage:
SQL> create table testtab (a
number);
Table created.
SQL> create table log (msg
varchar2(2000));
Table created.
SQL>
SQL> create or replace trigger test_trig
2 before insert on testtab
3 declare
4 begin
5 insert into log (msg) values (dbms_utility.format_call_stack);
6 end;
7 /
SQL> create or replace trigger test_trig
2 before insert on testtab
3 declare
4 begin
5 insert into log (msg) values (dbms_utility.format_call_stack);
6 end;
7 /
Trigger created.
SQL>
SQL> create or replace procedure test_proc is
2 begin
3 insert into testtab (a) values (1);
4 commit;
5 end;
6 /
SQL> create or replace procedure test_proc is
2 begin
3 insert into testtab (a) values (1);
4 commit;
5 end;
6 /
Procedure created.
SQL>
SQL> exec test_proc
SQL> exec test_proc
PL/SQL procedure successfully
completed.
SQL>
SQL> select * from log;
SQL> select * from log;
MSG
----------------------------------------------------------------------------
----- PL/SQL Call Stack -----
object line object
handle number name
2F71866C 1 anonymous block
2F743DB8 3 ADMIN.TEST_TRIG
2F73D358 3 procedure ADMIN.TEST_PROC
2F6BF024 1 anonymous block
----------------------------------------------------------------------------
----- PL/SQL Call Stack -----
object line object
handle number name
2F71866C 1 anonymous block
2F743DB8 3 ADMIN.TEST_TRIG
2F73D358 3 procedure ADMIN.TEST_PROC
2F6BF024 1 anonymous block
If you start reading the stack from bottom up you
see that I executed an anonymous block ("exec testproc" command which is
translated internally to "BEGIN test_proc; END;" in sqlplus).
This command executed stored procedure TEST_PROC on
line 1, which caused TEST_TRIG to fire on line 3.
And TEST_TRIG caused an insert cursor to open on
its 3rd line (although it shows just "anynymous block" in object name, you can
verify it's a cursor using a library cache dump, field type for given object
handle is "CRSR").
I'm not sure whether you can map the line number
from stack with any DD objects, but at least in this test procedure lines seem
to match with their corresponding ones from DBA_SOURCE and trigger lines match
theirs from DBA_TRIGGERS.
SQL> select line, text from dba_source where name = 'TEST_PROC';
LINE
TEXT
---------- -----------------------------------------------------------------
1 procedure test_proc is
2 begin
3 insert into testtab (a) values (1);
4 commit;
5 end;
---------- -----------------------------------------------------------------
1 procedure test_proc is
2 begin
3 insert into testtab (a) values (1);
4 commit;
5 end;
SQL> set long 100000
SQL> select trigger_body from dba_triggers where trigger_name = 'TEST_TRIG';
SQL> select trigger_body from dba_triggers where trigger_name = 'TEST_TRIG';
TRIGGER_BODY
----------------------------------------------------------------------------
declare
begin
insert into log (msg) values (dbms_utility.format_call_stack);
end;
----------------------------------------------------------------------------
declare
begin
insert into log (msg) values (dbms_utility.format_call_stack);
end;
Note that format_call_stack only returns first 2000
bytes from PL/SQL stack.
Tanel.