Password File (orapwd Utility ) of Oracle

Oracle password file stores passwords for users with administrative privileges.

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate the DBA. Obviously, DBA password cannot be stored in the database, because Oracle cannot access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA:

(i) Using the password file or

(ii) Through the operating system (groups). Any OS user under dba group, can login as SYSDBA.

The default location for the password file is:

$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix, %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

REMOTE_LOGIN_PASSWORDFILE

The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the DBA or not. If it set either to SHARED or EXCLUSIVE, password file will be used.

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without bouncing the database.

Following are the valid values for REMOTE_LOGIN_PASSWORDFILE:

NONE - Oracle ignores the password file if it exists i.e. no privileged connections are allowed over non secure connections. If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

EXCLUSIVE (default) - Password file is exclusively used by only one (instance of the) database. Any user can be added to the password file. Only an EXCLUSIVE file can be modified. EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

SHARED - The password file is shared among databases. A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. However, the only user that can be added/authenticated is SYS.

A SHARED password file cannot be modified i.e. you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER or SYSASM (this is from 11g) privileges generates an error. All users needing SYSDBA or SYSOPER or SYSASM system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED.

This option is useful if you are administering multiple databases or a RAC database.

If a password file is SHARED or EXCLUSIVE is also stored in the password file. After its creation, the state is SHARED. The state can be changed by setting REMOTE_LOGIN_PASSWORDFILE and starting the database i.e. the database overwrites the state in the password file when it is started up.

ORAPWD

You can create a password file using orapwd utility. For some Operating systems, you can create this file as part of standard installation.

Users are added to the password file when they are granted the SYSDBA or SYSOPER or SYSASM privilege.

The Oracle orapwd utility assists the DBA while granting SYSDBA, SYSOPER and SYSASM privileges to other users. By default, SYS is the only user that has SYSDBA and SYSOPER privileges. Creating a password file, via orapwd, enables remote users to connect with administrative privileges.

$ orapwd file=password_file_name [password=the_password] [entries=n] [force=Y|N] [ignorecase=Y|N] [nosysdba=Y|N]

Examples:

$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret

$ orapwd file=orapwprod entries=30 force=y

C:\orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora password=2012 entries=20

C:\orapwd file=D:\oracle11g\product\11.1.0\db_1\database\pwdsfs.ora password=id entries=6 force=y

$ orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n

$ orapwd file=orapwprodb password=oracle1 ignorecase=y

There are no spaces permitted around the equal-to (=).

The following describe the orapwd command line arguments.

FILE

Name to assign to the password file, which will hold the password information. You must supply complete path. If you supply only filename, the file is written to the current directory. The contents are encrypted and are unreadable. This argument is mandatory.

The filenames allowed for the password file are OS specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

If you are running multiple instances of Oracle Database using Oracle Real Application Clusters (RAC), the environment variable for each instance should point to the same password file.

It is critically important to secure password file.

PASSWORD

This is the password the privileged users should enter while connecting as SYSDBA or SYSOPER or SYSASM.

ENTRIES

Entries specify the maximum number of distinct SYSDBA, SYSOPER and SYSASM users that can be stored in the password file.

This argument specifies the number of entries that you require the password file to accept. The actual number of allowable entries can be higher than the number of users, because the orapwd utility continues to assign password entries until an OS block is filled. For example, if your OS block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.

FORCE

(Optional) If Y, permits overwriting an existing password file. An error will be returned if password file of the same name already exists and this argument is omitted or set to N.

IGNORECASE

(Optional) If Y, passwords are treated as case-insensitive i.e. case is ignored when comparing the password that the user supplies during login with the password in the password file.

NOSYSDBA

(Optional) For Data Vault installations.

Granting SYSDBA or SYSOPER or SYSASM privileges

Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER or SYSASM system privileges for a database.

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

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

SYS TRUE TRUE FALSE 

The columns displayed by the view V$PWFILE_USERS are:

Column	Description
USERNAME	This column contains the name of the user that is recognized by the password file.
SYSDBA	If the value of this column is TRUE, then the user can log on with SYSDBA system privilege.
SYSOPER	If the value of this column is TRUE, then the user can log on with SYSOPER system privilege.
SYSASM	If the value of this column is TRUE, then the user can log on with SYSASM system privilege.

If orapwd has not yet been executed or password file is not available, attempting to grant SYSDBA or SYSOPER or SYSASM privileges will result in the following error:

SQL> grant sysdba to him;

ORA-01994: GRANT failed: cannot add users to public password file

If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER or SYSASM system privilege to a user, as shown in the following example:

