Solved

Find Active processes/connections in Oracle Database

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

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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now