Re: Capturing Failed Sql

i hope this is what you are lookign for

CREATE TABLE APPLICATION_ERRORS (

DB_ERR_ID NUMBER, DB_ERR_NBR NUMBER(10), DB_ERR_DTTM DATE, DB_ERR_USERNAME VARCHAR2(30 BYTE), DB_ERR_OSUSER VARCHAR2(30 BYTE), DB_ERR_MACHINE VARCHAR2(64 BYTE), DB_ERR_PROCESS VARCHAR2(8 BYTE), DB_ERR_PROGRAM VARCHAR2(48 BYTE), DB_ERR_CLIENTIP VARCHAR2(50 BYTE), DB_ERR_MSG VARCHAR2(4000 BYTE), DB_ERR_SQL_TEXT VARCHAR2(2000 BYTE) )

CREATE SEQUENCE TEMPDBA.SQ_DB_ERROR START WITH 135978

MAXVALUE 9999999999999999999999

MINVALUE 1 CYCLE CACHE 20 NOORDER; CREATE OR REPLACE TRIGGER TR_APPLICATION_ERR AFTER servererror

ON DATABASE DECLARE v_username VARCHAR2(30);

v_osuser VARCHAR2(30);

v_machine VARCHAR2(64);

v_process VARCHAR2(8);

v_program VARCHAR2(48);

--

stmt VARCHAR2 (2000);

sql_text ora_name_list_t;

l VARCHAR2(2000);  

BEGIN SELECT username, osuser, machine, process, program

INTO v_username, v_osuser, v_machine, v_process, v_program

FROM sys.v_$session

WHERE audsid = USERENV('sessionid');  

IF

((ora_is_servererror(4043)) OR (ora_is_servererror(1017)))

THEN stmt := 'no error stack found';

ELSE l := ora_sql_txt(sql_text);

FOR i IN 1..l LOOP

stmt :=stmt||sql_text(i);

END LOOP; END IF; IF (ora_is_rerror(918)) OR (ora_is_servererror(1446)) OR (ora_is_servererror(1445)))

THEN NULL; ELSE FOR n IN 1..ora_server_error_depth LOOP

INSERT INTO application_errors VALUES (sq_db_error.NEXTVAL, ora_server_error(n), SYSDATE, ora_login_user, v_osuser, v_machine, v_process, v_program, ora_client_ip_address, ora_server_error_msg(n),SUBSTR(stmt,1,900));

END LOOP; END IF; END tr_application_err;

/
"Mercadante, Thomas F" <thomas.mercadante_at_labor.state.ny.us> wrote: All,

Occasionally, I have a Cognos report that gets run and runs out of TEMP space (ORA-1652). I don't have the opportunity to talk to the person running the report, so I don't exactly know what query they are running.

I can put a trigger in the database to capture a "server error". But can I capture the offending sql? I tried looking into v$sql with:

SELECT UPPER(program) program, username, osuser, terminal,v$sql.SQL_TEXT
FROM v$SQL,V$SESSION
WHERE AUDSID = USERENV('SESSIONID')
and v$SQL.ADDRESS = V$SESSION.SQL_ADDRESS;

But that only captures the above sql. How can I capture the exact sql that is failing? I do know the Oracle user that they are logging in under, so I could enable a 10053 trace for every session that they log on with. But that seems like the "shotgun" approach.

Thanks in advance.

Thomas Mercadante
Oracle Certified Professional

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 18 2005 - 11:27:04 CST