Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Capturing Schema changes ??

From: <Satheesh.Babu_at_iflexsolutions.com>
Date: Mon, 12 Jul 2004 16:07:34 +0530
Message-ID: <10898BE7CA96D611988B000802255AAF07118DCA@fmgrt.rt.i-flex.com> 





DDl trigger is at schema level.



Create a table event_table, add column as per you requirement. Here I am
capturing owner and object name. If you want to capture other
information you can sys_context.



SQL> desc event_table;

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------

 ORA_DICT_OBJ_OWNER                                 VARCHAR2(30)
 ORA_DICT_OBJ_NAME                                  VARCHAR2(30)





create or replace trigger role_update


after create or alter or drop or truncate on system.schema
declare


  pragma autonomous_transaction;


begin


  IF ( ora_sysevent=3D'CREATE' and ora_dict_obj_type =3D 'TABLE') THEN


       insert into event_table values



	(ora_dict_obj_owner, ora_dict_obj_name);

  elsif ( ora_sysevent=3D'ALTER' and ora_dict_obj_type =3D 'TABLE') THEN


       insert into event_table values



	(ora_dict_obj_owner, ora_dict_obj_name);

  elsif ( ora_sysevent=3D'TRUNCATE' and ora_dict_obj_type =3D 'TABLE') =
THEN



       insert into event_table values



	(ora_dict_obj_owner, ora_dict_obj_name);

  elsif ( ora_sysevent=3D'DROP' and ora_dict_obj_type =3D 'TABLE') THEN


       insert into event_table values



	(ora_dict_obj_owner, ora_dict_obj_name);

  END IF;



  COMMIT;



END;



/


SQL> CREATE TABLE TAB1 ( NO NUMBER );


Table created.



SQL> SELECT * FROM EVENT_TABLE;


ORA_DICT_OBJ_OWNER             ORA_DICT_OBJ_NAME


------------------------------ ------------------------------
SYSTEM                         TAB1







Thanks and Regards,


Satheesh Babu.S


Associate Consultant.


080-57593938


Bangalore.


India.







-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Leonard, George
Sent: Monday, July 12, 2004 3:45 PM


To: 'oracle-l_at_freelists.org'


Subject: RE: Capturing Schema changes ??



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




George Leonard


Oracle Database Administrator


New Dawn Technologies @ Wesbank


E-mail:gleonard_at_wesbank.co.za


=20


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,=20
Every Fool Has the Right to Kill or Injure Themselves as They See Fit!






-----Original Message-----


From: Satheesh.Babu_at_iflexsolutions.com


[mailto:Satheesh.Babu_at_iflexsolutions.com]
Sent: Monday, July 12, 2004 11:41 AM


To: oracle-l_at_freelists.org


Subject: RE: Capturing Schema changes ??




No I don't think fga could be a option here. FGA is meant for row level
=3D


security.=3D20


If your just looking for time by which the object definition is changed
=3D


or a object is created, then last_ddl_time&timestamp columns of =3D
dba_objects will help.


If you want to know more than that like which user has changed, from =3D
which machine etc., then ddl triggers will be best option.



Thanks and Regards,


Satheesh Babu.S


Associate Consultant.


080-57593938


Bangalore.


India.






-----Original Message-----


From: oracle-l-bounce_at_freelists.org =3D


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mogens N=3DF8rgaard
Sent: Monday, July 12, 2004 3:01 PM


To: oracle-l_at_freelists.org


Subject: Re: Capturing Schema changes ??



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



Would FGA be an option here?



Mogens



Stephane Faroult wrote:


> =3D20
> George,=3D20
>=3D20
>    If you really want details about what has truly occurred (instead =


=3D


of


> recording that *something* has occurred), I think that DDL triggers =
=3D


are the


> way to go (I don't think that audit has much evolved since at least =
=3D


the days


> of Oracle 5 ...). Beware though that some DDL statements fire several
> triggers (eg ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY ... if no
=3D


index


> to enforce the constraint already exists). But for the more trivial =
=3D


cases,


> it can become tricky.






Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

________________________________________________________________________
___________________________


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.=20
If you are not the intended recipient, any disclosure, copying,
distribution or any action taken or=20
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=20
transmitted electronically and to preserve the confidentiality thereof,
no liability or=20
responsibility whatsoever is accepted if information or data is, for
whatever reason, corrupted=20
or does not reach its intended destination.

                               ________________________________
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Mon Jul 12 2004 - 05:30:20 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US