Find active sessions in oracle database 

set echo off

set linesize 95

set head on

set feedback on

col sid head "Sid" form 9999 trunc

col serial# form 99999 trunc head "Ser#"

col username form a8 trunc

col osuser form a7 trunc

col machine form a20 trunc head "Client|Machine"

col program form a15 trunc head "Client|Program"

col login form a11

col "last call" form 9999999 trunc head "Last Call|In Secs"

col status form a6 trunc

select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,

substr(program||module,1,15) program,substr(machine,1,22) machine,

to_char(logon_time,'ddMon hh24:mi') login,

last_call_et "last call",status

from gv$session where status='ACTIVE'

order by 1

/ 

Find sessions generating lot of redo 

set lines 2000

set pages 1000

col sid for 99999

col name for a09

col username for a14

col PROGRAM for a21

col MODULE for a25

select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id

from v$sesstat s join v$statname n on n.statistic# = s.statistic#

join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by redo_mb desc; 

Get size of the database :

col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used, (select sum(bytes) as p

from dba_free_space) free group by free.p

/ 

Find blocking sessions 

SELECT

s.inst_id,

s.blocking_session,

s.sid,

s.serial#,

s.seconds_in_wait

FROM

gv$session s

WHERE

blocking_session IS NOT NULL; 

Find long running operations 

select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining

from gv$session_longops

where totalwork<>sofar

/ 

Kill all session of a user :

select 'alter system kill session ' ||''''||SID||','||SERIAL#||' immediate ;' from v$session
where sql_id='&sql_id';

FOR RAC

select 'alter system kill session ' ||''''||SID||','||SERIAL#||',@'||inst_id||''''||' immediate ;'
from gv$session where sql_id='&sql_id'

Top Query with high elapsed time (Queries in last 1 hour )

Select module,parsing_schema_name,inst_id,sql_id,CHILD_NUMBER,sql_plan_baseline,sql_profile,plan_hash_value,sql_fulltext,

to_char(last_active_time,'DD/MM/YY HH24:MI:SS' ),executions, elapsed_time/executions/1000/1000,

rows_processed,sql_plan_baseline from gv$sql where last_active_time>sysdate-1/24 

and executions <> 0 order by elapsed_time/executions desc