Re: ora1652 question...

[Tanel Poder]

(repost) Few comments inline:   > 1. Easier, but requires a bounce : Add the following event into init.ora > > 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) 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. 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  /   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  /   Procedure created.   SQL> SQL> exec test_proc   PL/SQL procedure successfully completed.   SQL> 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   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;   SQL> set long 100000 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;     Note that format_call_stack only returns first 2000 bytes from PL/SQL stack.   Tanel.