Solved

Find Active processes/connections in Oracle Database

Posted on 2014-04-30
5
1,187 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 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

821 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