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’

2 thoughts on “How to find SQL running for a user/sid?

  1. Pingback: Session information « Oracle Spin

  2. Pingback: Oracle DBA Daily Experiences » Blog Archive » Session information (wait, io)

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