?
Solved

How to tie V$session and V$sqlarea

Posted on 2003-03-14
8
Medium Priority
?
3,679 Views
Last Modified: 2013-12-11
I logon with sqlplus or a third party tools and issue several sql statements. During that session let say my sid=34 and serial#=134. Now I logon as Sys thru sqlplus and want to find all the sql statement in the sga issued by sid=34 serial#134. What query will give me all the statement issued by a session if I had the sid and serial#.
0
Comment
Question by:MrCrawfish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 4

Expert Comment

by:bmoshier
ID: 8139968
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





0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8139992
The key is joining based upon the hash values.  For example:

v$session.sql_hash_value  and  v$sqlarea.hash_value

Bert.
0
 

Author Comment

by:MrCrawfish
ID: 8147992
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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 4

Expert Comment

by:bmoshier
ID: 8148774
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
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8169116
How is the above suggestion working for you?
0
 
LVL 2

Accepted Solution

by:
ypwitkow earned 160 total points
ID: 8216884
You cannot do it that way.
In v$session there is an address of the last issued SQL only.
You can query v$sql, v$sqlarea but for all statements of the user.
If you want to trace SQL issued by session, use package
procedure
 dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);

Best regards,
Lucy
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10216327
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question