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 |
Table of Contents
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;
page revision: 5, last edited: 26 Oct 2017 15:15