• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 844
  • Last Modified:

How to check yesterdays active session on oracle

How to check yesterdays active session on oracle?
0
pinkuray
Asked:
pinkuray
  • 6
  • 5
  • 2
  • +2
1 Solution
 
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
 
pinkurayAuthor Commented:
I want to know what are the SQL queries that were active yesterday from 5 PM to 7 PM.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
MrNedCommented:
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now