Indeed I have a suggestion - standard auditing may be insufficient. (In 9i it certainly is.) I use a custom method - with trigger, a procedure, a sequence and a table. (Granted, there is room for improvement here. This has evolved over a long time and is now likely quite "suboptimal". It does have the advantage of actually working though.) The trick is in the permissions needed by the owner, but I have to leave for an appointment, so will let you (and the rest of Oracle-L) work that out. create sequence DDL_AUDIT_SEQ; create table DDL_AUDIT ( ID number(38.0) not null, DB_NAME varchar2(30) null, ACTION_TIME date not null, LOGON_TIME date null, SQL_TEXT clob null, CLIENT_IP varchar2(20) null, ORA_SYSEVENT varchar2(20) null, OBJ_OWNER varchar2(30) null, OBJ_TYPE varchar2(30) null, OBJ_NAME varchar2(30) null, INSTANCE_NUM number(2) null, LOGON_USER varchar2(30) null, OSUSER varchar2(30) null, SESSIONID number null, MACHINE varchar2(64) null, TERMINAL varchar2(30) null, PROGRAM varchar2(48) null, MODULE varchar2(48) null, CLIENT_INFO varchar2(64) null, HOST varchar2(54) null, AUTHENTICATION_TYPE varchar2(30) null, SCHEMANAME varchar2(30) null, EXTERNAL_NAME varchar2(256) null ); create or replace procedure GET_SESSION_INFO ( sessionid in number, schemaname out varchar2, osuser out varchar2, process out varchar2, machine out varchar2, terminal out varchar2, program out varchar2, module out varchar2, action out varchar2, logon_time out date, client_identifier out varchar2, external_name out varchar2 ) as BEGIN select SCHEMANAME, OSUSER, PROCESS, MACHINE, TERMINAL, PROGRAM, MODULE, ACTION, LOGON_TIME, CLIENT_IDENTIFIER, EXTERNAL_NAME into schemaname, osuser, process, machine, terminal, program, module, action, logon_time, client_identifier, external_name from V$SESSION S where S.AUDSID = sessionid and rownum = 1; EXCEPTION when OTHERS then raise; END; / create or replace trigger BEFORE_DDL before ddl on database DECLARE id number; sql_text ora_name_list_t; n number; stmt clob; client_ip varchar2(20); session_id number; host varchar2(54); external_name varchar2(256); os_user varchar2(30); sessionid number; l_command number; l_db_name varchar2(30); l_schemaname varchar2(30); l_osuser varchar2(30); l_process varchar2(12); l_machine varchar2(64); l_terminal varchar2(30); l_program varchar2(48); l_module varchar2(48); l_action varchar2(32); l_client_info varchar2(64); l_logon_time date; l_client_identifier varchar2(64); l_external_name varchar2(256); l_authentication_type varchar2(30); BEGIN -- Note: This will generate audit records only if the target is in the SCOTT schema or the BIGAPP schema. IF ora_dict_obj_owner IN ('SCOTT','BIGAPP') then stmt := null; n := ora_sql_txt(sql_text); FOR i IN 1..n LOOP stmt := stmt || sql_text(i); END LOOP; select sys_context('USERENV','HOST'), sys_context('USERENV','OS_USER'), sys_context('USERENV','EXTERNAL_NAME'), sys_context('USERENV','DB_NAME') into host, os_user, external_name, l_db_name from dual; sessionid := userenv('SESSIONID'); get_session_info ( sessionid, l_schemaname, l_osuser, l_process, l_machine, l_terminal, l_program, l_module, l_action, l_logon_time, l_client_identifier, l_external_name); INSERT INTO DDL_AUDIT (ID, DB_NAME, ACTION_TIME, LOGON_TIME, SQL_TEXT, CLIENT_IP, ORA_SYSEVENT, OBJ_OWNER, OBJ_TYPE, OBJ_NAME, INSTANCE_NUM, LOGON_USER, OSUSER, SESSIONID, MACHINE, TERMINAL, PROGRAM, MODULE, CLIENT_INFO, HOST, AUTHENTICATION_TYPE, SCHEMANAME, EXTERNAL_NAME) values ( DDL_AUDIT_SEQ.NEXTVAL, l_db_name, sysdate, l_logon_time, stmt, ora_client_ip_address, ora_sysevent, ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, ora_instance_num, ora_login_user, os_user, sessionid, NULL, /* machine */ userenv('TERMINAL'), l_program, l_module, userenv('CLIENT_INFO'), host, l_authentication_type, l_schemaname, l_external_name ); end if; END; / From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dennis Williams Sent: Monday, December 06, 2010 12:48 PM To: Mayen.Shah@xxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx Subject: Re: Simple auditing question Thanks everyone for you great replies. As Mayen suggested, I checked further, and the requirement is to audit DDL statements, but not DML statements. Specifically we need to know how to audit DDL. I.E. ALTER TABLE, CREATE TABLE, DROP TABLE, CREATE INDEX, DROP INDEX, ALTER INDEX. Basically we need to audit all ALTERs, CREATEs, and DROPs on all objects in the database. Any thoughts on that score? Dennis Williams On Fri, Dec 3, 2010 at 2:06 PM, <Mayen.Shah@xxxxxxxxxx> wrote: Dennis, Rather than what you do not want to know (or what not to audit) you should come up with the requirements of what you want to know or what you want to audit. Best - Mayen From: "Dennis Williams" <oracledba.williams@xxxxxxxxx> To: oracle-l@xxxxxxxxxxxxx Date: 12/03/2010 02:45 PM Subject: Simple auditing question Sent by: oracle-l-bounce@xxxxxxxxxxxxx ________________________________________ List, I haven't used Oracle Auditing much, so this is probably a newbie question. Need to turn on auditing for a high-volume OLTP database. My understanding is that if I turn on AUDIT TABLE, it gets everything. I don't need to know all the inserts, updates on these tables. Is there a simpler audit setting? I probably am missing a concept. Thanks, Dennis Williams -- //www.freelists.org/webpage/oracle-l