Oracle Database Discussions

I am using oracle10g database.


Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



I enabled extended auditing on my database....

SQL> alter system set audit_trail=db,extended scope=spfile;

System altered.

Now i want to audit who ever applies any SELECT SQL on scott schema....

SQL> audit select table by scott by access;

Audit succeeded.


Now i ran the below query in SCOTT schema

select count(*) from emp

Then i went to system schema and select the data from dba_audit_trail table. It shows seven records including my original query.



1* select sql_text from dba_audit_trail
SQL> /

SQL_TEXT
--------------------------------------------------------------------------------
SELECT USER FROM DUAL
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)


SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)


SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPE
R(PRODUCT)) AND ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPE

SQL_TEXT
--------------------------------------------------------------------------------
R(ATTRIBUTE) = 'ROLES')

SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPE
R(PRODUCT)) AND ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPE
R(ATTRIBUTE) = 'ROLES')

SELECT DECODE('A','A','1','2') FROM DUAL
select count(*) from emp

7 rows selected.

SQL>


How do i eliminate the first 6 records..

Also how can i find how long that query run in the database? dba_audit_trail does not have that info...

Edited by: Shrinika on Mar 29, 2010 1:08 PM