Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




Oracle Tips by Burleson 

Auditing the Database

Auditing is a method of recording database activity as part of database security. It allows the DBA to track user activity within the database. The audit records will provide information on who performed what database operation and when it was performed. Records are written to a SYS-owned table named AUD$. The SYS.AUD$ table is commonly referred to as the audit trail.      

It is advisable to copy the AUD$ table into a separate tablespace from other SYS-owned objects. In some cases, the AUD$ table should be owned by a user other than SYS. There are three reasons for these two statements: 

  • The AUD$ table, if auditing is used, may grow to a very large size, depending on the audit options selected.

  • In some situations, you may want to add a trigger to the AUD$ table to count logins, monitor for specific actions (prior to 8i), or perform other security-related functions (such as implement password-checking functionality prior to version 8). Remember, you can’t add triggers to catalog objects owned by SYS.

  • Since the table will grow and shrink and be high activity, it is advisable to move it from the SYSTEM tablespace to avoid fragmentation.

Auditing information is not collected without some impact on performance and database resources. How much of an impact auditing will have on your system depends largely on the type of auditing you enable. For example, setting high-level auditing such as connection activity will not have as much of a performance impact as tracking all SQL statements issued by all users. It is best to start out with high-level auditing and then refine additional auditing as needed.      

You can audit all users with the exception of SYS and CONNECT INTERNAL. Auditing can only be performed for users connected directly to the database, not for actions on a remote database.      

On some versions of Oracle7 (7.3.2 to 7.3.3) operating system-level auditing of SYS and INTERNAL logins was initiated automatically on install. Auditing at operating system level (EPC_DISABLE=FALSE) results in a small audit file being generated in an operating system-specific directory location for each use of SYS or INTERNAL. This system-level auditing also may result in longer and longer login times for all users as the files process.dat, regid.dat, and so on, in the $ORACLE_

HOME/otrace/admin directory on UNIX, or orant\otrave73\admin on NT, expand in size. This is disabled by dropping the *.dat files in the previously mentioned locations, shutting down the instance, typing otrccref from the command line to reinitiate the files, and then setting the environmental variable EPC_DISABLED to TRUE (EPC_DISABLED=TRUE (Borne shell), setenv EPC_DISABLED TRUE (C shell), and restarting the instance(s).

Auditing should be enabled if the following types of questionable activities are noted:

  • Unexplained changes in passwords, tablespace settings, or quotas appear.

  • Excessive deadlocks are encountered.

  • Records are being read, deleted, or changed without authorization.

There are three types of auditing:

  • Statement auditing

  • Privilege auditing

  • Object auditing

See Code Depot

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks