find the LAST_DDL_TIME change time of an Oracle object

SQL to find the LAST_DDL_TIME change time of an Oracle object in the database.

-- Get the name, type, date of change of the DDL of a user object.

select OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where
owner not in ('SYS','SYSTEM');

Dropping large columns in database - ORACLE

alter table table_name set unused 

There may be a situation where you want to drop a column that has a huge data 10 Million rows .It will take lot of time to drop that column and the worst part is that Oracle will place a lock on that tables until With the " alter table set unused " command you can make that column invisible to users. at a later point of time. when you set the column to unused it will be stored in sys as unused.

MARKING UNUSED COLUMN

sql> 

desc abc_test




Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 

sql>  

alter table abc_test add (lname varchar2(20))




table ABC_TEST altered.

sql>  

desc abc_test




Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER       

LNAME           VARCHAR2(20) 

sql> 

alter table abc_test set unused (lname)

table ABC_TEST altered.

sql>  

desc abc_test

Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 




Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done using the following. 

Note : 

Dropping the unused column will still put a lock on the base table. I suggest you to drop 
unused column during maintenance period, to avoid locking.

ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

The DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.

Physically dropping column 

To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns.

ALTER TABLE table_name DROP COLUMN column_name; -- 1 column
ALTER TABLE table_name DROP (column_name1, column_name2);  -- multiple columns

Configure email server to send job notifcations- Oracle

Sample for adding scheduler e-mail notification

    Connected to SQL*PLUS using a privileged user.Using the set_scheduler_attribute procedure we have set the email_sender attribute to the SMTP server IP address, and specified the port to 25:

SQL> connect / as sysdba
Connected.

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','10.155.252.333:25');

PL/SQL procedure successfully completed.

        where:

             host is the host name or IP address of the SMTP server.
            port is the TCP port on which the SMTP server listens. If not specified, the default port of 25 is used.

        If this attribute is not specified, set to NULL, or set to an invalid SMTP server address, the Scheduler cannot send job state e-mail notifications. SMTP servers that require secure sockets (SSL) connections or require user authentication are not supported.

Optional Setup (Default email sender )

    Using the same procedure we have set the email_sender attribute to set the default email address for the email notifications:

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','DBMS_NOTIFICATION@organization.net');

PL/SQL procedure successfully completed.

Confirm the email server has been set

Using the get_scheduler_attribute procedure we can check the current values of both email_server and email_sender attributes.

declare
 v_att VARCHAR2(64);
 v_att2 varchar2(64);
 BEGIN
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_att);
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_att2);
 dbms_output.put_line('server: ' || v_att);
 dbms_output.put_line('sender: ' || v_att2);
 END;
 /


 Create Sample Job

    Connecting to the test user to create a sample job and created the test_mail job, that will start after 10 seconds from now.

SQL> BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
 job_name => 'TEST_MAIL',
 job_type => 'PLSQL_BLOCK',
 job_action => 'begin
 for i in 1..200 loop
 for j in 1..200 loop
 null;
 end loop;
 end loop;
 end;
 ',
 start_date => systimestamp + interval '10' second ,
 repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
 enabled => True,
comments => '');
 END;
 /

Add Job Notifications to send emails :


    Added the mail notification to the job, we have altered the value of the sender, so the default sender will not be used. Also we used job_all_events that will send mail notification for all events:

SQL> BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
 job_name => 'TEST_MAIL',
 recipients => 'arvind@organization.net',
 sender => 'DBMS_NOTIFICATION@organization.net',
 subject => 'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
 body => '%event_type% occurred at %event_timestamp%. %error_message%',
 events => 'job_all_events');
 END;
 /

PL/SQL procedure successfully completed.


    To review the scheduler email notifications setup, we will use the newly introduced views:
    USER | ALL | DBA_SCHEDULER_NOTIFICATIONS

SQL> desc user_SCHEDULER_NOTIFICATIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB_NAME                                  NOT NULL VARCHAR2(30)
 JOB_SUBNAME                                        VARCHAR2(30)
 RECIPIENT                                 NOT NULL VARCHAR2(4000)
 SENDER                                             VARCHAR2(4000)
 SUBJECT                                            VARCHAR2(4000)
 BODY                                               VARCHAR2(4000)
 FILTER_CONDITION                                   VARCHAR2(4000)
 EVENT                                              VARCHAR2(19)
 EVENT_FLAG                                NOT NULL NUMBER

SQL> select EVENT from user_SCHEDULER_NOTIFICATIONS where job_name='TEST_MAIL';

