Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Capturing Schema changes ??

RE: Capturing Schema changes ??

From: Leonard, George <>
Date: Mon, 12 Jul 2004 12:14:50 +0200
Message-ID: <>

Hi all

Some clearing up of requirements.

We are currently looking at source control/document management software to start tying up the different development/testing and then finally production environments together to display the creation and evolvement of processes.

Although we have very very few people making changes in production we want to be able to track changes since sometimes during the night we have a emergency and code gets changed to get a process/batch to complete.

And as with all humans this sometimes evade the responsible person from informing where required.

So one the one side we want something independent of human interaction to notify us that something was changed but we further want the before change version.

SO in summary, the DDL trigger option is high up the solution list.

now i remember reading here before that there was a solution like this on one of the resource sites. If someone has a copy of this would appreciate it or the link. One other question, and this depends on how the solution is designed, ORACLE Support for it. Guessing if triggers gets created on the Dictionary objects they could have a problem whereas if it is a trigger on schema... then we are ok.

I don't want to go and a trigger on every table since this would require to much changes every time a table is added or dropped.

Auditing looked nice but also thought about the before and after issue and the non ability to time/version stamp before versions...

thanks so far.


George Leonard
Oracle Database Administrator
New Dawn Technologies @ Wesbank  

You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!

-----Original Message-----
[] Sent: Monday, July 12, 2004 11:41 AM
Subject: RE: Capturing Schema changes ??

No I don't think fga could be a option here. FGA is meant for row level = security.=20
If your just looking for time by which the object definition is changed = or a object is created, then last_ddl_time&timestamp columns of = dba_objects will help.
If you want to know more than that like which user has changed, from = which machine etc., then ddl triggers will be best option.

Thanks and Regards,
Satheesh Babu.S
Associate Consultant.

-----Original Message-----
From: =
[] On Behalf Of Mogens N=F8rgaard Sent: Monday, July 12, 2004 3:01 PM
Subject: Re: Capturing Schema changes ??

Setting DML_LOCKS to 0 could also stop it :-).

Would FGA be an option here?


Stephane Faroult wrote:

> =20
> George,=20

> If you really want details about what has truly occurred (instead =
> recording that *something* has occurred), I think that DDL triggers =
are the
> way to go (I don't think that audit has much evolved since at least =
the days
> of Oracle 5 ...). Beware though that some DDL statements fire several
> triggers (eg ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY ... if no =
> to enforce the constraint already exists). But for the more trivial =
> it can become tricky.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at


The views expressed in this email are, unless otherwise stated, those of the author and not those
of the FirstRand Banking Group or its management.  The information in this e-mail is confidential
and is intended solely for the addressee. Access to this e-mail by anyone else is unauthorised. 
If you are not the intended recipient, any disclosure, copying, distribution or any action taken or 
omitted in reliance on this, is prohibited and may be unlawful.
Whilst all reasonable steps are taken to ensure the accuracy and integrity of information and data 
transmitted electronically and to preserve the confidentiality thereof, no liability or 
responsibility whatsoever is accepted if information or data is, for whatever reason, corrupted 
or does not reach its intended destination.

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Jul 12 2004 - 05:11:27 CDT

Original text of this message