Capture all DDL run on a database instance

DROP TABLE SYSTEM.DDL_LOG CASCADE CONSTRAINTS
/

CREATE TABLE SYSTEM.DDL_LOG
(
  TSTAMP       TIMESTAMP(6)   NOT NULL,
  HOST         VARCHAR2(100),
  IP_ADDRESS   VARCHAR2(100),
  MODULE       VARCHAR2(100),
  OS_USER      VARCHAR2(100),
  TERMINAL     VARCHAR2(100),
  OPERATION    VARCHAR2(100),
  OWNER        VARCHAR2(50),
  OBJECT_NAME  VARCHAR2(50),
  OBJECT_TYPE  VARCHAR2(50),
  SQLTEXT      CLOB,
  PREVSQLTEXT  CLOB
)
/


DROP TRIGGER SYSTEM.capture_all_ddl
/

CREATE OR REPLACE TRIGGER SYSTEM.CAPTURE_ALL_DDL
after create or alter or drop on database
begin
  --
  -- lots of flexibility here in choosing what you want to log
  -- and when etc etc.
  --
  if ora_dict_obj_owner in ('....')
    and  dbms_utility.format_call_stack not like '%NIGHTLY%'  -- not the nightly maint jobs
    and  nvl(sys_context('USERENV','MODULE'),'x') != 'DBMS_SCHEDULER'  -- not jobs
  then
    --
    -- and we can capture all the usual sys_context values
    --
    insert into SYSTEM.ddl_log
    values (systimestamp,
                sys_context('USERENV','HOST'),
                sys_context('USERENV','IP_ADDRESS'),
                sys_context('USERENV','MODULE'),
                sys_context('USERENV','OS_USER'),
                sys_context('USERENV','TERMINAL'),
                ora_sysevent,
                ora_dict_obj_owner,
                ora_dict_obj_name,
                ora_dict_obj_type,
                --
                -- In my case I choose to not log PL/SQL source, just the fact that it had been changed
                -- but you can do whatever you like here.
                --
                case when ora_dict_obj_type not in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and ora_sysevent != 'DROP' then
                  ( select sql_fulltext from v$sql
                    where sql_id = ( select sql_id from v$session where sid = sys_context('USERENV','SID') ) 
                    and rownum = 1 
                  )
                end,
                case when ora_dict_obj_type not in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and ora_sysevent != 'DROP' then
                  ( select sql_fulltext from v$sql
                    where sql_id = ( select prev_sql_id from v$session where sid = sys_context('USERENV','SID') ) 
                    and rownum = 1 
                  )
                end
           );
     
  end if;
exception
  when others then null;  -- we wil not STOP the ddl if we fail to track it
end;
/

8 comments

1. Conor, Thank you for sharing.
   I do not think that commit is needed here – it’s ddl trigger after all 🙂

   Interesting, but on 11.2 sql_fulltext truncated to 20 bytes – that is what I see in the ddl_log table.

   Reply

   1. Yeah – I remember that being a drama in 11.2 for all kinds of ddl logging – even in the alert log you’d only get 20 bytes. I’ve never checked, but perhaps the ora_sql_text structure contains the entire thing

      Reply

2. Thank you again for sharing. Simple and great.
   Tested on 18c and I see full ddl now. Maybe it was truncated because the first was done on 11.2 SE.

   Some thoughts:
   1. I use schema trigger instead of a database trigger. Too many non-app related ddl operations will trash the log table. And it’s easier to keep some enabled and others disabled if needed.

   2. I do not see the value of prev_sql. In my case, it’s always uninformative.

   3. I found that the version below works without noticeable delay between ddl statements, while the original is slow in my testing.
   drop trigger after_schema_ddl;
   create or replace trigger after_schema_ddl
   after create or alter or drop on schema
   declare
   l_sql_id varchar2(16);
   l_sql clob;
   begin
   begin
   select nvl(sql_id, prev_sql_id)
   into l_sql_id
   from v$session
   where sid = sys_context(‘userenv’,’sid’)
   and rownum = 1
   and ora_dict_obj_type not in (‘FUNCTION’, ‘PACKAGE’,’PACKAGE BODY’,’PROCEDURE’)
   /* and ora_sysevent != ‘DROP’ — want to log drop too */
   ;
   select sql_fulltext
   into l_sql
   from v$sql
   where sql_id = l_sql_id and rownum = 1; /* to be on the safe side as many sessions and children can have the same sql_id */

   exception
   when others then null;
   end;

   insert into utility.ddl_log
   values (
   systimestamp, ora_login_user, sys_context(‘userenv’,’os_user’), sys_context(‘userenv’,’host’)
   , sys_context(‘userenv’,’ip_address’), sys_context(‘userenv’,’module’), sys_context(‘userenv’,’terminal’)
   , ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type
   , l_sql, l_prev_sql
   );
   exception
   when others then null;
   end;
   /

   Reply

3. I have been looking at this for a while, and I’m getting errors from the trigger.

   [4098] ORA-04098: trigger ‘CAPTURE_ALL_DDL’ is invalid and failed re-validation

   Looking at the details in user_error, if either of the two subselects:

   case when ora_dict_obj_type not in (‘PACKAGE’,’PROCEDURE’,’FUNCTION’,’PACKAGE BODY’) and ora_sysevent != ‘DROP’ then
   ( select sql_fulltext from v$sql
   where sql_id = ( select sql_id from v$session where sid = sys_context(‘USERENV’,’SID’) and rownum = 1 )
   )
   END,

   is present I get a “table or view does not exist” error. If I comment these two out and insert NULL, the trigger works fine.

   If I run this separately:

   select sql_fulltext from v$sql
   where sql_id = ( select sql_id from v$session where sid = sys_context(‘USERENV’,’SID’) and rownum = 1 )

   It works fine.

   I’m not sure where to start troubleshooting. Not the trigger is not in SYS. And it is ‘AFTER DDL ON SCHEMA’, but I cant see why that would make a difference

   Thanks for your work.

   Reply

   1. This is a privileges issue. See this link for why you’re getting the error in the trigger

      http://asktom.oracle.com/Misc/RolesAndProcedures.html

      Reply

4. Hi Conner, great technique and very valuable but some small bugs:

   1) You need to add a child_number=0 predicate to both sub-queries as you’re suppressing errors and not seeing that it doesn’t catch all DDL due to exceptions. If you change the exception to raise, you’ll see that it fails sometimes due to child cursors and hence multiple rows being returned from v$sql for the given sql_id. The child_number predicate should fix that.

   2) Having the commit inside the trigger also raises an exception with 18c+ and needs to be removed.

   3) And I guess a DROP statement is just that so why bother logging it is maybe why you have the predicate to exclude it? But if you want to log whether the user included something like the “PURGE” option in the drop statement, even after commenting out your !=’DROP’ predicate, the drop operation is captured but not the associated DDL. Actually on 18c the trigger captures both an ALTER and DROP operation. Can you add an explanation around that? I haven’t given it a lot of thought yet but I might guess recursive SQL or something.

   Thanks – great technique.

   Simon.

   Reply

   1. Thanks for the awesome feedback. Will amend the code.
      Cheers, Connor

      Reply

      1. Awesome. P.S. Sorry about the typo in your name spelling – I’m on the other end of that all the time, hence you would think I’m more careful about that sort of thing. Sorry, my bad.

         Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.