Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle DDL Triggers

Oracle Tips by Burleson Consulting

 


Using Oracle DDL Triggers

Auditing database changes: Oracle provides DDL triggers to audit all schema changes and can report the exact change, when it was made, and by which user.  There are several ways to audit within Oracle and the following auditing tools are provided:
  • SQL audit command (for DML)
  • Auditing with object triggers (DML auditing)
  • Auditing with system-level triggers (DML and DDL)
  • Auditing with LogMiner (DML and DDL)
  • Fine-grained auditing (select auditing)
DDL triggers: Using the Data Definition Language (DDL) triggers, the Oracle DBA can automatically track all changes to the database, including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.

DDL triggers execute every time a DDL statement is executed, and adds new entries to your new table, as shown below:

connect sys/manager

create or replace trigger
DDLTrigger
AFTER DDL ON DATABASE
BEGIN
insert into
perfstat.stats$ddl_log
(
user_name,
ddl_date,
ddl_type,
object_type,
owner,
object_name
)
VALUES
(
ora_login_user,
sysdate,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name
);

END;
/

What is truly exciting is that we can combine the system-level triggers with STATSPACK extension tables, thereby building a mechanism that will give complete auditing information on user logon and logoff, DDL, and server errors.

Just as an Oracle trigger fires on a specific DM event, system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror triggers

DDL triggers -- Using Oracle DDL triggers, the Oracle administrator can automatically track all changes to the database including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA. 

Laurent Schneider also has this alternative DDL triggers script for DDL auditing:

CREATE TABLE AUDIT_DDL (
  d date,
  OSUSER varchar2(255),
  CURRENT_USER varchar2(255),
  HOST varchar2(255),
  TERMINAL varchar2(255),
  owner varchar2(30),
  type varchar2(30),
  name varchar2(30),
  sysevent varchar2(30));
  
create or replace trigger audit_ddl_trg after ddl on schema
begin
  if (ora_sysevent='TRUNCATE')
  then
    null; -- I do not care about truncate
  else
    insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','CURRENT_USER') ,
      sys_context('USERENV','HOST') ,
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent
    );
  end if;
end;
/

Now whenever a change is made, the details will be written to your table, and you can create audit reports for management. This report is critical for the Oracle DBA who must track changes to his or her production database. This report uses the DDL trigger and produces a complete audit log of all Oracle databases changes, as shown below:

DDL table -- Using the code snippet shown below, we create an Oracle table to capture all of the salient metrics required to do effective change management within an Oracle environment. Note this table contains the date that the DDL was made, the ID of the user who originated the DDL change, the type of the object, and the object's name. This information can be quite useful for tracking purposes.

DDL Triggers - DDL triggers execute every time a DDL statement is executed and adds new entries to the stats$ddl_log table.

DDL Reports - This report is critical for the Oracle DBA who must track changes to their production database. This report uses Oracle DDL triggers and produces a complete audit log of all Oracle databases changes.
 


 

 

 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.