longer than 5 minutes active running queries.

Hi colleagues:

What should I do in order to find those sessions running for more than 5 minutes ?

I need to trap these and kill them.

Thanks in advance, SIDCAP.
Who is Participating?
jammalkConnect With a Mentor Commented:
OK...! You mentioned in your query to kill those session running for more than 5 minutes..! If u want sesisons that are running transactions for more than 5 minutes.. , then...follow the following procedure..!

If you want to find out the sessions that have long running transactions (in your case more than 5 min).., use the view V$SESSION_LONGOPS.

Column  Datatype  Description  
SID     NUMBER    Session identifier
SERIAL# NUMBER    Session serial number
OPNAME  VARCHAR2(64) Brief description of the operation
TARGET  VARCHAR2(64) The object on which the operation is carried out
TARGET_DESC  VARCHAR2(32)  Description of the target
SOFAR    NUMBER  The units of work done so far
TOTALWORK  NUMBER   The total units of work
UNITS  VARCHAR2(32)  The units of measurement
START_TIME  DATE  The starting time of operation
LAST_UPDATE_TIME  DATE  Time when statistics last updated
TIME_REMAINING  NUMBER  Estimate (in seconds) of time remaining for the operation to complete.
ELAPSED_SECONDS   NUMBER  The number of elapsed seconds from the start of operations
MESSAGE  VARCHAR2(512)  Statistics summary message
USERNAME  VARCHAR2(30)  User ID of the user performing the operation.
SQL_ADDRESS  RAW(4) Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation.
SQL_HASH_VALUE  NUMER  Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation.
QCSID  NUMBER  Session identifier of the parallel coordinator.
I think you can use the columns, SID, SERIAL#, START_TIME to get the desired result...!

But, to make use of these view, there are some restrictions:
1. Must be using Cost-based optimizer.
2. Set the parameters, TIMED_STATISTICS or SQL_TRACE parameter to TRUE.
3. Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package.

This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution (this is what u need) , and more operations are added for every Oracle release.

You can modify the previously mentioned query with this V$SESSION_LONGOPS and get the results..u want..!
sidcapAuthor Commented:
I need to log the killed sqls too.
Two options:

1)  set up profiles and grant them to the user:
Profiles are a set of limits on database resources.
Some of the resource parameters are:
Specify the number of concurrent sessions to which you want to limit the user.
Specify the CPU time limit for a session, expressed in hundredth of seconds.
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.  
Specify the total elapsed time limit for a session, expressed in minutes.  
Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.  
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

Another option to look into would be using
Database Resource Manager  (new in oracle 8i)
It is described in the Oracle8i Administrator's Guide
Release 2 (8.1.6) Part Number A76956-01
chapter 25:  The Database Resource Manager

Here is a clip:
What is the Database Resource Manager?
Typically, when database resource allocation decisions are left to the operating system, you may encounter the following problems:
*  Excessive overhead
*  Excessive overhead results from operating system context switching between Oracle servers when the number of servers is high.
*  Inefficient scheduling
*  The operating system de-schedules Oracle servers while they hold latches, which is inefficient.
*  Poor allocation of resources
*  The operating system fails to allocate CPU resources appropriately among tasks of varying importance.
*  Inability to manage database-specific resources, such as parallel slaves and active sessions

Oracle's Database Resource Manager helps to overcome these problems by allowing the database more control over how machine resources are allocated.

Specifically, using the Database Resource Manager, you can:
* Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users.
* Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP applications than to batch jobs.
* Limit the degree of parallelism that a set of users can use.
* Configure an instance to use a particular method of allocating resources. A DBA can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.
* The elements of Oracle's database resource management, which you define through the Database Resource

Hope these suggestions help!



Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Spool the output of the following query & run it with ALTER SYSTEM privileges.
select 'alter system kill session kill session ' || '''' ||
 sid || ',' || serial# || '''' || ';'
 from v$session
where (sysdate - logon_time)*24*60 > 5 ;
If you would like to see the sessions that are running for more than 5 minutes, use the following query:

select sid, serial#, to_char(sysdate,'HH24:MI:SS') "CURRENT",
to_char(logon_time,'HH24:MI:SS') "LOGON",
(sysdate - logon_time)*24*60 from v$session
 where (sysdate - logon_time)*24*60 > 5 ;

Oops  I repeated KILL SESSION twice... in the above query.. ! Just type only once..!
sidcapAuthor Commented:
Thanks jammalk, but v$session.logon_time does not tell me that there is a query actually running.

a session may change from INACTIVE to ACTIVE and then INACTIVE again, but logon_time never changes.

So, (sysdate - logon_time)*24*60 > 5 does not necessary imply that there is actually a 5 minutes running query.

thanks anyway, sidcap.
v$session_Longops does the trick alright

Now for ur second question
if u want to record the Killed sql queries

use V$Open_cursor

it contains following columns
against killed session take sql_text and logit
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.

All Courses

From novice to tech pro — start learning today.