建议创建触发器,具体操作如下: craete table logs ( log_id number(10) primary key, log_table varchar2(10) null, log_dml varchar2(10), log_key_id varchar2(10), log_date date, log_user varchar2(15) ); create sequence logs_id_squ increment by 1 start with 1 maxvalue 9999999 nocycle nocache; //创建一个logs表的主序列logs_id_squ create or replace trigger dml_log before delete or insert or update on emp for each row begin if inserting then insert into logs values(logs_id_squ.nextval,'emp','insert',:old.empno,sysdate,user); elseif deleteing then insert into logs values(logs_id_squ.nextval,'emp','delete',:old.empno,sysdate,user); else insert into logs values(logs_id_squ.nextval,'emp','update',:old.empno,sysdate,user); end if; end;
当对emp表进行dml操作时候,就会引起触发器发生 select * from logs; 就可以看到你对emp表进行的操作了
并且要配有履历表。
select * from v$sql;
craete table logs
( log_id number(10) primary key,
log_table varchar2(10) null,
log_dml varchar2(10),
log_key_id varchar2(10),
log_date date,
log_user varchar2(15)
);
create sequence logs_id_squ increment by 1
start with 1 maxvalue 9999999 nocycle nocache; //创建一个logs表的主序列logs_id_squ
create or replace trigger dml_log
before
delete or insert or update
on emp
for each row
begin
if inserting then
insert into logs
values(logs_id_squ.nextval,'emp','insert',:old.empno,sysdate,user);
elseif deleteing then
insert into logs
values(logs_id_squ.nextval,'emp','delete',:old.empno,sysdate,user);
else
insert into logs
values(logs_id_squ.nextval,'emp','update',:old.empno,sysdate,user);
end if;
end;
select * from logs;
就可以看到你对emp表进行的操作了
Oracle LogMiner 分析工具ORACLE 的审计功能
感谢cheng_fengming!~ 在您提供SQL基础了做了修正
CREATE TABLE logs
(
log_id NUMBER (10) PRIMARY KEY,
log_table VARCHAR2 (10) NULL,
log_dml VARCHAR2 (10),
log_key_id VARCHAR2 (10),
log_date DATE,
log_user VARCHAR2 (15)
);//创建一个logs表的主序列logs_id_squ
CREATE SEQUENCE logs_id_squ
INCREMENT BY 1
START WITH 1
MAXVALUE 9999999
NOCYCLE
NOCACHE;CREATE OR REPLACE TRIGGER dml_log
BEFORE DELETE OR INSERT OR UPDATE
ON emp
FOR EACH ROW
BEGIN
IF INSERTING
THEN
INSERT INTO logs
VALUES (logs_id_squ.NEXTVAL,
'emp',
'insert',
:old.empno,
SYSDATE,
USER);
ELSIF DELETING
THEN
INSERT INTO logs
VALUES (logs_id_squ.NEXTVAL,
'emp',
'delete',
:old.empno,
SYSDATE,
USER);
ELSE
INSERT INTO logs
VALUES (logs_id_squ.NEXTVAL,
'emp',
'update',
:old.empno,
SYSDATE,
USER);
END IF;
END;