EVENT
-------------------
JOB_STARTED
JOB_SUCCEEDED
JOB_FAILED
JOB_BROKEN
JOB_COMPLETED
JOB_STOPPED
JOB_SCH_LIM_REACHED
JOB_DISABLED
JOB_CHAIN_STALLED
JOB_OVER_MAX_DUR

10 rows selected


Finally, after all the testing is done we can now remove the (Just) email notification part for the job:

SQL> connect test_job/test_job
Connected.

SQL> exec DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION ('TEST_MAIL');

PL/SQL procedure successfully completed.

Drop scheduler job :

SQL> BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name => 'TEST_MAIL',
                                defer => false,
                                force => false);
END;
/



Note : this has been tested on Oracle 11g Databases

Automating Oracle ADDM Reports with PL/SQL: A Guide to Sending Performance Reports via Email

Blog Article Title: "Automating Oracle ADDM Reports with PL/SQL: A Guide to Sending Performance Reports via Email"

Article Description:

In this article, we will explore how to automate the process of generating and sending Oracle Automatic Database Diagnostic Monitor (ADDM) reports using PL/SQL. ADDM provides valuable insights into database performance, identifying potential issues and offering recommendations for tuning. By integrating PL/SQL with email functionalities, you can streamline the delivery of these performance reports directly to your inbox, making it easier to monitor and address database health. This guide will cover the components of a PL/SQL package designed for this purpose, with detailed explanations of each section, making it an essential resource for Oracle DBAs looking to enhance their monitoring capabilities.

Note: You can use either DBMS_Sceduler to set job inside the database or trigger through any shell script through the command prompt (sqlplus)

This is an awesome script that I found online blog post by Gokhan Atil (ORACLE ACE). I wanted to share this with my friends, as it will be very helpful in daily maintenance.

PLSQL for ADDM sent via EMAIL:

DECLARE
   dbid           NUMBER;
   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);
   host_name      VARCHAR2(64);
   status         VARCHAR2(11);
   starttime      CHAR (5);
   endtime        CHAR (5);
   output         VARCHAR2 (32000);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := 'arvind@domain.com';
   v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';
   v_mail_conn    UTL_SMTP.connection;
   tname varchar2(50);
   tid   number;
BEGIN
   starttime := '01:00';
   endtime := '12:00';

   SELECT MIN (snap_id), MAX (snap_id)
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
      AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
      AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE);

   SELECT dbid, db_unique_name
     INTO dbid, db_unique_name
     FROM v$database;

   SELECT host_name INTO host_name
     FROM v$instance;
   
    DBMS_ADVISOR.CREATE_TASK('ADDM',tid,tname,'ADDM Report( '
    || bid || ',' || eid || ' )');
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'START_SNAPSHOT',bid );
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'END_SNAPSHOT',eid );
    DBMS_ADVISOR.EXECUTE_TASK( tname );  
   
    status := 0;
 
    while status <> 'COMPLETED' loop
    select status into status from dba_advisor_tasks where task_id = tid;
    dbms_lock.sleep(5);  
    end loop;

   v_from := db_unique_name ||  '@' || host_name;

   v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
   UTL_SMTP.HELO (v_mail_conn, v_mail_host);
   UTL_SMTP.MAIL (v_mail_conn, v_from);
   UTL_SMTP.RCPT (v_mail_conn, v_recipient);
   UTL_SMTP.OPEN_DATA( v_mail_conn );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '
        || 'ADDM Report of ' || v_from || ' '
        || SYSDATE || ' ' || starttime || '-' || endtime
        || UTL_TCP.CRLF || UTL_TCP.CRLF );


   SELECT DBMS_ADVISOR.GET_TASK_REPORT( tname) INTO output FROM DUAL;
   UTL_SMTP.WRITE_DATA (v_mail_conn, output );
 
   UTL_SMTP.CLOSE_DATA (v_mail_conn);
   UTL_SMTP.QUIT (v_mail_conn);
 
EXCEPTION
   WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR
   THEN
      RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM);
END;
/

Note : 

works only if you have set the SMTP server and ACL if you are working on Database 11G or higher



Extracted from :

http://www.gokhanatil.com/2011/07/create-awr-and-addm-reports-and-send-them-via-email.html

Plsql Package to receive an Oracle Database AWR Report sent to EMAIL

In this article, we delve into the practical implementation of using a PL/SQL package to automate the generation and distribution of Oracle Database Automatic Workload Repository (AWR) reports via email. AWR reports are essential tools for database administrators, providing in-depth performance metrics and insights that help in diagnosing issues, optimizing queries, and improving overall database performance. Manually generating these reports and sharing them with stakeholders can be time-consuming and prone to delays.

