Solved

How to check yesterdays active session on oracle

Posted on 2011-02-23
15
750 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)
ID: 34967014
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
ID: 34967037
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
ID: 34967045
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
ID: 34967074
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
ID: 34967077
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
ID: 34967121
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
ID: 34967143
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
ID: 34967176
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
ID: 34967206
@nav_kum_v:

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

Author Comment

by:pinkuray
ID: 34967223
@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
ID: 34967260
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
ID: 34967273
@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
ID: 34967320
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
ID: 34967329
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
ID: 34967612
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.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

932 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

13 Experts available now in Live!

Get 1:1 Help Now