| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ora1652 question...
Content-Type: text/plain;
charset="windows-1257"
Content-Transfer-Encoding: quoted-printable
(repost)
Few comments inline:
> 1. Easier, but requires a bounce : Add the following event into =
init.ora
>=20
> event=3D"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
>=20
>=20
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.
>=20
>=20
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.=20
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).=20
This command executed stored procedure TEST_PROC on line 1, which caused =
TEST_TRIG to fire on line 3.=20
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 =3D '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 =3D =
'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.
------=_NextPart_000_22D7_01C3D576.2E868190
Content-Type: text/html;
charset="windows-1257"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1257">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#e0e0e0>
<DIV><FONT face=3DArial size=3D2>(repost)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Few comments inline:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>> 1. Easier, but requires a bounce : =
Add the=20
following event into init.ora<BR>> <BR>> event=3D"1652 trace name=20
processstate level 10"<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>You can use alter system and =
dbms_system.set_ev in=20
combination to force an event for all new and existing sessions, without =
a=20
bounce (alter system does not set events for already existing=20
sessions).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>> And you can even add the following =
to capture=20
1555 and 4031 errors<BR>> <BR>> event=3D"1555 trace name =
errorstack level=20
3"<BR>> event=3D"4031 trace name errorstack level 3"<BR>> <BR>> =
**BUT**,=20
keep _all_ 'event' lines together in the file (just as with<BR>> =
utl_file_dir=20
entries)<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Btw, you can have all needed events set =
with only=20
one line as well, for example:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>alter session set events '1555 trace =
name=20
processstate level 10; name errorstack level 3:4031 trace =
name errorstack=20
level 3';</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>This sets both processtate AND =
errorstack handlers=20
(actions) for event 1555 AND an errorstack handler for event=20
4031.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>The colon separates events, and =
semicolons separate=20
handlers for a specific event.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>> <BR>> 2. Harder (requires =
coding/testing),=20
but better control and options:<BR>> <BR>> Create a System-level =
ON=20
SERVERERROR trigger and check for 1652 (among<BR>> others) and record =
all the=20
details into either alert.log (via<BR>> dbms_system.ksdwrt call), =
database=20
table, utl_file etc.<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Yep, that's a good idea. Some help for =
finding out=20
where exactly did the error occur, can be got by using dbms_systems =
functions format_call_stack and format_error_stack. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Here's a simple example of =
usage:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL> create table testtab (a =
number);</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Table created.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL> create table log (msg=20
varchar2(2000));</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Table created.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL><BR>SQL> create or =
replace=20
trigger test_trig<BR> 2 before insert on testtab<BR> =
3 =20
declare<BR> 4 begin<BR> 5 insert into log (msg) =
values=20
(dbms_utility.format_call_stack);<BR> 6 end;<BR> =
7 =20
/</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Trigger created.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL><BR>SQL> create or =
replace=20
procedure test_proc is<BR> 2 begin<BR> =
3 =20
insert into testtab (a) values (1);<BR> 4 =20
commit;<BR> 5 end;<BR> 6 /</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Procedure created.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL><BR>SQL> exec=20
test_proc</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>PL/SQL procedure successfully=20
completed.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL><BR>SQL> select * =
from=20
log;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New"=20
size=3D2>MSG<BR>---------------------------------------------------------=
-------------------<BR>-----=20
PL/SQL Call Stack -----<BR> object =20
line object<BR> handle number =20
name<BR>2F71866C 1 =
anonymous =
block<BR>2F743DB8 =20
<STRONG>3</STRONG> =20
ADMIN.TEST_TRIG<BR>2F73D358 &nbs=
p;=20
<STRONG>3</STRONG> procedure=20
ADMIN.TEST_PROC<BR>2F6BF024 &nbs=
p;=20
1 anonymous block</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>If you start reading the stack from =
bottom up you=20
see that I executed an anonymous block ("exec testproc" command =
which is=20
translated internally to "BEGIN test_proc; END;" in sqlplus).=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>This command executed stored procedure =
TEST_PROC on=20
line 1, which caused TEST_TRIG to fire on line 3. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>And TEST_TRIG caused an insert cursor =
to open on=20
its 3rd line (although it shows just "anynymous block" in object name, =
you can=20
verify it's a cursor using a library cache dump, field type for given =
object=20
handle is "CRSR").</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial =
size=3D2></FONT><FONT=20
face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I'm not sure whether you can map the =
line number=20
from stack with any DD objects, but at least in this test procedure =
lines seem=20
to match with their corresponding ones from DBA_SOURCE and trigger lines =
match=20
theirs from DBA_TRIGGERS.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT><BR><FONT face=3D"Courier New" =
size=3D2>SQL>=20
select line, text from dba_source where name =3D =
'TEST_PROC';</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2> =
LINE=20
TEXT<BR>----------=20
-----------------------------------------------------------------<BR>&nbs=
p; =20
1 procedure test_proc =
is<BR> 2=20
begin<BR> =
<STRONG>3 =20
insert into testtab (a) values=20
(1);</STRONG><BR> =
4 =20
commit;<BR> 5 =
end;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>SQL> set long =
100000<BR></FONT><FONT=20
face=3D"Courier New" size=3D2>SQL> select trigger_body from =
dba_triggers where=20
trigger_name =3D 'TEST_TRIG';</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New"=20
size=3D2>TRIGGER_BODY<BR>------------------------------------------------=
----------------------------<BR>declare<BR>begin<BR><STRONG>insert=20
into log (msg) values=20
(dbms_utility.format_call_stack);</STRONG><BR>end;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Note that format_call_stack only =
returns first 2000=20
bytes from PL/SQL stack.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Tanel.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV></BODY></HTML>
------=_NextPart_000_22D7_01C3D576.2E868190--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tanel Poder
INET: tanel.poder.003_at_mail.ee
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Jan 07 2004 - 15:29:42 CST
 |
 |