Improve company productivity with a Business Account.Sign Up

x
?
Solved

Find Active processes/connections in Oracle Database

Posted on 2014-04-30
5
Medium Priority
?
1,381 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
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 79

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 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 1600 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

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

584 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