/**********************************************************************
 * File:	audit_ddl.sql
 * Type:	SQL*Plus script
 * Author:	Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date:	10Oct01
 *
 * Description:
 *	SQL*Plus script to issue AUDIT commands to enable database
 *	auditing of all DDL commands.
 *
 * Modifications:
 *********************************************************************/
whenever oserror exit failure
whenever sqlerror exit failure
column schema_user format a12
column what format a50
set pagesize 100 linesize 130 trimout on trimspool on
set echo on feedback on timing on

spool audit_ddl

connect / as sysdba

show user
show release

show parameter audit_trail

audit CLUSTER;
audit CONTEXT;
audit DATABASE LINK;
audit DIMENSION;
audit DIRECTORY;
audit INDEX;
audit PROCEDURE;
audit PROFILE;
audit PUBLIC DATABASE LINK;
audit PUBLIC SYNONYM;
audit ROLE;
audit ROLLBACK SEGMENT;
audit SEQUENCE;
REM audit SESSION; -- enable session auditing only if desired
audit SYNONYM;
audit SYSTEM AUDIT;
audit SYSTEM GRANT;
audit TABLE;
audit TABLESPACE;
audit TRIGGER;
audit TYPE;
audit USER;
audit VIEW;
audit ALTER SEQUENCE;
audit ALTER TABLE;
audit COMMENT TABLE;
audit GRANT DIRECTORY;
audit GRANT PROCEDURE;
audit GRANT SEQUENCE;
audit GRANT TABLE;
audit GRANT TYPE;

REM
REM Please set the number of days to retain audit-trail information
REM as desired.  Currently, this script will submit a DBMS_JOB job
REM to purge data from the sys.aud$ table older than 45 days...
REM
variable jobno number
exec dbms_job.submit(:jobno, 'begin delete from aud$ where timestamp# < sysdate - 45; end;', -
	sysdate+(1/1440), 'sysdate+1', TRUE)
select	job,
	schema_user,
	what,
	next_date,
	next_sec,
	broken,
	failures
from	user_jobs
where	job = :jobno;

exit success