先建好记录表ddl$trace，capt$sql

捕获ddl操作触发器：

CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER DDL ON DATABASE
DECLARE
sql_text ora_name_list_t;
state_sql ddl$trace.ddl_sql%TYPE;
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql||sql_text(i);
END LOOP;
INSERT INTO ddl$trace(login_user,ddl_time,ip_address,audsid,
schema_user,schema_object,ddl_sql)
VALUES(ora_login_user,SYSDATE,userenv('SESSIONID'),
sys_context('USERENV','IP_ADDRESS'),
ora_dict_obj_owner,ora_dict_obj_name,state_sql);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Capture DDL Excption:'||SQLERRM);
END tr_trace_ddl;

捕获某表dml操作触发器：

CREATE OR REPLACE TRIGGER tr_capt_sql
BEFORE DELETE OR INSERT OR UPDATE
ON 表名
DECLARE
sql_text ora_name_list_t;
state_sql capt$sql.sql_text%TYPE;
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql || sql_text(i);
END LOOP;
INSERT INTO capt$sql(login_user,capt_time,ip_address,audsid,owner,table_name,sql_text)
VALUES(ora_login_user,sysdate,sys_context('USERENV','IP_ADDRESS'),
userenv('SESSIONID'),'MANAGER','TEST',state_sql);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Capture DML Exception:'||SQLERRM);
END tr_capt_sql;

up

ora_client_ip_address
VARCHAR2
Provides the IP address of the client machine when using TCP/IP

ora_database_name
VARCHAR2(50)
Provides the database name

ora_des_encrypted_password
VARCHAR2
Provides the DES encrypted password of the user being created or altered

ora_dict_obj_name
VARCHAR(30)
Provides the object name of the object being manipulated

ora_dict_obj_name_list(name_list OUT ora_name_list_t)
BINARY_INTEGER
Provides a list of object names being manipulated

ora_dict_obj_owner
VARCHAR(30)
Provides the owner of the object being manipulated

ora_dict_obj_owner_list(owner_list OUT ora_name_list_t)
BINARY_INTEGER
Provides the owners of the objects being manipulated

ora_dict_obj_type
VARCHAR(20)
Provides the type of object being manipulated

ora_grantee(user_list OUT ora_name_list_t)
BINARY_INTEGER
Provides the number of grantees

ora_instance_num
NUMBER
Provides the instance number.

ora_is_alter_column(column_name IN VARCHAR2)
BOOLEAN
Provides a return value of TRUE if the specified column is altered

ora_is_creating_nested_table
BOOLEAN
Provides a return value of TRUE if the current event is creating a nested table

ora_is_drop_column(column_name IN VARCHAR2)
BOOLEAN
Provides a return value of TRUE if the specified column is dropped

ora_is_servererror
BOOLEAN
Provides a return value of TRUE is the error specified is on the error stack

ora_login_user
VARCHAR2(30)
Provides the login schema

ora_partition_pos
BINARY_INTEGER
Provides the position in a CREATE TABLE command where the partition clause can be inserted when using the INSTEAD OF trigger

ora_privilege_list(privilege_list OUT ora_name_list_t)
BINARY_INTEGER
Provides the list of privileges being granted or revoked

ora_revokee (user_list OUT ora_name_list_t)
BINARY_INTEGER
Provides a list of the revokees of the revoke command

ora_server_error
NUMBER
Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack)

ora_server_error_depth
BINARY_INTEGER
Provides the total number of errors on the error stack

ora_server_error_msg (position in binary_integer)
VARCHAR2
Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack)

ora_server_error_num_params (position in binary_integer)
BINARY_INTEGER
Provides the number of strings that have been substituted into the error message on the error stack for the position specified in the stack (1 meaning the top of the stack)

ora_server_error_param (position in binary_integer, param in binary_integer)
VARCHAR2
Provides the matching substitution value in the error message for the parameter number specified in conjunction with the position specified on the stack ( 1 meaning the top of the stack)

ora_sql_txt (sql_text out ora_name_list_t)
BINARY_INTEGER
Provides the SQL statement of the statement that caused the trigger to execute (if the statement is lengthy, it will separate it into multiple PL/SQL table elements); the value returned specifies the number of elements

ora_sysevent
VARCHAR2(20)
Provides the system or client event that caused the trigger to execute

ora_with_grant_option
BOOLEAN
Provides a return value of TRUE if the privileges are granted with the grant option

space_error_info(error_number OUT NUMBER,error_type OUT VARCHAR2,object_owner OUT VARCHAR2,table_space_name OUT VARCHAR2,object_name OUT VARCHAR2,sub_object_name OUT
VARCHAR2)
BOOLEAN
Provides a return value of true if the error is related to an out-of-space error and provides the object information of the object with the error