RE: ora1652 question...
1. Easier, but requires a bounce : Add the following event into init.ora event=1652 trace name processstate level 10 You should be able to set this dynamically as well (tested on 8.1.7). SQL alter system set events '1652 trace name processstate level 10'; System altered. Replace level 10 with off to disable it. -- My employers like me, but not enough to let me speak for them. Greg Norris Sprint LTD Database Administration -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Norris, Gregory T [ITS] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: ora1652 question...
Fewcomments 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 notset 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 nameerrorstack
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 exactlydid the error occur, can be got by using dbms_systems
functions format_call_stack and format_error_stack.
Here's a simpleexample of usage:
SQL create table testtab (a
number);
Table created.
SQL create table log (msg
varchar2(2000));
Table created.
SQLSQL 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.
SQLSQL create or replace
procedure test_proc is 2 begin 3
insert into testtab (a) values (1); 4
commit; 5 end; 6 /
Procedure created.
SQLSQL exec
test_proc
PL/SQL procedure successfully
completed.
SQLSQL select * from
log;
MSG-
PL/SQL Call Stack - object
line object handle number
name2F71866C 1
anonymous block2F743DB8
3
ADMIN.TEST_TRIG2F73D358
3 procedure
ADMIN.TEST_PROC2F6BF024
1 anonymous block
If you start reading the stack from bottom up you
see that I executed an anonymous block ("exec testproc" command whichis
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 10SQL select trigger_body from dba_triggers where
trigger_name = 'TEST_TRIG';
TRIGGER_BODYdeclarebegininsert
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.
Re: ora1652 question...
(repost)
Fewcomments 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 notset 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 nameerrorstack
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 exactlydid the error occur, can be got by using dbms_systems
functions format_call_stack and format_error_stack.
Here's a simpleexample of usage:
SQL create table testtab (a
number);
Table created.
SQL create table log (msg
varchar2(2000));
Table created.
SQLSQL 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.
SQLSQL create or replace
procedure test_proc is 2 begin 3
insert into testtab (a) values (1); 4
commit; 5 end; 6 /
Procedure created.
SQLSQL exec
test_proc
PL/SQL procedure successfully
completed.
SQLSQL select * from
log;
MSG-
PL/SQL Call Stack - object
line object handle number
name2F71866C 1
anonymous block2F743DB8
3
ADMIN.TEST_TRIG2F73D358
3 procedure
ADMIN.TEST_PROC2F6BF024
1 anonymous block
If you start reading the stack from bottom up you
see that I executed an anonymous block ("exec testproc" command whichis
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 10SQL select trigger_body from dba_triggers where
trigger_name = 'TEST_TRIG';
TRIGGER_BODYdeclarebegininsert
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.
ora1652 question...
Is there an event to set where I can identify any sql that receives a 1652 error message? There is some process running each night in a reporting database that has been generating this error for the past week. I figured someone would complain. That didn't happen so I went and asked the reporting people if any of the reports were blowing up. They said no. I just set up statspack and will run that every 10 minutes tonight. I also have a query that will capture the session info on sessions currently sorting that I will run every 10 minutes. Neither of the techniques are very direct. I would imagine there is an event to set so that I can generate a trace file. Any other suggestions of nailing this down would be appreciated. ..and so I don't have to ask about events anymore...where do I find what event means what? Thanks, Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chris Stephens INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: ora1652 question...
Chris, There are two options: 1. Easier, but requires a bounce : Add the following event into init.ora event=1652 trace name processstate level 10 This will dump the processstate for processing that encounter an ORA-01652. 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) 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. Hth, John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Chris Stephens [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:10 PM To: Multiple recipients of list ORACLE-L Subject: ora1652 question... Is there an event to set where I can identify any sql that receives a 1652 error message? There is some process running each night in a reporting database that has been generating this error for the past week. I figured someone would complain. That didn't happen so I went and asked the reporting people if any of the reports were blowing up. They said no. I just set up statspack and will run that every 10 minutes tonight. I also have a query that will capture the session info on sessions currently sorting that I will run every 10 minutes. Neither of the techniques are very direct. I would imagine there is an event to set so that I can generate a trace file. Any other suggestions of nailing this down would be appreciated. ..and so I don't have to ask about events anymore...where do I find what event means what? Thanks, Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chris Stephens INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: ora1652 question...
You could also use a trigger to get the info to a temp table as follows. This will probably fire after any error and could be performance hit. (happy new year to all).: create or replace trigger system.server_1652_error_trig after servererror on database declare v_audsid number; v_username varchar2(40); begin if (ora_server_error(1) = 01652) then Chris Stephens [EMAIL PROTECTED] wrote: Is there an event to set where I can identify any sql that receives a 1652error message?There is some process running each night in a reporting database that hasbeen generating this error for the past week. I figured someone wouldcomplain. That didn't happen so I went and asked the reporting people ifany of the reports were blowing up. They said no. I just set up statspackand will run that every 10 minutes tonight. I also have a query that willcapture the session info on sessions currently sorting that I will run every10 minutes. Neither of the techniques are very direct. I would imaginethere is an event to set so that I can generate a trace file. Any othersuggestions of nailing this down would be appreciated...and so I don't have to ask about events anymore...where do I find whatevent means what?Thanks,Chris! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Chris StephensINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
RE: ora1652 question...
John has already shown you how to generate a trace when an error is encountered via the 'events' mechanism. If you would like to see what events are available, peruse the file $ORACLE_HOME/rdbms/mesg/oraus.msg if you are on *nix. If you are on win32, the file is unfortunately not available. Start with error code 1. There may or may not be an explanation for an event in that file. Jared On Tue, 2004-01-06 at 15:44, John Kanagaraj wrote: Chris, There are two options: 1. Easier, but requires a bounce : Add the following event into init.ora event=1652 trace name processstate level 10 This will dump the processstate for processing that encounter an ORA-01652. 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) 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. Hth, John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Chris Stephens [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:10 PM To: Multiple recipients of list ORACLE-L Subject: ora1652 question... Is there an event to set where I can identify any sql that receives a 1652 error message? There is some process running each night in a reporting database that has been generating this error for the past week. I figured someone would complain. That didn't happen so I went and asked the reporting people if any of the reports were blowing up. They said no. I just set up statspack and will run that every 10 minutes tonight. I also have a query that will capture the session info on sessions currently sorting that I will run every 10 minutes. Neither of the techniques are very direct. I would imagine there is an event to set so that I can generate a trace file. Any other suggestions of nailing this down would be appreciated. ..and so I don't have to ask about events anymore...where do I find what event means what? Thanks, Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chris Stephens INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
