Solved

How to check yesterdays active session on oracle

Posted on 2011-02-23
15
763 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
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 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
 
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

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 …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

790 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