SQL> grant sysdba to him;

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

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

SYS TRUE TRUE FALSE 

HIM TRUE FALSE FALSE

SQL> grant sysoper to him;

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

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

SYS TRUE TRUE FALSE 

HIM TRUE TRUE FALSE

SQL> grant sysasm to HIM;

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

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

SYS TRUE TRUE FALSE 

HIM TRUE TRUE TRUE

When you grant SYSDBA or SYSOPER or SYSASM privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (i.e. if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle issues an error if you attempt to grant these privileges.

Use the REVOKE statement to revoke the SYSDBA or SYSOPER or SYSASM system privilege from a user, as shown in the following example:

SQL> revoke sysoper from him;

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER SYSASM

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

SYS TRUE TRUE FALSE 

HIM TRUE FALSE TRUE

A user's name remains in the password file only as long as that user has at least one of these three privileges. If you revoke all 3 privileges, Oracle removes the user from the password file.

Because SYSDBA, SYSOPER and SYSASM are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER or SYSASM privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPER or SYSASM system privileges. These privileges cannot be granted to roles, because roles are available only after database startup.

If you receive the file full error (ORA-01996) when you try to grant SYSDBA or SYSOPER or SYSASM system privileges to a user, you must create a larger password file and regrant the privileges to the users.

Removing Password File

If you determine that you no longer require a password file to authenticate users, you can delete the password file and then optionally reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the OS can perform SYSDBA or SYSOPER or SYSASM database administration operations.

Recycle bin in Oracle 10g

Recycle bin was introduced in 10^g

Recycle bin is actually a data dictionary table containing information about dropped objects. When an object has been dropped from a locally managed tablespace (LMTS), which is not the SYSTEM tablespace, the database does not immediately delete the object & reclaim the space associated with the object. Instead, it places the object and any dependent objects in the recycle bin, which is similar to deleting a file/folder from Windows/Macintosh. You can then restore the object, its data and its dependent objects from the recycle bin.

The FLASHBACK DROP and the FLASHBACK TABLE feature places the object in the recycle bin after removing the object from the database. This eliminates the need to perform a point-in-time recovery operation.

When objects are dropped, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. The recycle bin is merely a logical structure that catalogs the dropped objects.

The recyclebin is enabled, by default, from 10^g.
But you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.
SQL> ALTER SYSTEM/SESSION SET RECYCLEBIN=ON/OFF SCOPE=BOTH;
SQL> SHOW PARAMETER RECYCLEBIN

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin.

SQL> DROP TABLE attachment;
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$Wk/N7nbuC2DgRAAAd7F0UA==$0 TABLE

The deleted table has been renamed with system name, physically it’s not dropped.

The renaming convention is as follows:
BIN$unique_id$version
where:

• unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
• version is a version number assigned by the database

Use the following command to see recycle bin contents:
SQL> SELECT * FROM RECYCLEBIN;
or
SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------- ------------------------ ---------- -----------------
ATTACHMENT BIN$Wk/N7nbuC2DgRAAAd7F0UA==$0 TABLE 2008-10-28:11:46:55

This shows the original name of the table, as well as the new name in the bin.

Note that users can see only objects that they own in the recycle bin.

Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:
SQL> PURGE RECYCLEBIN;

But what if you want to drop the table completely, without needing a flashback feature, in that case, you can drop it permanently using:
SQL> DROP TABLE table-name PURGE;

This is similar to SHIFT+DELETE in Windows. This command will not rename the table to the recycle bin name; rather, it will be deleted permanently, as it would have been before 10^g.

To get back the deleted table and its contents
SQL> FLASHBACK TABLE table-name/bin-name TO BEFORE DROP [RENAME TO new-name];

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin.
SQL> SELECT * FROM "BIN$W1PPyhVRSbuv6g+V69OgRQ==$0";

Managing Recycle Bin

If the tables are not really dropped in this process, therefore not releasing the tablespace, what happens when the dropped objects take up all of that space?
When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out manner. The dependent objects (such as indexes) are removed before a table is removed.

Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.

In addition, there are several ways you can manually control the recycle bin. If you want to purge the specific table from the recyclebin after its drop, you could issue
SQL> PURGE TABLE table-name;

or using its recycle bin name

SQL> PURGE TABLE "BIN$Wk/N7nbuC2DgRCBAd7F0UA==$0";

This command will remove table and all dependent objects such as indexes, constraints, and so on from the recycle bin, saving some space.

If you want to permanently drop an index from the recycle bin, you can do so using:
SQL> PURGE INDEX index-name;

This will remove the index only, leaving the copy of the table in the recycle bin. Sometimes it might be useful to purge at a higher level. For instance, you may want to purge all the objects in recycle bin in a tablespace.
You would issue:
SQL> PURGE TABLESPACE tablespace-name;

You may want to purge only the recycle bin for a particular user in that tablespace. This approach could come handy in data warehouse environments where users create and drop many transient tables. You could modify the command above to limit the purge to a specific user only:
SQL> PURGE TABLESPACE tablespace-name USER user-name;

The PURGE TABLESPACE command only removes recyclebin segments belonging to the currently connected user. Therefore, it may not remove all the recyclebin segments in the tablespace. You can determine which users have recyclebin segments in a target tablespace using the following query:
SQL> SELECT DISTINCT owner FROM dba_recyclebin WHERE ts_name = "tablespace-name";

You can then use the above PURGE TABLESPACE command to purge the segments for each of the users.

A normal user, such as SCOTT, could clear his own recycle bin with
SQL> PURGE RECYCLEBIN;

A DBA can purge all the objects in any tablespace using
SQL> PURGE DBA_RECYCLEBIN;

The PURGE DBA_RECYCLEBIN command can be used only if you have SYSDBA system privileges. It removes all objects from the recycle bin, regardless of user.

Note: When a table is retrieved from the recycle bin, all the dependent objects for the table that are in the recycle bin are retrieved with it. They cannot be retrieved separately.

The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names. Sources such as views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved/renamed manually and then applied to the flashed-back table.

A few types of dependent objects are not handled like the simple index above.

• Bitmap join indexes are not put in the recyclebin when their base table is DROPped, and not retrieved when the table is restored withFLASHBACK DROP.
• The same goes for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
• Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.

If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first.

The constraint names are also not retrievable from the view. They have to be renamed from other sources.

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace.
The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

• When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.
• When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.
• When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup). Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin.

