Auditing your changes SQL

 
-- be absolutely certain you have access to another user with permission to drop any trigger
-- before you run this demo
-- or you may find yourself unable to run any DDL at all in this schema again!

col object_name format a30
create table so_i_have_a_record_of (
  what_happened varchar2(30),
  to_what varchar2(30),
  owned_by_whom varchar2(30)
);

create trigger i_want_to_log 
before ddl on schema
begin
  insert into so_i_have_a_record_of (
    what_happened, to_what, owned_by_whom
  ) values (
    ora_sysevent, ora_dict_obj_name, ora_dict_obj_owner
  );
end;
/

-- make some changes
create table just_a_boring_table (x integer);
alter table just_a_boring_table add y integer;
alter table just_a_boring_table add constraint just_a_pk primary key (x);
drop table just_a_boring_table purge;

-- and see what happened
select * from so_i_have_a_record_of;

-- uh-oh, we've broken the trigger
drop table so_i_have_a_record_of purge;

select object_name, status from user_objects
where  object_type = 'TRIGGER';

-- so now we can't create a new table...
create table just_a_boring_table (x integer);
drop table just_a_boring_table purge;

select object_name, status from user_objects
where  object_type = 'TRIGGER';

-- dropping the trigger works (for me), but be sure you have access to another user 
-- with "drop any trigger" privilege in case it doesn't work
drop trigger i_want_to_log;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)