Narsimha

• Jun 6th, 2006

 

Hi,

 

Please follow the below steps to record

1. which user has executed

2. which query is executed

3. at what time

 

Step 1: Store the query in a variable before executing it.

 

Step 2: Simply insert the above query variable, sysdate and user into the User Action table.

 

Note : User name (person logged in into the application) can be obtained from the session. 

 

This is feasible in both the cases i.e. if u build the query dynamically in Java or if u build it in a stored proc.

 

Please let me know if u need any clarification.

 

 

nareshg_009

 Profile Answers by nareshg_009 

• Dec 20th, 2007

 

Can you pls explain it a bit clearly
Thanks in advance

harinicm

 Profile Answers by harinicm 

• Dec 11th, 2009

 

Use AUDIT is used to track the occurrence of the sql statements in the subsequent user sessions (not the current session).  The audited record contains:

Name of the user

Type of the operation

Objects involved in that operation

Data and time of the operation

Oracle stores these audited record in the audit_trail , the database table containing the audited records. For example:

AUDIT SELECT TABLE, UPDATE TABLE

AUDIT SELECT TABLE, UPDATE TABLE BY Scott, Chandru

AUDIT DELETE ANY TABLE

AUDIT CREATE ANY DIRECTORY;

AUDIT READ ON DIRECTORY mydir;

AUDIT SELECT ON scott.emp ;

AUDIT UPDATE ON scott.emp WHENEVER SUCCESSFUL;

AUDIT UPDATE ON scott.emp WHENEVER NOT SUCCESSFUL;

AUDIT ALL ON scott.seq1

NOAUDIT – to stop the audit