Grab all the DDL

Posted by

I posted a video a couple of days ago showing a trigger mechanism to customize the capture of DDL that is issued on your database. The aim here is to be more generous with letting developers execute DDL on their Development databases, whilst still having a thorough record of the changes that are occurring. Of course, it goes without saying, which is why I am saying it 🙂 that this is not a replacement for good source code control.

Here’s the video

and here is the supporting code


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.

    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

  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;
    /

  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.

  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.

      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.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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