To streamline this process, we demonstrate how to create a PL/SQL package that automates the generation of AWR reports and sends them directly to your inbox or other designated recipients. We will cover the package setup, key PL/SQL procedures, and how to configure the email functionality using Oracle’s UTL_MAIL or UTL_SMTP package. This solution not only saves time but also ensures that critical performance data is consistently monitored and shared with the right team members.

By the end of this article, you will have a ready-to-use script that simplifies AWR report distribution, enhancing your ability to maintain optimal database performance with minimal manual intervention. Whether you are a seasoned DBA or new to Oracle performance tuning, this guide will help you automate a critical aspect of your database management workflow.

This is an awesome script that I found online blog post by Gokhan Atil (ORACLE ACE).I wanted to share this with my friends, as it will be very helpful in daily maintenance.

PLSQL to send the AWR report to your email directly.

DECLARE
   dbid           NUMBER;
   inst_id        NUMBER;
   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);
   host_name       VARCHAR2(64);
   starttime      CHAR (5);
   endtime        CHAR (5);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := 'arvind@domain.com';
   v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';
   v_mail_conn    UTL_SMTP.connection;
BEGIN
   starttime := '06:00';
   endtime := '10:00';

   SELECT MIN (snap_id), MAX (snap_id)
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
      AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
      AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE);

   SELECT dbid, inst_id, db_unique_name
     INTO dbid, inst_id, db_unique_name
     FROM gv$database;

   SELECT host_name INTO host_name
     FROM v$instance;

   v_from := db_unique_name ||  '@' || host_name;

   v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
   UTL_SMTP.HELO (v_mail_conn, v_mail_host);
   UTL_SMTP.MAIL (v_mail_conn, v_from);
   UTL_SMTP.RCPT (v_mail_conn, v_recipient);
   UTL_SMTP.OPEN_DATA( v_mail_conn );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '
        || 'AWR Report of ' || v_from || ' '
        || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF  );
   UTL_SMTP.WRITE_DATA ( v_mail_conn,
        'Content-Type: text/html; charset=utf8'
        || UTL_TCP.CRLF || UTL_TCP.CRLF );  

   FOR c1_rec IN
      (SELECT output
         FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(dbid,
           inst_id, bid, eid, 8 )))
   LOOP
      UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF );
   END LOOP;
 
   UTL_SMTP.CLOSE_DATA (v_mail_conn);
   UTL_SMTP.QUIT (v_mail_conn);
 
EXCEPTION
   WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR
   THEN
      RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM);
END;



Note : 

works only if you have set the SMTP server and ACL if you are working on Database 11G or higher



Extracted from :

http://www.gokhanatil.com/2011/07/create-awr-and-addm-reports-and-send-them-via-email.html

Connecting to CDB and PDB - Oracle 12c

Creation on a CDB (Container database) creates a service named is the CDB name. This is a side effect of creating a PDB (Pluggable Database) in the CDB, a service is created inside it with a property that identifies it as the initial current container. The service is also started as a side effect of creating the PDB. Although its metadata is recorded inside the PDB, the invariant is maintained so that a service name is unique within the entire CDB.



Use the Easy Connect syntax to connect to the root unless a net service name is configured in the tnsnames for the root service.




. oraenv

[enter cdb1 at the prompt]


sqlplus sys/oracle@localhost:1521/cdb1 as sysdba

show con_name

show con_id

Connect to the root by using OS authentication.



connect / as sysdba

show con_name

show con_id 






Display the list of available services for the root and the PDBs.



select name, con_id from v$active_services order by 1;



Use the Easy Connect syntax to connect to the PDB unless a net service name is configured in the tnsnames for the PDB service.



connect sys/oracle@localhost:1521/pdb1 as sysdba

show con_name

show con_id

exit

Trigger to backup the data before delete or update on a table - Oracle

Lets create a table :

 CREATE TABLE "ABC" 
   ( "EMPNO" NUMBER(4,0), 
"ENAME" VARCHAR2(10 BYTE), 
"JOB" VARCHAR2(9 BYTE), 
"MGR" NUMBER(4,0), 
"HIREDATE" DATE, 
"SAL" NUMBER(7,2), 
"COMM" NUMBER(7,2), 
"DEPTNO" NUMBER(2,0)
   ) TABLESPACE "USERS" ;

Now we will insert some data into it :

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);

select count(*) from abc;
-- 5 rows

