Home > Oracle > How to find SQL running for a user/sid?

How to find SQL running for a user/sid?

Using the SQL below in which joining with V$session and V$sqlarea one can find the SQL currently running.

In this example using a session id one can find the SQL running


SQL> select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.sid = 257;

SID SERIAL#
———- ———-
SQL_TEXT
——————————————————————————–
257 8885
ALTER TABLE SCOTT.TEST_TBL1 MOVE PARTITION PART1 TABLESPACE NEW_TBS COMPRESS PCTFREE 0

In the following example, one can list all SQLs currently running by user SCOTT.
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username = ‘SCOTT’;

Advertisements
Categories: Oracle
  1. No comments yet.
  1. May 4, 2012 at 5:25 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: