Solved

Find Active processes/connections in Oracle Database

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
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

809 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