Related Views
RECYCLEBIN$ (base table)

DBA_RECYCLEBIN

USER_RECYCLEBIN
RECYCLEBIN (synonym for USER_RECYCLEBIN)

Auditing in Oracle

The auditing mechanism for Oracle is extremely flexible. Oracle stores information that is relevant to auditing in its data dictionary.

Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$. This table is, by default, located in the SYSTEM tablespace. This in itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.

init parameters
Until Oracle 10g, auditing is disabled by default, but can be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file.

From Oracle 11g, auditing is enabled for some system level privileges.

SQL> show parameter audit

NAME	TYPE	VALUE
----------------------	------------	-------------
audit_file_dest	string	?/rdbms/audit
audit_sys_operations	boolean	FALSE
audit_trail	string	NONE
transaction_auditing	boolean	TRUE

AUDIT_TRAIL can have the following values.
AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}

The following list provides a description of each value:

• NONE or FALSE -> Auditing is disabled. Default until Oracle 10g.
• DB or TRUE -> Auditing is enabled, with all audit records stored in the database audit trial (AUD$). Default from Oracle 11g.
• DB_EXTENDED –> Same as DB, but the SQL_BIND and SQL_TEXT columns are also populated.
• XML-> Auditing is enabled, with all audit records stored as XML format OS files.
• XML_EXTENDED –> Same as XML, but the SQL_BIND and SQL_TEXT columns are also populated.
• OS -> Auditing is enabled, with all audit records directed to the operating system's file specified by AUDIT_FILE_DEST.

Note: In Oracle 10g Release 1, DB_EXTENDED was used in place of "DB,EXTENDED". The XML options were brought in Oracle 10g Release 2.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the OS, XML and XML_EXTENDED options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS(no need to run this, if you ran catalog.sql at the time of database creation).

Start Auditing
Syntax of audit command:
audit {statement_option|privilege_option} [by user] [by
{session|access}] [whenever {successful|not successful}]

Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.

There are three levels that can be audited:

Statement level
Auditing will be done at statement level.
Statements that can be audited are found in STMT_AUDIT_OPTION_MAP.
SQL> audit table by scott;

Audit records can be found in DBA_STMT_AUDIT_OPTS.
SQL> select * from DBA_STMT_AUDIT_OPTS;

Object level
Auditing will be done at object level.
These objects can be audited: tables, views, sequences, packages, stored procedures and stored functions.
SQL> audit insert, update, delete on scott.emp by hr;

Audit records can be found in DBA_OBJ_AUDIT_OPTS.
SQL> select * from DBA_OBJ_AUDIT_OPTS;

Privilege level
Auditing will be done at privilege level.
All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.
SQL> audit create tablespace, alter tablespace by all;

Specify ALL PRIVILEGES to audit all system privileges.

Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;

