epipko_at_gmail.com wrote:
> It makes sense.
> I am trying to log an event when d/b is down or user can't connect to
> it.
>
> fitzjarrell_at_cox.net wrote:
>> epipko_at_gmail.com wrote:
>>> Hi all,
>>> I have a trigger to catch ddl errors. I am trying (unsuccessful so far)
>>> to catch ORA-03113.
>>> I don't see this error in the list of errors Oracle can't catch and
>>> don't know why it doesnt' work.
>>> ----------------------------------------------------------------------------------------------------------
>>> sql> DROP TABLE SERVERERROR_LOG;
>>>
>>> Table dropped.
>>>
>>> sql> CREATE TABLE SERVERERROR_LOG (
>>> 2 err_user VARCHAR2(30),
>>> 3 err_host VARCHAR2(30),
>>> 4 err_program varchar2(30),
>>> 5 err_datetime TIMESTAMP,
>>> 6 err_stack VARCHAR2(2000),
>>> 7 err_sql VARCHAR2(2000));
>>>
>>> Table created.
>>>
>>> CREATE OR REPLACE TRIGGER UB_OBJECTS.log_server_errors
>>> AFTER SERVERERROR ON DATABASE
>>> DECLARE
>>> sql_text ora_name_list_t;
>>> sql_stmt VARCHAR2(2000);
>>> vProgram VARCHAR2(30);
>>>
>>> BEGIN
>>> SELECT
>>> program
>>> INTO
>>> vProgram
>>> FROM v$session
>>> WHERE audsid=USERENV('sessionid');
>>>
>>> FOR i IN 1..ora_sql_txt(sql_text)
>>> LOOP
>>> sql_stmt := sql_stmt || sql_text(i);
>>> END LOOP;
>>>
>>> INSERT INTO SERVERERROR_LOG
>>> (err_user,
>>> err_host,
>>> err_program,
>>> err_datetime,
>>> err_stack,
>>> err_sql)
>>> VALUES
>>> ((SELECT SYS_CONTEXT ('USERENV', 'OS_USER') FROM DUAL),
>>> (SELECT SYS_CONTEXT ('USERENV', 'HOST') FROM DUAL),
>>> vProgram,
>>> SYSTIMESTAMP,
>>> DBMS_UTILITY.FORMAT_ERROR_STACK,
>>> sql_stmt);
>>>
>>> END log_server_errors;
>>>
>>> sql> select * from dualllll
>>> *
>>> ERROR at line 1:
>>> ORA-00942: table or view does not exist
>>>
>>> sql> select err_stack from servererror_log;
>>>
>>> ERR_STACK
>>> ----------------------------------------
>>> ORA-00942: table or view does not exist
>>>
>>> sql> select dbms_metadata.get_ddl('TABLE','PD2_WORK_ORDER')from dual;
>>> ERROR:
>>> ORA-03113: end-of-file on communication channel
>>>
>>> sql> select err_stack from servererror_log;
>>> ERR_STACK
>>> ----------------------------------------
>>> ORA-00942: table or view does not exist
>>> ----------------------------------------------------------------------------------------------------------
>>>
>>> Why can't I catch 3113???
>>>
>>> Thanks,
>>> Eugene
>> Does the fact that you're no longer CONNECTED to the database provide
>> any clue? ORA-03113 is an informational message from SQL*Plus reported
>> after the connection is lost. You can't capture that which isn't
>> there.
>>
>>
>> David Fitzjarrell
>
Please don't top post.
Think about what you just said. If the database is down how is it going
to fire a trigger?
If the user can't connect to the database for some other reason (any
other reason), how is the database going to fire a trigger regarding a
session that never got started?
Even for an ora-3113 (dropped connection), the database may not even
know that the connection dropped unless DCD is enabled.
Some errors simply cannot be trapped.
Received on Tue Jul 25 2006 - 16:16:15 CDT
