All Scripts

===================================== long running queres =========

COLUMN percent FORMAT 999.99

SELECT sid, to_char(start_time,'hh24:mi:ss') stime, message,( sofar/totalwork)*100 percent FROM v$session_longops

WHERE sofar <> totalwork;

select object_name, object_type, session_id,type,lmode,request,block,ctime from v$locked_object, all_objects, v$lock where v$locked_object.object_id = all_objects.object_id AND v$lock.id1 = all_objects.object_id AND v$lock.sid = v$locked_object.session_id order by session_id, ctime desc, object_name ;

SELECT username,sid,TO_CHAR(start_time,'HH24:MI:SS') AS logon,

TO_CHAR(CURRENT_TIMESTAMP,'HH24:MI:SS') AS curr,(sysdate - start_time)*24*60 AS mins

FROM V$SESSION_LONGOPS WHERE username is not NULL

AND (SYSDATE - start_time)*24*60 > 1 order by 5;

==== show Users which logged on between specific times and their sql =====

set linesize 150

column username format a12

column program format a15

col sql_text for a70

break on username

select substr(b.username,1,10) "username",b.sid from V$SQLTEXT a,V$SESSION b where a.address=b.sql_address

and b.username is not null and b.status = 'ACTIVE'

and to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') between '21-OCT-2011 09:59:30'

and '21-OCT-2011 13:38:48' order by piece;

set linesize 150

column username format a12

column program format a15

col sql_text for a70

break on username

select b.sid,a.sql_text from V$SQLTEXT a,V$SESSION b where a.address=b.sql_address

and b.username is not null and b.status = 'ACTIVE'

and to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') between '21-OCT-2011 12:59:30'

and '21-OCT-2011 14:38:48' AND b.EVENT LIKE '%&event%' order by piece;