Skip to main content


How to Setup Auditing
Do the following to set up auditing on your database:

1. Modify the "init.ora" file, usually located in the "$ORACLE_HOME/dbs"
directory to enable the AUDIT_TRAIL parameter and stop/start the instance to
make the parameter effective.

AUDIT_TRAIL can be set to one of the following four values:

--> DB/TRUE enables systemwide auditing where audited records are written to
the database audit trail, the SYS.AUD$ table
--> OS enables systemwide auditing where audited records are written to the
operating system's audit trail

--> DB_EXTENDED enables systemwide auditing as DB/TRUE does; in addition, it
populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table

DB_EXTENDED can be used starting with 10g only. More informations can be found in
Note 249438.1 - 10G New Value DB_EXTENDED for the AUDIT_TRAIL Parameter

--> NONE/FALSE disables auditing. This is the default value.


2. If you have set AUDIT_TRAIL = OS, modify the "init.ora" file to specify
the destination for the audited records using the AUDIT_FILE_DEST parameter.
If your operating system supports AUDIT_TRAIL = OS auditing, files are
automatically created in the AUDIT_FILE_DEST for certain actions, and the
generated name contains the OS PID of the shadow process audited:

$ ls -l $ORACLE_HOME/rdbms/audit
total 68
-rw-rw---- 1 ora92 dba 881 Mar 17 09:57 ora_13264.aud
$ ps -efgrep 13264

ora92 13264 13235 0 09:56:43 ? 0:00 oracleV92 (DESCRIPTION=(LOCAL=Y)
SQL> select spid, program, username from v$process;
------------ -------------------------------------------- -------------
13264 oracle@frhp11 (TNS V1-V3) ora92

Note: Windows NT does not use the AUDIT_FILE_DEST parameter.
Operating System auditing changes are recorded in the NT Event Viewer.

3. Make sure auditing has not already been installed.
Check for the existence of audit views:

SVRMGR> connect internal
SVRMGR> select * from sys.aud$; -- no rows should be returned
SVRMGR> select * from dba_audit_trail; -- no rows should be returned

If these views do not exist continue with the following, otherwise go to
step 4:

SVRMGR> connect internal
SVRMGR> @cataudit.sql

The auditing table will be installed in the SYSTEM tablespace.
It is therefore important that you have enough space available in this
tablespace before you enable auditing.
Note that as of version 8.1.7, cataudit.sql is run as part of a normal database
installation as it is called by catalog.sql, so there should be no need to
run cataudit.sql manually.

4. Shutdown and then restart the database, to enable the changes made to the
initialization parameters to be recognized.

SVRMGR> shutdown immediate
SVRMGR> startup

5. Specify the Audit option to be used.
The user must have system privilege 'audit system' privilege to execute
audit or noaudit commands.
SQL> connect system/manager
SQL> grant audit system to scott;
SQL> connect scott/tiger
SQL> audit session;
and to disable session auditing:
SQL> noaudit session;


Rozer Baine said…
I am very grateful that I got some useful knowledge about auditing from this post. Thank you so much for posting it. Keep it up. Fedex Audit
Harry jack said…
However, on this case, now no longer most effective might the deliver store IFO (foremost engine gasoline) however additionally store MDO (generator engine gasoline). Shipping from China to Usa
This article provided me with a wealth of information about Auditing and singapore tax. The article is both educational and helpful. Thank you for providing this information. Keep up the good work.
Unknown said…
The article you've shared here is fantastic because it provides a wealth of information that will be incredibly beneficial to me. Thank you for sharing about audit opinion letter. Continue to post.

Popular posts from this blog

Basics of RDBMS

Data Small set of information becomes data, this set of information helps make decision. Data is always some useful information. Database Place where you store the data. Database represents some aspect of the real world called "miniworld". A database is designed, built and populated with data for a specific purpose. It has intended group of users and some preconceived applications in which these users are interested. In other words, a database has some source from which data is derived, some degree of interaction with events in the real world and an audience that is actively interested in the contents of the database. Database can also be defined as collection of one or more tables. Ex: Mobile, human brain etc DBMS (Database Management System ) Is a program that stores retrieves and modifies data in the database on request. Study of different techniques of design, development and maintenance of the database Types of DBMS These types are based upon their m

SQL Interview Questions on Subqueries

SUB Queries: 1. List the employees working in research department 2. List employees who are located in New York and Chicago 3. Display the department name in which ANALYSTS are working 4. Display employees who are reporting to JONES 5. Display all the employees who are reporting to Jones Manager 6. Display all the managers in SALES and ACCOUNTING department 7. Display all the employee names in Research and Sales Department who are having at least 1 person reporting to them 8. Display all employees who do not have any reportees 9. List employees who are having at least 2 reporting 10. List the department names which are having more than 5 employees 11. List department name having at-least 3 salesman 12. List employees from research and accounting having at-least 2 reporting 13. Display second max salary 14. Display 4th max salary 15. Display 5th max salary  -- Answer for nth Max Salary Co-Related Subqueries: 16. Write a query to get 4th max salary from EMP table 17. Wri

Answers for SQL Functions

1. SQL> SELECT empno, ename FROM emp WHERE Length(ename) = 4; 2. SQL> SELECT empno, ename, job FROM emp where Length(job)=7; 3. SQL> SELECT Length('qspiders') - Length(replace('qspiders','s','')) FROM dual; 4. SQL>  SELECT empno, ename, job FROM emp WHERE Instr(job,'MAN') >0; 5. SQL> SELECT empno, ename, job FROM emp WHERE Instr(job, 'MAN') =1; 6. SQL> SELECT empno, ename, job FROM emp WHERE (Length(ename) - Length(Replace(ename, 'L',''))) = 1; 7. SQL> SELECT * FROM dept WHERE Instr(dname,'O') > 0; 8. SQL> SELECT Concat(ename,' working as a ') || Concat(job, ' earns ') || Concat(sal, '  in ') || Conc at('dept ',deptno) AS text from emp; OR SQL> SELECT Concat(Concat(Concat(Concat(Concat(Concat(Concat(ename,' working as a '), job),' earns '), sal),'  in '),'dept '), deptno) AS text FROM emp; 9. SQL