?
Solved

How to tie V$session and V$sqlarea

Posted on 2003-03-14
8
Medium Priority
?
3,879 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
7 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month5 days, 16 hours left to enroll

588 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