Solved

How to check yesterdays active session on oracle

Posted on 2011-02-23
15
743 Views
Last Modified: 2012-05-11
How to check yesterdays active session on oracle?
0
Comment
Question by:pinkuray
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 7

Expert Comment

by:MrNed
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
I want to know what are the SQL queries that were active yesterday from 5 PM to 7 PM.
0
 
LVL 5

Expert Comment

by:TIGERMARK
Comment Utility
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
 
LVL 7

Accepted Solution

by:
MrNed earned 500 total points
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
 
LVL 7

Expert Comment

by:MrNed
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
@nav_kum_v:

can you please let me know how to check in AWR report
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
@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
 
LVL 7

Expert Comment

by:MrNed
Comment Utility
Sorry I copied and pasted that from one of my RAC scripts, change V$ to GV$ or remove the INST_ID join.
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
@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
 
LVL 7

Expert Comment

by:MrNed
Comment Utility
Yes. Remove the last s.piece join if you want full SQL text, that just shows the first line.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 4

Author Closing Comment

by:pinkuray
Comment Utility
Thanks for your help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now