• Arun Kumar

Find Session Id Running Specific Query

At times DBAs need to find or search for session details that are running a specific query inside database. Example, you might want to find out session ID that is running ALTER TABLE command.


Note: This query will give details only if the query is still running inside the database.

SET LINES 300
SET PAGES 999
COL SID FOR 99999
COL SER# FOR 9999999
COL OS_ID FOR A5
COL STATUS FOR A8
COL SQL_FULLTEXT FOR A60
SELECT
   SES.SID,
   SES.SERIAL# SER#,
   SES.PROCESS OS_ID,
   SES.STATUS,
   SQL.SQL_FULLTEXT
FROM 
   V$SESSION SES,
   V$SQL SQL,
   V$PROCESS PRC
WHERE
   SES.SQL_ID=SQL.SQL_ID AND
   SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND 
   SES.PADDR=PRC.ADDR AND
   UPPER(SQL.SQL_FULLTEXT) LIKE UPPER('ALTER TABLE%SHRINK%');
   

You can change the last line to search for sessions that are running specific queries. Replace ALTER TABLE%SHRINK% with other command that you want to search.

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback