Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 30, 2016 - 10:36 am UTC

Last updated: April 20, 2018 - 2:56 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

I am thinking of creating a utility Proc that will capture all Development DDL changes from the Database.
This utility will baseline all DDl's for a given release say R1 and while we are developing for release R2
The utility will create the incremental scripts based on the Baseline that I did for R1 - with the current database changes.
Now I would want to know how would I differentiate between a column Rename / vs a column Drop and re-create

consider these 2 scenarios with table t1

scenario1>
table t1 ( col1 NUMBER, col2 NUMBER) --Baselined R1
I renamed col2 to col3
so t1 now becomes ( col1 NUMBER, col3 NUMBER) --R2

scenario2>
table t1 ( col1 NUMBER, col2 NUMBER) --Baselined R1
DROP col2
ADD col3
so t1 now becomes ( col1 NUMBER, col3 NUMBER) --R2

How can I say ( looking into the oracle data dictionary ) that from release R1 to R2 whether the column has undergone a Rename or a Drop Re-Create.
I hope my question makes sense.

and Connor said...

Here's a basic ddl capture routine I wrote a while back. In my case, I was excluding PLSQL compiliations, but you can modify to suit your needs. Hope it helps

CREATE TABLE 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
)
LOB (SQLTEXT) STORE AS (
  TABLESPACE  USERS
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  PCTVERSION  0)
LOB (PREVSQLTEXT) STORE AS (
  TABLESPACE  USERS
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION)
RESULT_CACHE (MODE DEFAULT)
NOCOMPRESS 
/


CREATE INDEX DDL_LOG_IX ON DDL_LOG
(TSTAMP)
/

DROP TRIGGER SYS.capture_all_ddl
/

CREATE OR REPLACE TRIGGER SYS.CAPTURE_ALL_DDL
after create or alter or drop on database
begin
  if ora_dict_obj_owner in (...)
  then
    insert into 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,
                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
           );
     commit;
  end if;
exception
  when others then ...
end;
/


Addenda 2017
============
There is a parameter in recent versions of Oracle

enable_ddl_logging

which if set to true, will save DDL output to a file ddl_[oraclesid].log in your diagnostic repository.

Note that use of this parameter requires the Database Lifecycle Management Pack license.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perf

A reader, October 11, 2016 - 5:33 am UTC

As you have use it. Did you mesure perf impacts ?
Is there any? Final recommendation ?
Connor McDonald
October 11, 2016 - 9:01 am UTC

I've used it at many client sites over the past 10 years

Question

A reader, February 12, 2017 - 6:33 am UTC

"....and ora_sysevent != 'DROP' .."

For what reasons you exclude Drop?
What are possible values for ora_sysevent?
Why not exclude also Truncate?
What code should add to overcome all types of objects in such single trigger?
Connor McDonald
February 13, 2017 - 10:39 pm UTC

My mistake - I simply copied from one from a place that had different requirements.

But one would imagine that you would use this a *template* to implement your *own* requirements - not just grab my code carte blanche no ?

Didn't get answers

A reader, February 14, 2017 - 4:52 am UTC

I am sorry. It wasn't a critical viewpoint but sincere need.
My questions above still no answered. Reviewing the initial need what changes to the code is needed in order to trace all possible ddl issued against the db?

John Smith, February 16, 2017 - 2:50 am UTC

Should add 'enable_ddl_logging'

Capturing Triggering SQL

Jane Essig, April 19, 2018 - 1:56 pm UTC

Thanks for your example Tom! I was using a similar trigger "after ddl on database", but it wasn't capturing the triggering SQL using sys_context('USERENV','CURRENT_SQL'). I don't care about the SQL used for comments, analyzes, or drops, so I excluded those with the case statements from your code. Very useful!
For the reviewer asking how to capture all DDL: "after ddl on database" will do the trick.
Connor McDonald
April 20, 2018 - 2:56 am UTC

glad we could help

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.