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