How to tie V$session and V$sqlarea

Try this as the basics.  It will give you the known SQL_TEXT, sid, serial#, and usernames

select sid, serial#, username, sql_text
  from v$session a, v$sqlarea
 where hash_value = prev_hash_value;

To limit the listing and knowing only the sid and serial# you could go with:

select sid, serial#, username, sql_text
  from v$session a, v$sqlarea
 where hash_value = prev_hash_value
   and sid = &1
   and serial# = &2;

You can also consider:

select username, sql_text
  from v$session a, v$sql b
 where a.sql_hash_value = b.hash_value
   and a.sql_address = b.address
   and sid = &1;

You should also look at:

REM -----------------------------------------------
REM  List all sessions with their SQL statements
REM -----------------------------------------------

SET    PAGES   0       -
       NEWP    0       -
       LINES   132     -
       TERM    Off     -
       HEAD    Off

Col     X Form a132

SPOOL  all_session.log

TTitle  T Skip 2

Select  ' ' x
,       Lpad( 'SADDR', 24, ' ')||' : '||V$Session.SADDR x
,       Lpad( '_', 43, '_') x
,       Lpad( 'SID', 24, ' ')||' : '||V$Session.SID x
,       Lpad( 'SERIAL#', 24, ' ')||' : '||V$Session.SERIAL# x
,       Lpad( 'AUDSID', 24, ' ')||' : '||V$Session.AUDSID x
,       Lpad( 'PADDR', 24, ' ')||' : '||V$Session.PADDR x
,       Lpad( 'USER#', 24, ' ')||' : '||V$Session.USER# x
,       Lpad( 'USERNAME', 24, ' ')||' : '||V$Session.USERNAME x
,       Lpad( 'COMMAND', 24, ' ')||' : '||V$Session.COMMAND x
,       Lpad( 'TADDR', 24, ' ')||' : '||V$Session.TADDR x
,       Lpad( 'LOCKWAIT', 24, ' ')||' : '||V$Session.LOCKWAIT x
,       Lpad( 'STATUS', 24, ' ')||' : '||V$Session.STATUS x
,       Lpad( 'SERVER', 24, ' ')||' : '||V$Session.SERVER x
,       Lpad( 'SCHEMA#', 24, ' ')||' : '||V$Session.SCHEMA# x
,       Lpad( 'SCHEMANAME', 24, ' ')||' : '||V$Session.SCHEMANAME x
,       Lpad( 'OSUSER', 24, ' ')||' : '||V$Session.OSUSER x
,       Lpad( 'Client Process', 24, ' ')||' : '||V$Session.PROCESS x
,       Lpad( 'Server Process', 24, ' ')||' : '||V$Process.Spid x
,       Lpad( 'MACHINE', 24, ' ')||' : '||V$Session.MACHINE x
,       Lpad( 'TERMINAL', 24, ' ')||' : '||V$Session.TERMINAL x
,       Lpad( 'PROGRAM', 24, ' ')||' : '||V$Session.PROGRAM x
,       Lpad( 'TYPE', 24, ' ')||' : '||V$Session.TYPE x
,       Lpad( 'SQL_ADDRESS', 24, ' ')||' : '||V$Session.SQL_ADDRESS x
,       Lpad( 'SQL_HASH_VALUE', 24, ' ')||' : '||V$Session.SQL_HASH_VALUE x
,       Lpad( 'PREV_SQL_ADDR', 24, ' ')||' : '||V$Session.PREV_SQL_ADDR x
,       Lpad( 'PREV_HASH_VALUE', 24, ' ')||' : '||V$Session.PREV_HASH_VALUE x
,       Lpad( 'MODULE', 24, ' ')||' : '||V$Session.MODULE x
,       Lpad( 'MODULE_HASH', 24, ' ')||' : '||V$Session.MODULE_HASH x
,       Lpad( 'ACTION', 24, ' ')||' : '||V$Session.ACTION x
,       Lpad( 'ACTION_HASH', 24, ' ')||' : '||V$Session.ACTION_HASH x
,       Lpad( 'CLIENT_INFO', 24, ' ')||' : '||V$Session.CLIENT_INFO x
,       Lpad( 'FIXED_TABLE_SEQUENCE', 24, ' ')||' : '||V$Session.FIXED_TABLE_SEQUENCE x
,       Lpad( 'ROW_WAIT_OBJ#', 24, ' ')||' : '||V$Session.ROW_WAIT_OBJ# x
,       Lpad( 'ROW_WAIT_FILE#', 24, ' ')||' : '||V$Session.ROW_WAIT_FILE# x
,       Lpad( 'ROW_WAIT_BLOCK#', 24, ' ')||' : '||V$Session.ROW_WAIT_BLOCK# x
,       Lpad( 'ROW_WAIT_ROW#', 24, ' ')||' : '||V$Session.ROW_WAIT_ROW# x
,       Lpad( 'LOGON_TIME', 24, ' ')||' : '||To_Char( V$Session.LOGON_TIME, 'DD Mon YYYY hh24:mi:ss') x
,       Lpad( 'LAST_CALL_ET', 24, ' ')||' : '||V$Session.LAST_CALL_ET x
,       Lpad( 'SQL FIRST_LOAD_TIME', 24, ' ')||' : '||V$SqlArea.FIRST_LOAD_TIME x
,       Lpad( 'SQL', 24, ' ')||' : '||V$SqlArea.Sql_Text x
From
        V$SqlArea
,       V$Process
,       V$Session
Where
        V$Process.Addr          = V$Session.Paddr(+)
And     Nvl( V$Session.Sql_Address, 'FFFFFFFF') = V$SqlArea.Address(+)
And     Nvl( V$Session.SERIAL#, 0)              > 1
/


SPOOL OFF
TTITLE OFF

SET PAGES 24 LINESIZE 80 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

EDIT all_session.log            


Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

The key is joining based upon the hash values.  For example:

v$session.sql_hash_value  and  v$sqlarea.hash_value

Bert.

None of the above worked: Remember it is to list all statements: For example

sqlplus scott/tiger     (let's say the sid=31 and serial=148)

> select 'sample1' from dual;
> select 'sample2' from dual;
> select 'sampel3' from dual;


What select from v$sqlarea,v$session, .etc will give me

"ALL" the statement issued by scott/tige.

Now, I understand thanks for explaining.  If you issue the three commands you provided:

select 'sample1' from dual;
select 'sample2' from dual;
select 'sample3' from dual;

Then issue:

select * from v$sqlarea where sql_text like '%sample__ from dual%';

or

select * from v$sql where sql_text like '%sample__ from dual%';


you'll notice there are only a few columns that match between all three sql statements.  For V$SQLAREA, if you look closer still, you'' notice all these columns match most (if not all) other SQL statements (not just these three).  For V$SQL there is one matching column unique for all three statements but it does NOT relate to the username.  It relates to the plan.  This means there are no columns common to those statements issued by a single user.

On the other hand, you could do what you want by creating a trigger to record the sid, serial#, username, hash_value, and address when the hash_value changes.  You could even capture the SQL_TEXT. This would give you a record of all SQL issues and the values you wish to save.

You could even save the MAC Address to keep a record of the host from which the SQL originated.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

How is the above suggestion working for you?

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept ypwitkow's comment as answer.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer