SQL script to check available space in your recovery area

SQL script to check available space in your recovery area (db_recovery_file_dest_size)

col name for a32
col size_m for 999,999,999
col reclaimable_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999

SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used / 1024 / 1024) USED_M
, ceil( space_reclaimable / 1024 / 1024) RECLAIMABLE_M
, decode( nvl( space_used, 0),
0, 0
, ceil ( ( ( space_used - space_reclaimable ) / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest
ORDER BY name
/

or


col name for a32
col size_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999
SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used / 1024 / 1024) USED_M
, decode( nvl( space_used, 0),
0, 0
, ceil ( ( space_used / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest
ORDER BY name
/

SQL Net* break message

SQL Net* break message

SELECT S.LOGON_TIME, --s.LAST_CALL_ET "idel in sec", floor(s.LAST_CALL_ET/86400) || 'd ' || to_char(to_date(mod  (s.LAST_CALL_ET,86400) ,'sssss'),'hh24"h" mi"m" ss"s"') "Idle Time", S.username,S.sid,S.serial#,S.status,S.program,S.module,  s.machine, s.event, 'alter system kill session ' ||''''||s.sid||','||s.serial#||''''||'  IMMEDIATE;' Kill_Stmt --,'alter system kill session ' ||''''||s.sid||','||s.serial#||',@'||inst_id||''''||'  IMMEDIATE;' Kill_Stmt FROM gV$SESSION SWHERE 1=1 --  and S.logon_time  <sysdate  --   and s.type='USER' and s.status<> 'ACTIVE' --and s.LAST_CALL_ET>86400/48 --and s.event='SQL*Net break/reset to client' --and( s.machine = 'anb-srl-ogcoms.anb,net' or  s.terminal ='anb-srl-ogcoms.anb.net') ORDER BY 2 desc;

ASM Diskgroup usage

ASM Diskgroup usage

select dg.name dg_name, round(sum(d.total_mb)/1024,0) total_gb, round(sum(d.free_mb)/1024,0) free_gb from v$asm_diskgroup dg, v$asm_disk d where dg.group_number=d.group_number and dg.name like '%MQM_DG%' group by dg.name order by dg_name;


select dg.name dg_name, round(sum(d.total_mb)/1024,0) total_gb, round(sum(d.free_mb)/1024,0) free_gb from v$asm_diskgroup dg, v$asm_disk d where dg.group_number=d.group_number group by dg.name order by dg_name;

RMAN BACKUP STATUS REPORT

RMAN BACKUP STATUS REPORT

set linesize 1000

set pagesize 1000

select I.instance_name,R.SESSION_KEY, R.INPUT_TYPE, R.STATUS, R.start_time,R.end_time,"Minutes" 

from (

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'dd/mm/yyyy hh24:mi') start_time,

to_char(END_TIME,'dd/mm/yyyy hh24:mi') end_time,

round((end_time-start_time)*1440,2) "Minutes"

from V$RMAN_BACKUP_JOB_DETAILS

where to_char(start_time,'yyyymmdd') between '20230101' and '20231231' --and input_type='DB INCR'

order by session_key desc

) R, v$instance I 

where rownum < 10;