有没有办法在 oracle 10g 中记录所有失败的 sql 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/492705/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
is there any way to log all failed sql statements in oracle 10g
提问by user60890
is there any way to log all failed sql statements in oracle 10g to a table or file?
有没有办法将 oracle 10g 中所有失败的 sql 语句记录到表或文件中？
By failed I mean bad formated sql statement or sql statements that do not have permission for a table or object.
失败是指格式错误的 sql 语句或对表或对象没有权限的 sql 语句。
回答by user60890
You may want to use Auditing like:
您可能想要使用审计，如：
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;
审计选择表，插入表，删除表，在不成功时按访问执行程序；
By ACCESS is for each statement (which seems like what you want). By SESSION would record one record per session (high volume environment).
By ACCESS 适用于每个语句（这似乎是您想要的）。按 SESSION 将记录每个会话的一条记录（高容量环境）。
Oracle's built in auditing has less overhead then a trigger. A trigger, which other answers contain, allows you to log the exact information you want. Auditing will also only catch hits on existing objects. If someone selects on a non-existent table (misspelled or whatnot) auditing will not catch it. The triggers above will.
Oracle 内置审计的开销比触发器少。其他答案包含的触发器允许您记录所需的确切信息。审计也只会捕获对现有对象的命中。如果有人在不存在的表（拼写错误或诸如此类）上进行选择，则审计将无法捕获它。上面的触发器会。
A lot more info in the security guide: http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1011984
安全指南中的更多信息：http: //download.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1011984
回答by Gary Myers
Rather than hit the system views, as in Demge's answer, there is an ora_sql_txt function that gives the relevant statement.
不是像在 Demge 的回答中那样点击系统视图，而是有一个 ora_sql_txt 函数给出了相关的语句。
create or replace TRIGGER log_err after servererror on schema
DECLARE
v_stack VARCHAR2(2000) := substr(dbms_utility.format_error_stack,1,2000);
v_back VARCHAR2(2000);-- := substr(dbms_utility.format_error_backtrace,1,2000);
v_num NUMBER;
v_sql_text ora_name_list_t;
procedure track(p_text in varchar2) is
begin
insert into .... values (p_text);
end;
begin
v_stack := translate(v_stack,'''','"');
track(v_stack);
v_back := translate(v_back,'''','"');
if v_back is not null then track(v_back); end if;
v_num := ora_sql_txt(v_sql_text);
BEGIN
FOR i IN 1..v_num LOOP
track(to_char(i,'0000')||':'||v_sql_text(i));
END LOOP;
EXCEPTION
WHEN VALUE_ERROR THEN NULL;
END;
end;
In my own environment, I actually have 'TRACK' as a separate procedure that uses an autonomous transaction, rather than a block as above.
在我自己的环境中，我实际上将“TRACK”作为使用自治事务的单独过程，而不是上述块。
create or replace procedure track (p_text IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_user is
select sys_context('USERENV','CLIENT_INFO') client_info,
sys_context('USERENV','CURRENT_SCHEMA') curr_schema,
sys_context('USERENV','CURRENT_USER') curr_user,
sys_context('USERENV','DB_NAME') db_name,
sys_context('USERENV','HOST') host,
sys_context('USERENV','IP_ADDRESS') ip,
sys_context('USERENV','OS_USER') osuser,
sys_context('USERENV','SESSIONID') sessid,
sys_context('USERENV','SESSION_USER') sess_user,
sys_context('USERENV','TERMINAL') terminal
from dual;
user_rec c_user%rowtype;
v_mod VARCHAR2(48);
v_act VARCHAR2(32);
v_cli_info varchar2(64);
begin
open c_user;
fetch c_user into user_rec;
close c_user;
DBMS_APPLICATION_INFO.READ_MODULE (v_mod, v_act);
--DBMS_APPLICATION_INFO.READ_CLIENT_INFO(v_cli_info);
insert into track_detail
(id, track_time, detail, client_info, curr_schema, curr_user, db_name,
host, ip, osuser, sessid, sess_user, terminal, module, action)
values (track_seq.nextval, systimestamp, p_text,
user_rec.client_info, user_rec.curr_schema, user_rec.curr_user,
user_rec.db_name, user_rec.host, user_rec.ip,
user_rec.osuser, user_rec.sessid, user_rec.sess_user,
user_rec.terminal, v_mod, v_act);
commit;
end;
回答by Daniel Emge
You can do this with a system trigger.
您可以使用系统触发器执行此操作。
I directly copied this code from http://www.psoug.org/reference/system_trigger.html.
我直接从http://www.psoug.org/reference/system_trigger.html复制了这段代码。
CREATE TABLE servererror_log (
error_datetime TIMESTAMP,
error_user VARCHAR2(30),
db_name VARCHAR2(9),
error_stack VARCHAR2(2000),
captured_sql VARCHAR2(1000));
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
captured_sql VARCHAR2(1000);
BEGIN
SELECT q.sql_text
INTO captured_sql
FROM gv$sql q, gv$sql_cursor c, gv$session s
WHERE s.audsid = audsid
AND s.prev_sql_addr = q.address
AND q.address = c.parent_handle;
INSERT INTO servererror_log
(error_datetime, error_user, db_name,
error_stack, captured_sql)
VALUES
(systimestamp, sys.login_user, sys.database_name,
dbms_utility.format_error_stack, captured_sql);
END log_server_errors;
/
