Oracle Database Discussions

Important question is that why you want to know this? There would be many queries that you may have fired but there would MANY that oracle internally would have fired so what you are going to do by looking at such a massive info?

Aman....

Hi,

select sesion.sid,
sql_text
from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address
and sesion.username is not null
order by sqltext.piece;

Use this query to find the running query in all active sessions.

Thanks,
Ranjith

Hello,


The following query may give you the list of running SQL ( SQL_ID ) from Active Sessions:

select inst_id, program, module, SQL_ID, machine
from gv$session
where type!='BACKGROUND'
and status='ACTIVE' 
and sql_id is not null;

Then, from other Views like v$sqltext you may have the SQL statement for the SQL_ID you got with the above query.


Hope this help.
Best regards,
Jean-Valentin

>
How can find about the queries which are currently running in my database (11.1.0.7)

I was using v$session_longops, but I learnt that some of the activities are not recorded in this.

v$sql and v$sqlarea list the queries which are not running as well.
>

What version are you on? That's important to know before you can join V$session with v$sql. SQL_ID has come to v$session only from 10gR1, I think. Otherwise you may have to use sql_address.

Try this for 10g and above

select a.sid, a.username,b.sql_id, b.sql_fulltext from v$session a, v$sql b
where a.sql_id = b.sql_id and a.status = 'ACTIVE' and a.username != 'SYS';

Regards
Venkat

Edited by: VenkatB on Feb 28, 2012 9:31 AM

Hi,

V$SESSION_LONGOPS contains information about long operations (as the name suggests), not about all running SQL. An operation needs to take 6s or more to make it into V$SESSION_LONGOPS.

Just query V$SESSION, making sure that you only look at active sessions.

Best regards,
Nikolay