Administration - Session

Show all connected users

set lines 100 pages 999
col ID format a15
select username
,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/ 

Time since last user activity

set lines 100 pages 999
select username , floor(last_call_et / 60) "Minutes" , status from v$session where username is not null order by last_call_et
/

Sessions sorted by logon time

set lines 100 pages 999
col ID  format a15
col osuser format a15
col login_time format a14
select  username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/ 

Show user info including os pid

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL" , s.username , s.osuser , p.spid "OS PID" , s.program from v$session s , v$process p Where s.paddr = p.addr order by to_number(p.spid)
/

Show a users current sql

Select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value 
        from v$session
        where username like '&username')
/ 

Session status associated with the specified os process id

select s.username , s.sid , s.serial# , p.spid , last_call_et , status from V$SESSION s , V$PROCESS p where s.PADDR = p.ADDR and p.spid='&pid'
/

All active sql

set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
  for x in
   (select username||'('||sid||','||serial#||') ospid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value
   from v$session
   where status = 'ACTIVE'
   and rawtohex(sql_address) <> '00'
   and username is not null ) loop
   for y in (select sql_text
   from v$sqlarea
   where address = x.sql_address ) loop
   if ( y.sql_text not like '%listener.get_cmd%' and
    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   end if;
  end loop;
 end loop;
end;
/
 

Display any long operations

set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/ 

List open cursors per user

set pages 999
select     sess.username
,    sess.sid
,    sess.serial#
,    stat.value cursors
from    v$sesstat stat
,    v$statname sn
,    v$session sess
where    sess.username is not null
and    sess.sid = stat.sid
and    stat.statistic# = sn.statistic#
and    sn.name = 'opened cursors current'
order by value
/

Administration - Status

Startup time

select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance
/

How large is 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
/

Distribution of objects and data

Which schemas are taking up all of the space

set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from  (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
 from dba_segments group by owner) seg
where  obj.owner  = seg.owner(+)
order by 3 desc ,2 desc, 1
/ 

Show the ten largest objects in the database

col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner , segment_name , segment_type , mb from ( select owner , segment_name , segment_type , bytes / 1024 / 1024 "MB" from dba_segments order by bytes desc ) where rownum < 11
/

Display character set information

select * from nls_database_parameters
/ 

Show all used features

select name , detected_usages from dba_feature_usage_statistics where detected_usages > 0 /