DBA: Create a DML trigger in the production environment

Source: CCID

In a production environment, the data of a certain table or table may be modified for some reason, but it is difficult to find out which user and process to modify. This is a headache for DBAs (DBAs do not know the entire code logic very well ). To locate the "problem" statement, you can choose from the following methods: log miner, detail granularity audit, and trigger. Log miner requires archiving logs (not all systems can do this) and a considerable disk space. The advantage is that logs can be done offline; the detail granularity upgrade can record DML operations in the table below based on the conditions (9i and previously only select statements can be recorded). Complicated FGA requires users with higher permissions. triggers are flexible, records required information based on complex conditions. The following describes how to implement a trigger.

To create such a trigger, you need to use several system views: V $ session, V $ SQL, V $ cursor, (10g, 9.2.0.1, 9.2.0.5, 9.2.0. previous bugs)

SQL> connect "/As sysdba"

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;

Create Table trig_ SQL (LT date, SID number, serial # number,

Username varchar2 (30), osuser varchar2 (64 ),

Machine varchar2 (32), terminal varchar2 (16 ),

Program varchar2 (64), sqltext varchar2 (2000 ),

Status varchar2 (30 ));

Method 1:

Create or replace trigger ttt_trig

After insert or update on pga_ttt

Declare
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,

& Apos; none & apos;

From v $ SQL Q, V $ session s

Where S. audsid = (select userenv (& apos; sessionid & apos;) from dual)

And S. prev_ SQL _addr = Q. Address

And S. prev_hash_value = Q. hash_value;

Commit;

End;

Method 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 (& apos; sessionid & apos;) from dual)

And S. prev_ SQL _addr = Q. Address

And C. Status = & apos; curfetch & apos ;)

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;

The first method is to locate the trigger Statement by using the address (pre_ SQL _addr) and hash (prev_hash_value) values of the previous SQL statement. SQL _address and hash_value cannot be used to locate the trigger, otherwise, the statement itself that inserts record data into the log table in the trigger is obtained.

The second method is to add a cursor through the address. According to the description of each field in the view, it should be located through V $ SQL _cursor.parent_handle. However, tests show that only the current statement and the statement used to find the previous statement are valid in one PLSQL block,

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 (& apos; sessionid & apos;) 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.

Because the trigger itself is a PLSQL block, it is always unable to obtain the correct statement and can only be obtained through the cursor status. The following describes the meaning of each cursor status:

Curnull: The cursor already exists, but no SQL statement is using it (that is, the cursor cached in each session memory)

Cursyntax: A cursor state in the SQL statement parsing process. It indicates that the syntax of the SQL statement that calls the cursor is correct, but the parsing is not completed.

Curparse: The cursor statement is parsed.

Curbound: The cursor uses helper variables and defines helper variables.

Curfetch: the cursor is executed and the data is fetch.
Currow: The cursor points to a row.

Error: the cursor is incorrect. Generally, there is a bug.

The trigger is triggered only after an insert or update statement is executed. Therefore, the cursor state is curfetch, which is located by the curfetch state.

Source: Saidi net Author: Andy http://tech.ccidnet.com/art/1105/20080710/1502663_1.html