Ora Monitor
main monitor ddl objects dml stato e configurazione rman & dpump pl/sql cluster ASM
main config start-shut process memory monitor diag
user rman & dpump pl/sql cluster ASM

Sessions and Processes

Sessions 1

set echo off
set time on
set timing on
set pages 200
set lines 200
set trims on
set wrap on
col sid            for 99999
col osuser         for a10
col username       for a14
col machine        for a10
col client_info    for a20
select TO_CHAR(v$s.logon_Time,'DD-MON-YYYY HH24:MI:SS') as logon_time,
       v$s.sid,
       v$s.serial#,
       v$s.osuser,
       v$s.username,
       v$p.spid,
       v$s.status,
       v$s.machine,
       v$s.program,
       v$s.event,
                   v$s.sql_id
  from v$session v$s, V$process v$p
where v$s.paddr=v$p.addr
   --and v$s.username like '%&utente%'
   --and v$s.sid= '&sid'
   --and v$p.spid IN ('','')
   --and v$s.machine LIKE '%&machine%'
   --and v$s.program LIKE '%rman%'
   --and v$s.status='ACTIVE'
   --and v$s.event like '%enq%'
   --and trunc(logon_time)<trunc(sysdate-1)
   --and v$s.service_name like '%&service%'
order by 1
/

Sizes

Tablespaces

set line 200
col tablespace_name for a30
set pages 200
break on report
compute sum of "TOTAL (MB)", "USED (MB)", "FREE (MB)" on report
select IV1.tablespace_name, trunc(a,0) "TOTAL (MB)", trunc(a-c,0) "USED (MB)", trunc(c,0) "FREE (MB)", trunc(c/a*100,0) PERCFREE
from (select tablespace_name, sum(bytes)/1048576 a from dba_data_files group by tablespace_name) IV1,
     (select tablespace_name, sum(bytes)/1048576 c from dba_free_space group by tablespace_name) IV2
where IV1.tablespace_name=IV2.tablespace_name (+)
order by 2 desc
/

UNDO TBS

SELECT m.tablespace_name, m.used_percent, (m.tablespace_size - m.used_space)*t.block_size/1024/1024 mb_free 
FROM dba_tablespace_usage_metrics m, dba_tablespaces t, v$parameter p 
WHERE p.name = 'statistics_level' 
and p.value != 'BASIC' AND t.contents = 'UNDO' AND t.tablespace_name = m.tablespace_name ;

Query Text by SQL_ID

select sql_text from v$sqltext where sql_id='byx03k6ah72x3' order by piece;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License