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).

Reply via email to