在实际应用的过程中我遇到这样一种情况 需要监控一个频繁操作的表(EMLPOYEE) 我通过新建立一张新表(RECORD)记录在EMLPOYEE上所做的操作
要求有 当前时间、应用程序、机器名、SQL语句、对应操作
(就是我要晓得谁对这个表做了操作,什么操作,什么时候做的,是用什么应用程序做的是JDBC还是是SQLPLUS等等)
还要晓得对应的ID(也就是能晓得具体的对哪一行做了操作 比如插入是新的ID是什么,更新是对那行的ID是什么,删除是是哪一行)
我估计所有的不一定都能实现?多多益善
例如:
下面是测试表
create table EMPLOYEE
(
ID VARCHAR2(4) not null,
FIRST_NAME VARCHAR2(10),
LAST_NAME VARCHAR2(10),
START_DATE DATE,
END_DATE DATE,
SALARY NUMBER(8,2),
CITY VARCHAR2(10),
DESCRIPTION VARCHAR2(15)
)
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('09', 'Jason1', 'Martin2', to_date('25-07-1996', 'dd-mm-yyyy'), to_date('25-07-2006', 'dd-mm-yyyy'), 1234.56, 'Toronto', 'Programmer');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('10', 'Alison1', 'Mathews2', to_date('21-03-1976', 'dd-mm-yyyy'), to_date('21-02-1986', 'dd-mm-yyyy'), 6661.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('11', 'James1', 'Smith2', to_date('12-12-1978', 'dd-mm-yyyy'), to_date('15-03-1990', 'dd-mm-yyyy'), 6544.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('12', 'Celia1', 'Rice2', to_date('24-10-1982', 'dd-mm-yyyy'), to_date('21-04-1999', 'dd-mm-yyyy'), 2344.78, 'Vancouver', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('13', 'Robert1', 'Black2', to_date('15-01-1984', 'dd-mm-yyyy'), to_date('08-08-1998', 'dd-mm-yyyy'), 2334.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('14', 'Linda1', 'Green2', to_date('30-07-1987', 'dd-mm-yyyy'), to_date('04-01-1996', 'dd-mm-yyyy'), 4322.78, 'New York', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('15', 'David1', 'Larry2', to_date('31-12-1990', 'dd-mm-yyyy'), to_date('12-02-1998', 'dd-mm-yyyy'), 7897.78, 'New York', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('16', 'James1', 'Cat2', to_date('17-09-1996', 'dd-mm-yyyy'), to_date('15-04-2002', 'dd-mm-yyyy'), 1232.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('01', 'Jason', 'Martin', to_date('25-07-1996', 'dd-mm-yyyy'), to_date('25-07-2006', 'dd-mm-yyyy'), 1234.56, 'Toronto', 'Programmer');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('02', 'Alison', 'Mathews', to_date('21-03-1976', 'dd-mm-yyyy'), to_date('21-02-1986', 'dd-mm-yyyy'), 6661.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('03', 'James', 'Smith', to_date('12-12-1978', 'dd-mm-yyyy'), to_date('15-03-1990', 'dd-mm-yyyy'), 6544.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('04', 'Celia', 'Rice', to_date('24-10-1982', 'dd-mm-yyyy'), to_date('21-04-1999', 'dd-mm-yyyy'), 2344.78, 'Vancouver', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('05', 'Robert', 'Black', to_date('15-01-1984', 'dd-mm-yyyy'), to_date('08-08-1998', 'dd-mm-yyyy'), 2334.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('06', 'Linda', 'Green', to_date('30-07-1987', 'dd-mm-yyyy'), to_date('04-01-1996', 'dd-mm-yyyy'), 4322.78, 'New York', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('07', 'David', 'Larry', to_date('31-12-1990', 'dd-mm-yyyy'), to_date('12-02-1998', 'dd-mm-yyyy'), 7897.78, 'New York', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('08', 'James', 'Cat', to_date('17-09-1996', 'dd-mm-yyyy'), to_date('15-04-2002', 'dd-mm-yyyy'), 1232.78, 'Vancouver', 'Tester');
commit;
新表create table record(ID NUMBER,multime date,mul varchar2(40),machine varchar2(40),program varchar2(40),sqltext varchar2(1000));
用触发器WHEN INSERT OR UPDATE OR DELETE
帮我解决了还可以另外加分
要求有 当前时间、应用程序、机器名、SQL语句、对应操作
(就是我要晓得谁对这个表做了操作,什么操作,什么时候做的,是用什么应用程序做的是JDBC还是是SQLPLUS等等)
还要晓得对应的ID(也就是能晓得具体的对哪一行做了操作 比如插入是新的ID是什么,更新是对那行的ID是什么,删除是是哪一行)
我估计所有的不一定都能实现?多多益善
例如:
下面是测试表
create table EMPLOYEE
(
ID VARCHAR2(4) not null,
FIRST_NAME VARCHAR2(10),
LAST_NAME VARCHAR2(10),
START_DATE DATE,
END_DATE DATE,
SALARY NUMBER(8,2),
CITY VARCHAR2(10),
DESCRIPTION VARCHAR2(15)
)
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('09', 'Jason1', 'Martin2', to_date('25-07-1996', 'dd-mm-yyyy'), to_date('25-07-2006', 'dd-mm-yyyy'), 1234.56, 'Toronto', 'Programmer');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('10', 'Alison1', 'Mathews2', to_date('21-03-1976', 'dd-mm-yyyy'), to_date('21-02-1986', 'dd-mm-yyyy'), 6661.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('11', 'James1', 'Smith2', to_date('12-12-1978', 'dd-mm-yyyy'), to_date('15-03-1990', 'dd-mm-yyyy'), 6544.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('12', 'Celia1', 'Rice2', to_date('24-10-1982', 'dd-mm-yyyy'), to_date('21-04-1999', 'dd-mm-yyyy'), 2344.78, 'Vancouver', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('13', 'Robert1', 'Black2', to_date('15-01-1984', 'dd-mm-yyyy'), to_date('08-08-1998', 'dd-mm-yyyy'), 2334.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('14', 'Linda1', 'Green2', to_date('30-07-1987', 'dd-mm-yyyy'), to_date('04-01-1996', 'dd-mm-yyyy'), 4322.78, 'New York', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('15', 'David1', 'Larry2', to_date('31-12-1990', 'dd-mm-yyyy'), to_date('12-02-1998', 'dd-mm-yyyy'), 7897.78, 'New York', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('16', 'James1', 'Cat2', to_date('17-09-1996', 'dd-mm-yyyy'), to_date('15-04-2002', 'dd-mm-yyyy'), 1232.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('01', 'Jason', 'Martin', to_date('25-07-1996', 'dd-mm-yyyy'), to_date('25-07-2006', 'dd-mm-yyyy'), 1234.56, 'Toronto', 'Programmer');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('02', 'Alison', 'Mathews', to_date('21-03-1976', 'dd-mm-yyyy'), to_date('21-02-1986', 'dd-mm-yyyy'), 6661.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('03', 'James', 'Smith', to_date('12-12-1978', 'dd-mm-yyyy'), to_date('15-03-1990', 'dd-mm-yyyy'), 6544.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('04', 'Celia', 'Rice', to_date('24-10-1982', 'dd-mm-yyyy'), to_date('21-04-1999', 'dd-mm-yyyy'), 2344.78, 'Vancouver', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('05', 'Robert', 'Black', to_date('15-01-1984', 'dd-mm-yyyy'), to_date('08-08-1998', 'dd-mm-yyyy'), 2334.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('06', 'Linda', 'Green', to_date('30-07-1987', 'dd-mm-yyyy'), to_date('04-01-1996', 'dd-mm-yyyy'), 4322.78, 'New York', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('07', 'David', 'Larry', to_date('31-12-1990', 'dd-mm-yyyy'), to_date('12-02-1998', 'dd-mm-yyyy'), 7897.78, 'New York', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('08', 'James', 'Cat', to_date('17-09-1996', 'dd-mm-yyyy'), to_date('15-04-2002', 'dd-mm-yyyy'), 1232.78, 'Vancouver', 'Tester');
commit;
新表create table record(ID NUMBER,multime date,mul varchar2(40),machine varchar2(40),program varchar2(40),sqltext varchar2(1000));
用触发器WHEN INSERT OR UPDATE OR DELETE
帮我解决了还可以另外加分
(
id
,multime
,mul
,machine
,program
)
VALUES
(
:old.id
,sysdate
,user
,sys_context('userenv', 'host')
,(SELECT program FROM v$session where session_id = sys_context('userenv', 'sessionid'))
);
这个会写触发器的都能实现关键是 应用程序名、主机名、和对应的SQLsys_context('userenv', 'host')
这是本机的工作组和机器名
我要的是谁操作用什么操作的
SQL> select * from employee;ID FIRST_NAME LAST_NAME START_DATE END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
09 Jason1 Martin2 1996-7-25 2006-7-25 1234.56 Toronto Programmer
10 Alison1 Mathews2 1976-3-21 1986-2-21 6661.78 Vancouver Tester
11 James1 Smith2 1978-12-12 1990-3-15 6544.78 Vancouver Tester
12 Celia1 Rice2 1982-10-24 1999-4-21 2344.78 Vancouver Manager
13 Robert1 Black2 1984-1-15 1998-8-8 2334.78 Vancouver Tester
14 Linda1 Green2 1987-7-30 1996-1-4 4322.78 New York Tester
15 David1 Larry2 1990-12-31 1998-2-12 7897.78 New York Manager
16 James1 Cat2 1996-9-17 2002-4-15 1232.78 Vancouver Tester
01 Jason Martin 1996-7-25 2006-7-25 5000.00 Toronto Programmer
02 Alison Mathews 1976-3-21 1986-2-21 6661.78 Vancouver Tester
03 James Smith 1978-12-12 1990-3-15 6544.78 Vancouver Tester
04 Celia Rice 1982-10-24 1999-4-21 2344.78 Vancouver Manager
05 Robert Black 1984-1-15 1998-8-8 2334.78 Vancouver Tester
06 Linda Green 1987-7-30 1996-1-4 4322.78 New York Tester
07 David Larry 1990-12-31 1998-2-12 7897.78 New York Manager
08 James Cat 1996-9-17 2002-4-15 1232.78 Vancouver Tester16 rows selectedSQL> create table record(id number primary key,multime date,mul varchar2(40),ip varchar2(20));Table createdSQL> create sequence t_seq1;Sequence createdSQL>
SQL> --创建触发器
SQL> Create or replace trigger tri_ipaddress
2 Before insert or update or delete On employee
3 for each row
4 declare
5 --定义变量
6 cursor c1 is
7 select sys_context('userenv', 'ip_address') from dual;
8 action varchar2(10);
9 l_ip varchar2(20);
10 BEGIN
11 --记录操作类型
12 if inserting then
13 action := 'I';
14 end if;
15 if updating then
16 action := 'U';
17 end if;
18 if deleting then
19 action := 'D';
20 end if;
21 open c1;
22 fetch c1
23 into l_ip;
24 --更新记录
25 insert into record
26 (id, multime, mul, ip)
27 values
28 (t_seq1.nextval, sysdate, action, l_ip);
29 close c1;
30 end tri_ipaddress;
31 /Trigger createdSQL> update employee set salary=10000 where id='01';1 row updatedSQL> commit;Commit completeSQL> select * from record; ID MULTIME MUL IP
---------- ----------- ---------------------------------------- --------------------
1 2008-10-23 U 192.168.0.33SQL>
create table EMPLOYEE (
ID VARCHAR2(4) not null,
FIRST_NAME VARCHAR2(10),
LAST_NAME VARCHAR2(10),
START_DATE DATE,
END_DATE DATE,
SALARY NUMBER(8,2),
CITY VARCHAR2(10),
DESCRIPTION VARCHAR2(15)
) ;
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('09', 'Jason1', 'Martin2', to_date('25-07-1996', 'dd-mm-yyyy'), to_date('25-07-2006', 'dd-mm-yyyy'), 1234.56, 'Toronto', 'Programmer');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('10', 'Alison1', 'Mathews2', to_date('21-03-1976', 'dd-mm-yyyy'), to_date('21-02-1986', 'dd-mm-yyyy'), 6661.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('11', 'James1', 'Smith2', to_date('12-12-1978', 'dd-mm-yyyy'), to_date('15-03-1990', 'dd-mm-yyyy'), 6544.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('12', 'Celia1', 'Rice2', to_date('24-10-1982', 'dd-mm-yyyy'), to_date('21-04-1999', 'dd-mm-yyyy'), 2344.78, 'Vancouver', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('13', 'Robert1', 'Black2', to_date('15-01-1984', 'dd-mm-yyyy'), to_date('08-08-1998', 'dd-mm-yyyy'), 2334.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('14', 'Linda1', 'Green2', to_date('30-07-1987', 'dd-mm-yyyy'), to_date('04-01-1996', 'dd-mm-yyyy'), 4322.78, 'New York', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('15', 'David1', 'Larry2', to_date('31-12-1990', 'dd-mm-yyyy'), to_date('12-02-1998', 'dd-mm-yyyy'), 7897.78, 'New York', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('16', 'James1', 'Cat2', to_date('17-09-1996', 'dd-mm-yyyy'), to_date('15-04-2002', 'dd-mm-yyyy'), 1232.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('01', 'Jason', 'Martin', to_date('25-07-1996', 'dd-mm-yyyy'), to_date('25-07-2006', 'dd-mm-yyyy'), 1234.56, 'Toronto', 'Programmer');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('02', 'Alison', 'Mathews', to_date('21-03-1976', 'dd-mm-yyyy'), to_date('21-02-1986', 'dd-mm-yyyy'), 6661.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('03', 'James', 'Smith', to_date('12-12-1978', 'dd-mm-yyyy'), to_date('15-03-1990', 'dd-mm-yyyy'), 6544.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('04', 'Celia', 'Rice', to_date('24-10-1982', 'dd-mm-yyyy'), to_date('21-04-1999', 'dd-mm-yyyy'), 2344.78, 'Vancouver', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('05', 'Robert', 'Black', to_date('15-01-1984', 'dd-mm-yyyy'), to_date('08-08-1998', 'dd-mm-yyyy'), 2334.78, 'Vancouver', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('06', 'Linda', 'Green', to_date('30-07-1987', 'dd-mm-yyyy'), to_date('04-01-1996', 'dd-mm-yyyy'), 4322.78, 'New York', 'Tester');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('07', 'David', 'Larry', to_date('31-12-1990', 'dd-mm-yyyy'), to_date('12-02-1998', 'dd-mm-yyyy'), 7897.78, 'New York', 'Manager');
insert into EMPLOYEE (ID, FIRST_NAME, LAST_NAME, START_DATE, END_DATE, SALARY, CITY, DESCRIPTION)
values ('08', 'James', 'Cat', to_date('17-09-1996', 'dd-mm-yyyy'), to_date('15-04-2002', 'dd-mm-yyyy'), 1232.78, 'Vancouver', 'Tester');
commit; DROP TABLE RECORD;
create table record(id NUMBER ,empid VARCHAR2(10),multime date,mul varchar2(40),
terminal varchar2(40),ip VARCHAR2(20),program varchar2(40),sqltext varchar2(1000));
CREATE SEQUENCE seq_1;
CREATE OR REPLACE TRIGGER TRI_IPADDRESS
AFTER INSERT OR UPDATE OR DELETE ON EMPLOYEE
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
V_EMPID VARCHAR2(10);
V_MUL VARCHAR2(40);
BEGIN
IF INSERTING THEN
V_MUL := 'insert';
V_EMPID := :NEW.ID;
ELSIF DELETING THEN
V_MUL := 'delete';
V_EMPID := :OLD.ID;
ELSIF UPDATING THEN
V_MUL := 'update';
V_EMPID := :OLD.ID;
END IF;
INSERT INTO RECORD
SELECT SEQ_1.NEXTVAL,
V_EMPID,
SYSDATE,
V_MUL,
TERMINAL,
SYS_CONTEXT('userenv', 'ip_address') IP,
PROGRAM,
'sqltext'
FROM V$SESSION S
WHERE SID = SYS_CONTEXT('userenv', 'sid');
COMMIT;
END;
/输出:
3 07 2008-10-23 14:31:24 delete XQQX 10.1.70.55 sqlplus.exe sqltext
4 08 2008-10-23 13:55:49 insert XQQX 10.1.70.55 plsqldev.exe sqltext
我只做到可以放 ID,SYSDATE,MUL 没能加上PROGRAM,MACHINE是循环体结构理解的不清楚
牛啊
SELECT sql_text
FROM v$sql x
WHERE ( x.HASH_VALUE) IN
(
SELECT a.PREV_HASH_VALUE
FROM v$session a
WHERE audsid = sys_context('userenv', 'sessionid')
);
PRAGMA AUTONOMOUS_TRANSACTION; begin INSERT INTO trig_sql select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, 'NONE' from v$sql q, v$session s where s.audsid=(select userenv('SESSIONID') from dual) and s.prev_sql_addr=q.address AND s.PREV_HASH_VALUE = q.hash_value; COMMIT; end; 方法2:
create or replace trigger ttt_trig after insert or update on pga_ttt DECLARE PRAGMA AUTONOMOUS_TRANSACTION; begin for cr in (select s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, c.status stat from v$sql q, v$sql_cursor c, v$session s where s.audsid=(select userenv('SESSIONID') from dual) and s.prev_sql_addr=q.address AND c.STATUS = 'CURFETCH') loop INSERT INTO trig_sql VALUES(SYSDATE, cr.sid, cr.SERIAL#, cr.USERNAME, cr.OSUSER, cr.MACHINE, cr.TERMINAL, cr.PROGRAM, cr.line, cr.stat); end loop; COMMIT; end; 第一种方法是通过前一SQL的地址(pre_sql_addr)和HASH(prev_hash_value)值来定位出发trigger的语句的,不能用sql_address和hash_value来定位,否则获取到是触发器里面向日志表插入记录数据的语句本身了。 第二个方法是通过通过地址加游标的方法,按照视图各个字段的解释,应该是可以通过v$sql_cursor.parent_handle来定位的。但是通过测试发现,只有当前一条语句和查找前一条语句的语句在一个PLSQL块中的时候才有效, SQL> set serveroutput on SQL> declare 2 v_date date; 3 v_sql varchar2(2000); 4 begin 5 select sysdate into v_date from dual; 6 7 select q.sql_text into v_sql 8 from v$sql q, v$sql_cursor c, v$session s 9 where s.audsid=(select userenv('SESSIONID') from dual) 10 and s.prev_sql_addr=q.address and q.address=c.parent_handle; 11 12 dbms_output.put_line(v_sql); 13 end; 14 / SELECT SYSDATE FROM DUAL
PL/SQL procedure successfully completed. 因为触发器本身是一个PLSQL块,所以总是无法获得正确语句,最后只有通过cursor的状态来获取。下面简单了解一下CURSOR各个状态的含义: CURNULL:游标已经存在,但没有任何SQL语句在使用它(即cache在每个session内存中的游标) CURSYNTAX:解析SQL语句过程的一个游标状态,说明调用游标的SQL语句语法正确,但是没有解析完成。 CURPARSE:调用游标的语句解析完毕 CURBOUND:游标使用了帮定变量,并定义好了帮定变量 CURFETCH:游标执行完毕,并fetch了数据
CURROW:游标正指向某一行 ERROR:游标错误,一般是有BUG了。 当一条INSERT或者UPDATE语句执行以后才会触发触发器,所以这时候的游标状态是CURFETCH,我们这就通过状态为CURFETCH来定位。
理论是很好的
你没执行这个:
grant select on SYS.V_$SQL to demo;
grant select on SYS.V_$SQL_BIND_DATA to demo;
grant select on SYS.V_$SQL_CURSOR to demo;
grant select on SYS.V_$SESSION to demo;
grant create trigger to demo; 我猜原作者也是没经过测试,在触发器里我这里没法得到最开始的那个语句
但是还是不能 适时补捉 INSERT OR UPDATE 语句啊
我测试了用BEFORE和AFTER 都不行DELETE就不用了