Создаём таблицу
create table SystemEventLog
(sys_dt timestamp(6) default systimestamp,
module varchar2(128),
action varchar2(128),
line varchar2(64),
msg_type varchar2(8) default 'MSG',
msg_xml clob,
sessionid number,
login_user varchar2(256),
database_name varchar2(256),
username varchar2(256),
sql_fulltext clob,
error_stack varchar2(4000),
call_stack varchar2(4000),
authentication_type varchar2(256),
current_schema varchar2(256),
db_name varchar2(256),
os_user varchar2(256),
host varchar2(256),
ip_address varchar2(256)) tablespace miravild_data;
Предоставляем привилегии на системные представления
grant select on sys.v_$mystat to miravild;
grant select on sys.v_$instance to miravild;
grant select on sys.v_$session to miravild;
ПАКЕТ
Пример вызова
DECLARE
v_module CONSTANT VARCHAR2(256) := substr(sys_context('USERENV', 'MODULE'),
1,
256);
v_action CONSTANT VARCHAR2(256) := substr(sys_context('USERENV', 'ACTION'),
1,
256);
v PLS_INTEGER;
BEGIN
miravild.systemeventlog_pkg.p(p_msg_type => 'MSG',
p_module => v_module,
p_action => v_action,
p_line => to_char($$PLSQL_LINE),
p_param1 => 'v',
p_value1 => to_char(v));
SELECT 1 / 0 INTO v FROM dual;
EXCEPTION
WHEN OTHERS THEN
miravild.systemeventlog_pkg.p(p_module => v_module,
p_action => v_action,
p_line => to_char($$PLSQL_LINE),
p_param1 => 'v',
p_value1 => to_char(v),
p_sql_xml => to_clob(sys.dbms_utility.format_error_stack || '/' ||
chr(10) ||
sys.dbms_utility.format_error_backtrace));
END;