Solved

Find Active processes/connections in Oracle Database

Posted on 2014-04-30
5
1,240 Views
Last Modified: 2014-05-14
Hi,
I'd like to run a query to find out which processes and connections were active between 14:00 and 14:30 PM yesterday, and what jobs/queries were running at the same time.

Have you any idea?

Thanks in advance!
0
Comment
Question by:ralph_rea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40031824
You will get all the information you need by generating AWR report

this is present under $ORACLE_HOME/rdbms/admin/ with file name awrrpt.sql/awrrpti.sql

query the data dictionary and get the relevant snap ids for the specified time range
select * from DBA_HIST_snapshot

for more info on OWR and how to generate refer tim hall site

http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40031841
>>You will get all the information you need by generating AWR report

This assumes you are properly licensed for the Diagnostics Pack.  AWR isn't 'free'.

The only way I know to do this outside of what is mentioned is to turn on auditing.  If it isn't already turned on then I don't think you can get what you need.
0
 

Author Comment

by:ralph_rea
ID: 40031894
I do not think that AWR gives the number of connections/processes in a period of half an hour (minimum one hour), I think instead that it is necessary to use the DBA_HIST_ACTIVE_SESS_HISTORY Oracle view or ASH report.
I just wanted to know if there was a query with this output.
0
 
LVL 8

Expert Comment

by:Surrano
ID: 40031919
You can also find some info about login attempts in listener.log but no info about queries and jobs.
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 400 total points
ID: 40031961
-->I just wanted to know if there was a query with this output.

I don't think a query will hold output for such a long time, as steve suggested you can get this info, if you have auditing turned on(who all have logged in at that, what all did they did etc.,)..

with AWR you can get active session histories for 1 hour of that snap id, for half an hour am not sure, how will you get.. but it will give you a detailed analysis of that 1 hour slot..including the sql_id which has been performed and elapsed time of each activity etc.,

Example of my AWR shows like this.. number of sessions

      Snap Id      Snap Time      Sessions      Cursors/Session
Begin Snap:      170      29-Apr-14 16:00:39      71      5.2
End Snap:              171      29-Apr-14 17:00:05      60      6.6
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

724 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