Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Capturing Schema changes ??

From: Stephane Faroult <>
Date: Mon, 12 Jul 2004 11:19:06 +0200
Message-Id: <>



   If you really want details about what has truly occurred (instead of 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 index to enforce the constraint already exists). But for the more trivial cases, it can become tricky.
  This, however, looks to me like the ultimate rampart against barbarians changing wildly the schema, which hopefully should not happen, especially in a bank (I know ...). IMHO, CREATE privileges should be granted sparingly. Now, if we assume that schema changes are applied by some happy few who, hopefully, know what they are doing, your problem looks like a version control problem. In that case, AUDIT (to get the dates when applied) + any SCCS, RCS or whatever to manage the SQL scripts may provide a perfectly workable solution to get an historical report of changes, if this is mostly what you need.


Stephane Faroult

On Mon, 12 Jul 2004 10:55 , 'Leonard, George' <> sent:

Hi all

We are in need of a solution to capture all changes to a schema, table creates, alters drops,
procedure creates...
functions, views, indexes etc etc.

one option is Auditing,

The other is via a trigger based solution.

Any ideas, views, and examples would be appreciated.


George Leonard
Oracle Database Administrator
New Dawn Technologies @ Wesbank[1]

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!


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 ofinformation and data
transmitted electronically and to preserve the confidentiality thereof, no liability or
responsibility whatsoever is accepted if information or data is, for whateverreason, corrupted
or does not reach its intended destination.

Please see the official ORACLE-L FAQ:[2]

To unsubscribe send email to:[3] put 'unsubscribe' in the subject line.

Archives are at[4] FAQ is at[5]
Received on Mon Jul 12 2004 - 04:16:31 CDT

Original text of this message