how to get SQL stament in database auditing?

Hi,

I recently enabled auditing on my database.i audit all DDL,DML.its working fine.when i query SQL> select username, owner, obj_name, action_name,
  2          decode(returncode, '0','Success',returncode) decode,
  3          to_char(timestamp,'DD-MON-YYYY HH24:MI') login
  4          from dba_audit_object;

USERNAME     OWNER           OBJ_NAME   ACTION_NAM DECODE     LOGIN            
------------ --------------- ---------- ---------- ---------- ---------------  
REFERENCE_ST REFERENCE_STAGE MEMBERS    INSERT     Success    10-MAR-2011 05:  
AGE                                                           27                
                                                                               
REFERENCE_ST REFERENCE_STAGE MEMBERS    INSERT     Success    10-MAR-2011 05:  
AGE                                                           27                
                                                                               
REFERENCE_ST REFERENCE_STAGE MEMBERS    INSERT     Success    10-MAR-2011 05:  
AGE                                                           27                
                                                                               
REFERENCE_ST REFERENCE_STAGE MEMBERS    INSERT     Success    10-MAR-2011 05:  

i am getting the results like this.but i want to get  user changed SQL STAMENT(like select * from emp where empno='20').

can anybody tell how to get that .THQ
LVL 2
Cha1tuAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PilouteConnect With a Mentor Commented:
Aha... In this case, either your entries were not SQL, OR you missed something in the configuration... What is your AUDIT_TRAIL parameter ?

SQL_BIND & SQL_TEXT need the parameter AUDIT_TRAIL to be one of the following : db,extended or xml,extended. Otherwise they are not populated.

P
0
 
PilouteCommented:
Hi,

What's your db version ? I can't quite understand your question but guessing :

In 11.1, dba_audit_object contains a field called SQL_TEXT that actually contains the user's request.

Is this what you want ?

Cheers,
P
0
 
Cha1tuAuthor Commented:
@Piloute:  Thanks for your quick response.i am unsing 11g only,but when i qery this
SQL> select username, os_username, userhost, SQL_TEXT,priv_used from dba_audit_object where username = 'TEST';

USERNAME   OS_USERNAM   USERHOST  SQL_TEXT   PRIV_USED                              
-------- -             ---------           --------        ----------        ----------                              
TEST              HHKI\ABC          HHK\DEC                                                    
                                                           
                                                                               
TEST              HHKI\ABC          HHK\DEC                                                  
     


i am getting result like this.SQL_TEXT column is blank....its not displaying sql stament              
0
 
Cha1tuAuthor Commented:
I set the parameters like this

SQL> show parameter audit;

NAME                                        TYPE                VALUE
----------------------------------- ----------- ------------------------------
audit_file_dest                      string         F:\ORACLE_11G\RDBMS\AUDIT
audit_sys_operations          boolean      TRUE
audit_trail                             string          DB, EXTENDED


but still i am not getting SQL_TEXT..please help me


SQL> select username, os_username, userhost, SQL_TEXT,priv_used from dba_audit_object where username = 'TEST';

USERNAME   OS_USERNAM   USERHOST  SQL_TEXT   PRIV_USED                              
-------- -             ---------           --------        ----------        ----------                              
TEST              HHKI\ABC          HHK\DEC                                                    
                                                           
                                                                               
TEST              HHKI\ABC          HHK\DEC                                                  

0
All Courses

From novice to tech pro — start learning today.