--自定义数据库表
-- Create table
create table AUDIT_REC
(
host VARCHAR2(200),
"IP ADDRESS" VARCHAR2(200),
os_log_user VARCHAR2(200),
db_user VARCHAR2(200),
object_name VARCHAR2(200),
action VARCHAR2(200),
sql_text VARCHAR2(200),
action_time DATE
)
tablespace USERS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column AUDIT_REC.host is '主机名';
comment on column AUDIT_REC."IP ADDRESS" is 'IP地址';
comment on column AUDIT_REC.os_log_user is '操作系统用户名';
comment on column AUDIT_REC.db_user is '数据库用户名';
comment on column AUDIT_REC.object_name is '数据库对象名';
comment on column AUDIT_REC.action is '操作事件';
comment on column AUDIT_REC.action is 'SQL语句';
comment on column AUDIT_REC.action_time is '操作时间';
--DDL触发器
create or replace trigger audit_ddl
after ddl on database
declare
sql_text ora_name_list_t;
n number;
stmt varchar2(4000) :=null;
begin
n := ora_sql_txt(sql_text);
for i in 1 .. n loop
stmt := stmt || sql_text(i);
end loop;
if Ora_sysevent = 'DROP' or Ora_sysevent = 'TRUNCATE' then
insert into AUDIT_REC
select SYS_CONTEXT('USERENV', 'TERMINAL'),
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT('USERENV', 'OS_USER'),
ora_login_user,
ora_dict_obj_name,
ora_sysevent,
stmt,
sysdate
from dual;
end if;
end;