Yes, but how does one do that? I was really hoping someone would post an answer since there were other people wanting to be able to the same or something very similar. Failing to get a response. I wrote my own; actually, I took some code provided by Steve Adams for a different purpose and put "instr" and "sessionid" restrictions on it. Be warned the code provided below has not been thoroughly tested. select 'x' from sys.x$_kglpn p, sys.x$_kglcursor c, v$_session s where p.kglpnhdl = c.kglhdadr and p.kglpnses = s.saddr and instr(lower(replace(c.kglnaobj,' ', null)),'dropcolumn') > 0 and instr(lower(c.kglnaobj),'select') = 0 and s.audsid = sys_context('USERENV', 'SESSIONID'); All this does is to make sure "drop column" was part of the statement and "select" is not. The check for the "alter table" is done in the event trigger. The full text of the trigger is ------------------------------------------------------------------------------------------------------------------------- CREATE or replace TRIGGER record_destructive_ddl_trigger before drop or alter or truncate ON DATABASE Declare pcolumn_name dba_tab_columns.column_name%type; cursor get_column_name is select column_name from dba_tab_columns where owner = ora_dict_obj_owner and table_name = ora_dict_obj_name; sql_dummy varchar2(1); cursor is_drop_column is select 'x' from sys.x$_kglpn p, sys.x$_kglcursor c, v$session s where p.kglpnhdl = c.kglhdadr and p.kglpnses = s.saddr and instr(lower(replace(c.kglnaobj,' ', null)),'select') = 0 and instr(lower(replace(c.kglnaobj,' ', null)),'dropcolumn') > 0 and s.audsid = sys_context('USERENV', 'SESSIONID'); Begin if (((ora_sysevent = 'DROP') and (ora_dict_obj_type = 'TABLE')) or ora_sysevent = 'TRUNCATE') and ora_dict_obj_owner != 'SYS' THEN object_security.record_destructive_ddl(null); elsif ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE' and ora_dict_obj_owner != 'SYS' THEN open is_drop_column; fetch is_drop_column into sql_dummy; close is_drop_column; if sql_dummy = 'x' then open get_column_name; loop fetch get_column_name into pcolumn_name; exit when get_column_name%notfound; if ora_is_drop_column(pcolumn_name) then object_security.record_destructive_ddl(pcolumn_name); end if; end loop; end if; close get_column_name; end if; end; / ---------------------------------------------------------------------------------------------------------- object_security is a package I wrote which records the information about the destructive DDL into a table. It uses the autonomous transaction pragma and many of the event attribute functions described in Chapter 13 of the "Application Developers Guide - Fundamentals" manual. Create or replace package object_security is procedure RECORD_destructive_ddl(cname varchar2); end object_security; / create or replace package body object_security is procedure RECORD_destructive_ddl(cname varchar2) is PRAGMA AUTONOMOUS_TRANSACTION; Begin INSERT INTO oracle.DESTRUCTIVE_DDL_JOURNAL VALUES (ora_login_user, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, cname,ora_sysevent,ora_client_ip_address, sysdate); commit; end RECORD_destructive_ddl; end object_security; / ------------------------------------------------------------------------------------------------------------------- The idea again is to timestamp the destructive DDL. Again, the code has not been thoroughly tested. Also, it needs improvements such as excluding routinely truncated tables. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -----Original Message----- Sent: Monday, June 11, 2001 11:26 AM To: Multiple recipients of list ORACLE-L You are getting current SQL but you need to go thru all SQL for this session to find out if there was alter table drop (column). Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).