How to check yesterdays active session on oracle

How to check yesterdays active session on oracle?
LVL 4
pinkurayAsked:
Who is Participating?
 
MrNedConnect With a Mentor Commented:
Try this, I don't have a database handy right now to test it on but it should work with modified dates.


SELECT *
FROM v$active_session_history ash, v$sqltext sql
where 1=1
and ash.sample_time > '01/06/2009 20:40:00'
and ash.sample_time < '02/06/2009 04:50:00'
and ash.sql_id = sql.sql_id(+)
and ash.inst_id = sql.inst_id(+)
and sql.piece(+) = 0

Note that you are not "allowed" to query this view if you have not purchased the Diagnostics Pack.
0
 
slightwv (䄆 Netminder) Commented:
Define 'active session'?

You can see who 'connected' from the listener.log.

To track who did what, you need auditing.  Check the docs for specifics.
0
 
MrNedCommented:
You can check the ASH tables/views assuming you have purchased the appropriate packs from Oracle. By default the data in them last about 7 days.

e.g.: v$active_session_history
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
pinkurayAuthor Commented:
I want to know what are the SQL queries that were active yesterday from 5 PM to 7 PM.
0
 
TIGERMARKCommented:
SELECT s.sid, s.serial#
FROM v$session s
WHERE s.saddr in
(SELECT t.ses_addr
FROM V$transaction t, dba_rollback_segs r
WHERE t.xidusn=r.segment_id
AND r.tablespace_name='RBS');
0
 
pinkurayAuthor Commented:
Can any one tell me if my query is correct or not?

SELECT sql_text,
  application_wait_time
FROM gv$sql
WHERE sql_id IN
  (SELECT SQL_ID
  FROM GV$ACTIVE_SESSION_HISTORY
  WHERE TO_CHAR(SAMPLE_TIME,'DD-MON-YY HH24:MI:SS AM') BETWEEN '24-FEB-11 08:00:00' AND '24-FEB-11 10:00:00'
  );

0
 
MrNedCommented:
You are using the GV$ views so need to join on INST_ID column too (unless you only have a single instance).

Otherwise should work. APPLICATION_WAIT_TIME probably isn't what you expect as the GV$SQL view shows cumulative stats for the query not individual executions.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if you need to see the active sessions of yesterday then you can this simple query right ?

select * from v$session
where status ='ACTIVE'
and trunc(logon_time) = trunc(sysdate-1)

But if you need this "I want to know what are the SQL queries that were active yesterday from 5 PM to 7 PM. ", the best thing is to check the AWR reports for the given time slot and it will have all sql statements which got executed in that time slot.

Thanks
0
 
pinkurayAuthor Commented:
@nav_kum_v:

can you please let me know how to check in AWR report
0
 
pinkurayAuthor Commented:
@MrNed:

v$sqltext dont have inst_id column

So the below query don't work for me :



SELECT *
FROM v$active_session_history ash, v$sqltext s
where 1=1
and TO_CHAR(ASH.SAMPLE_TIME,'DD-MON-YY HH:MI:SS AM') > '24-FEB-11 08:00:00'
and TO_CHAR(ASH.SAMPLE_TIME,'DD-MON-YY HH:MI:SS AM') < '24-FEB-11 10:00:00'
and ASH.SQL_ID = S.SQL_ID(+)
and ash.inst_id = s.inst_id(+)  ---- v$sqltext dont have inst_id column
and S.piece(+) = 0;

Is any thing missing in this above so that to have a relationship with instance ID ?

0
 
MrNedCommented:
Sorry I copied and pasted that from one of my RAC scripts, change V$ to GV$ or remove the INST_ID join.
0
 
pinkurayAuthor Commented:
@MrNed:

So the code should be like this:
SELECT *
FROM v$active_session_history ash,
  v$sqltext s
WHERE 1                                              =1
AND TO_CHAR(ASH.SAMPLE_TIME,'DD-MON-YY HH:MI:SS AM') > '24-FEB-11 08:00:00'
AND TO_CHAR(ASH.SAMPLE_TIME,'DD-MON-YY HH:MI:SS AM') < '24-FEB-11 10:00:00'
AND ASH.SQL_ID                                       = S.SQL_ID(+)
AND S.piece(+)                                       = 0;

Open in new window

0
 
MrNedCommented:
Yes. Remove the last s.piece join if you want full SQL text, that just shows the first line.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Get to your DBA to give you the AWR report for that time slot and it will have all the sql statements executed in order ( by elapsed time, by buffer gets etc.. )
0
 
pinkurayAuthor Commented:
Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.