Helpful Oracle hints I've found and saved

How To See What Queries Are Running

Let's say you're running a long job, or somebody else is running a long job, (for example, a MASSIVE import into the database) and you'd like to see what the query is that's running.

Well, you could use all the pretty little tools in Oracle Enterprise Manager (OEM), or, if you're at the command line already, you could use SQL*Plus.

Here's a nice "little" query that shows what queries are running against your Oracle Database:

set linesize 300
set pagesize 0
set feedback on
set timing off

prompt  Display the active queries.
break on TimeInSecs on sid on username skip 1
column sid format 99999
column username format a10 trunc
column p.spid format 99999
select LAST_CALL_ET as TimeInSecs,
	s.sid,
	s.serial#,
	s.username,
	p.spid,
	t.sql_text curr_sql
from v$session s,
	v$sqltext t,
	v$process p
where (s.sql_address = t.address (+) and s.sql_hash_value = t.hash_value (+))
and status = 'ACTIVE' and s.username is not null
and s.paddr = p.addr
order by TimeInSecs, s.username, s.sid, p.spid, t.hash_value, piece

I can't actually remember where I got this. I believe from a colleague…

Thanks Colleague šŸ™‚

Leave a Reply

Powered by WordPress | Designed by Elegant Themes

Pin It on Pinterest

Shares
Share This