这个方法在开发数据库期间可能没什么用,但是在产品环境中你可能尝试查看和跟踪数据库的错误信息。
首先我们创建一个表来存储错误信息,必须确保每个人都有访问这个表的权限
01 | CREATE TABLE error_log ( |
02 | server_error VARCHAR2(100), |
04 | username VARCHAR2(30), |
然后创建如下触发器,触发条件:"AFTER SERVERERROR ON DATABASE":
02 | TRIGGER error_log_trigger |
03 | AFTER SERVERERROR ON DATABASE |
05 | username_ error_log.username%TYPE; |
06 | osuser_ error_log.osuser%TYPE; |
07 | machine_ error_log.machine%TYPE; |
08 | process_ error_log.process%TYPE; |
09 | program_ error_log.program%TYPE; |
12 | sql_text_ ora_name_list_t; |
15 | FOR i IN 1..NVL(ora_sql_txt(sql_text_), 0) LOOP |
16 | stmt_ := SUBSTR(stmt_ || sql_text_(i) ,1,4000); |
19 | FOR i IN 1..ora_server_error_depth LOOP |
20 | msg_ := SUBSTR(msg_ || ora_server_error_msg(i) ,1,4000); |
23 | SELECT osuser, username, machine, process, program |
24 | INTO osuser_, username_, machine_, process_, program_ |
26 | WHERE audsid = USERENV( 'SESSIONID' ); |
28 | INSERT INTO error_log VALUES (dbms_standard.server_error(1), osuser_, username_, machine_, process_, program_, stmt_, msg_, SYSDATE); |
等一等,可能表里就有错误信息了:)