Audit options
BY SESSION
Specify BY SESSION if you want Oracle to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session.

Oracle database can write to an operating system audit file but cannot read it to detect whether an entry has already been written for a particular operation. Therefore, if you are using an operating system file for the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to OS), then the database may write multiple records to the audit trail file even if you specify BY SESSION.
SQL> audit create, alter, drop on currency by xe by session;
SQL> audit alter materialized view by session;

BY ACCESS 
Specify BY ACCESS if you want Oracle database to write one record for each audited statement and operation.

If you specify statement options or system privileges that audit data definition language (DDL) statements, then the database automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.

For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.
SQL> audit update on health by access;
SQL> audit alter sequence by tester by access;

WHENEVER [NOT] SUCCESSFUL 
Specify WHENEVER SUCCESSFUL to audit only SQL statements and operations that succeed.
Specify WHENEVER NOT SUCCESSFUL to audit only SQL statements and operations that fail or result in errors.

If you omit this clause, then Oracle Database performs the audit regardless of success or failure.
SQL> audit insert, update, delete on hr.emp by hr by session whenever not successful;
SQL> audit materialized view by pingme by access whenever successful;

Examples
Auditing for every SQL statement related to roles (create, alter, drop or set a role).
SQL> AUDIT ROLE;

Auditing for every statement that reads files from database directory
SQL> AUDIT READ ON DIRECTORY ext_dir;

Auditing for every statement that performs any operation on the sequence
SQL> AUDIT ALL ON hr.emp_seq;

View Audit Trail
The audit trail is stored in the base table SYS.AUD$.
It's contents can be viewed in the following views:
· DBA_AUDIT_TRAIL
· DBA_OBJ_AUDIT_OPTS
· DBA_PRIV_AUDIT_OPTS
· DBA_STMT_AUDIT_OPTS
· DBA_AUDIT_EXISTS
· DBA_AUDIT_OBJECT
· DBA_AUDIT_SESSION
· DBA_AUDIT_STATEMENT
· AUDIT_ACTIONS
· DBA_AUDIT_POLICIES
· DBA_AUDIT_POLICY_COLUMNS
· DBA_COMMON_AUDIT_TRAIL
· DBA_FGA_AUDIT_TRAIL (FGA_LOG$)
· DBA_REPAUDIT_ATTRIBUTE
· DBA_REPAUDIT_COLUMN

The audit trail contains lots of data, but the following are most likely to be of interest:
Username - Oracle Username.
Terminal - Machine that the user performed the action from.
Timestamp - When the action occurred.
Object Owner - The owner of the object that was interacted with.
Object Name - name of the object that was interacted with.
Action Name - The action that occurred against the object (INSERT, UPDATE, DELETE, SELECT, EXECUTE)

Fine Grained Auditing (FGA), introduced in Oracle9i, allowed recording of row-level changes along with SCN numbers to reconstruct the old data, but they work for select statements only, not for DML such as update, insert, and delete.

From Oracle 10g, FGA supports DML statements in addition to selects.

Several fields have been added to both the standard and fine-grained audit trails:

• EXTENDED_TIMESTAMP - A more precise value than the existing TIMESTAMP column.
• PROXY_SESSIONID - Proxy session serial number when an enterprise user is logging in via the proxy method.
• GLOBAL_UID - Global Universal Identifier for an enterprise user.
• INSTANCE_NUMBER - The INSTANCE_NUMBER value from the actioning instance.
• OS_PROCESS - Operating system process id for the oracle process.
• TRANSACTIONID - Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
• SCN - System change number of the query. This column can be used in flashback queries.
• SQL_BIND - The values of any bind variables if any.
• SQL_TEXT - The SQL statement that initiated the audit action.
  The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL=DB_EXTENDED or AUDIT_TRAIL=XML_EXTENDED initialization parameter is set.

Maintenance
The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unacceptable size.

Only users who have been granted specific access to SYS.AUD$ can access the table to select, alter or delete from it. This is usually just the user SYS or any user who has had permissions. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE. These roles should not be granted to general users.

Auditing modifications of the data in the audit trail itself can be achieved as follows
SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

To delete all audit records from the audit trail:
SQL> DELETE FROM sys.aud$;

From Oracle 11g R2, we can change audit table's (SYS.AUD$ and SYS.FGA_LOG$) tablespace and we can periodically delete the audit trail records using DBMS_AUDIT_MGMT package.

Disabling Auditing
The NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement, privilege and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.

SQL> NOAUDIT;
SQL> NOAUDIT session;
SQL> NOAUDIT session BY scott, hr;
SQL> NOAUDIT DELETE ON emp;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;