Lets create a backup table to store data. we are only storing part of the original table data, we can edit as per requirement :

  CREATE TABLE "SCOTT"."ABC_BAK" 
   ( "EMPNO" NUMBER(4,0), 
"ENAME" VARCHAR2(10 BYTE), 
"JOB" VARCHAR2(9 BYTE), 
"MGR" VARCHAR2(20 BYTE), 
"T_STAMP" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
   ) TABLESPACE "USERS" ;


   select count(*) from abc_bak;
   
   0 rows
   
   
Now lets create a trigger, that will triger all the data from the table and store it in backup table before delete or update:

CREATE OR REPLACE TRIGGER ABC_BAK1 
BEFORE UPDATE OR DELETE 
  ON ABC FOR EACH ROW
  BEGIN
      INSERT INTO ABC_BAK
 ( EMPNO,
   ENAME,
   JOB,MGR
    )
VALUES
 ( :old.EMPNO,
   :old.ENAME,
   :old.JOB,
   :old.MGR); 
END;


===========================================================

NOTE : You also add username and Host machine by adding below to trigger
You need to declare the variable first and then assgn values to it

DECLARE
   v_username varchar2(10);
      
  BEGIN
  -- Find username of person performing the DELETE on the table

   SELECT user INTO v_username FROM dual;

Add these to values ----

V_USERNAME,
   sys_context('userenv','host')   --- You can also add host machine here

===========================================================

Lets test our trigger is working fine.

delete from abc where ename=SCOTT;
delete from abc where ename=ALENN;
update abc set ename=ADAM where ename=ADAMS;
.
.
.
.
do some activity and test then validate the bak table.


Select * from abc_bak;

    EMPNO ENAME      JOB       MGR                  T_STAMP                       

---------- ---------- --------- -------------------- -------------------------------

      7788 SCOTT                                     09-FEB-15 06.35.28.862219000 PM 

      7499 ALLEN                                     09-FEB-15 06.35.28.862219000 PM 

      7521 WARD                                      09-FEB-15 06.35.28.862219000 PM 

      7876 ADAMS      CLERK                          09-FEB-15 06.35.53.457126000 PM 

      7900 JAMES      CLERK                          09-FEB-15 06.35.53.462433000 PM 

      7902 FORD       ANALYST                        09-FEB-15 06.35.53.466738000 PM 

      7934 MILLER     CLERK                          09-FEB-15 06.35.53.472108000 PM 

      7788 SCOTT      ANALYST   7566                 09-FEB-15 06.37.30.307425000 PM 

 8 rows selected 

DELETE OS AUDIT FILES IN ORACLE

[atoorpu@ORACLE1 adump]$ pwd
/u01/app/oracle/admin/ORCL/adump
[atoorpu@ORACLE1 adump]$ ls -1 /u01/app/oracle/admin/ORCL/adump | wc -l
22273
[atoorpu@ORACLE1 adump]$ ls -lrt *aud | wc -l
11363
[atoorpu@ORACLE1 adump]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 9 10:40:58 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

If the audit files are in the database (sys.aud$). They can be cleaned up using:

SQL> DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);

If the audit files are in the OS. They can be cleaned up using:
SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
   END;
/  

PL/SQL procedure successfully completed.

The CLEAN_AUDIT_TRAIL procedure is the basic mechanism for manually purging the audit trail. It accepts two parameters.

AUDIT_TRAIL_TYPE: The audit trail whose timestamp is to be set (Constants). Only individual audit trails are valid, not the constants that specify multiples.

Types :
AUDIT_TRAIL_XML  ---- For Auditing on XML (XML files)
AUDIT_TRAIL_OS   --- For Auditing on OS (text files)
AUDIT_TRAIL_AUD_STD  --- For Standard Auditing

USE_LAST_ARCH_TIMESTAMP: Set to FALSE to purge all records/files, or TRUE to only purge records/files older than the timestamp specified for the audit trail.

 Lets see if the Last  Archive TS is  set for OS Audit files.


SQL> set pagesize 150
set linesize 150
col last_archive_ts format a40
select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;


AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 23-DEC-14 03.34.45.000000 PM +00:00



 You can also set the Last  Archive TS  if it is not set 
(in below it will set OS_AUDIT TS to sydate-45) :

SQL> BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-45);
END;
/  

PL/SQL procedure successfully completed.


 Now Lets confirm the last Archive Time stamp in DB.

SQL> COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;SQL> SQL> SQL>

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 23-DEC-14 03.34.45.000000 PM +00:00
OS AUDIT TRAIL                  1 26-DEC-14 10.43.05.000000 AM -06:00

SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
   END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[atoorpu@ORACLE1 adump]$ ls -lrt *aud | wc -l
1602
[atoorpu@ORACLE1 adump]$