Capture all DDL run on a database instance

April 24, 2019

1–2 minutes

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 responses to “Capture all DDL run on a database instance”

Alex

April 24, 2019 at 10:44 am

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. Connor McDonald
  
  April 24, 2019 at 3:39 pm
  
  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
Alex

April 24, 2019 at 10:31 pm

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
Simon

July 15, 2019 at 8:32 pm

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. Connor McDonald
  
  July 15, 2019 at 9:01 pm
  
  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
Simon Pane

August 2, 2019 at 8:15 am

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. Connor McDonald
  
  August 2, 2019 at 8:25 am
  
  Thanks for the awesome feedback. Will amend the code.
  Cheers, Connor
  
  Reply
  1. Simon Pane
    
    August 2, 2019 at 10:22 pm
    
    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