Loading....

Script – Check RMAN Backup Status

Scripts to check backup status and timings of database backups – This script will be run in the database, not the catalog. Login as sysdba – This script will report on all backups – full, incremental and archivelog backups – col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time, […]

Script – Sessions with high physical reads

set linesize 120 col os_user format a10 col username format a15 col pid format 9999999999 PROMPT SESSIONS SORTED BY PHYSICAL READS PROMPT select OSUSER os_user,username, PROCESS pid, ses.SID sid, SERIAL#, PHYSICAL_READS, BLOCK_CHANGES from v$session ses, v$sess_io sio where ses.SID = sio.SID and username is not null and status=’ACTIVE’ order by PHYSICAL_READS; Please follow and like […]

Script – Monitor Flashback Logs

PROMPT How Far Back Can We Flashback To (Time)? PROMPT select to_char(oldest_flashback_time,’dd-mon-yyyy hh24:mi:ss’) “Oldest Flashback Time” from v$flashback_database_log; PROMPT PROMPT How Far Back Can We Flashback To (SCN)? PROMPT col oldest_flashback_scn format 99999999999999999999999999 select oldest_flashback_scn from v$flashback_database_log; PROMPT PROMPT Flashback Area Usage SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; PROMPT col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999 […]

Script – Database structure and file location

set pagesize 500 set linesize 130 Prompt Prompt Control Files Location >>>> col name format a60 heading “Control Files” select name from sys.v_$controlfile / Prompt Prompt Redo Log File Locations >>>> Prompt col Grp format 9999 col member format a50 heading “Online REDO Logs” col File# format 9999 col name format a50 heading “Online REDO […]

Script – Tablespace free space and fragmentation

set linesize 150 column tablespace_name format a20 heading ‘Tablespace’ column sumb format 999,999,999 column extents format 9999 column bytes format 999,999,999,999 column largest format 999,999,999,999 column Tot_Size format 999,999 Heading ‘Total| Size(Mb)’ column Tot_Free format 999,999,999 heading ‘Total Free(MB)’ column Pct_Free format 999.99 heading ‘% Free’ column Chunks_Free format 9999 heading ‘No Of Ext.’ column […]

Script – Top SQL (Physical Reads)

This script will list the top 5 SQL statements sorted by the most number of physical reads set serverout on size 1000000 set feedback off declare top5 number; text1 varchar2(4000); x number; len1 number; Cursor c1 is select disk_reads,substr(sql_text,1,4000) from v$sqlarea order by disk_reads desc; begin dbms_output.put_line(‘Reads’||’ ‘||’ Text’); dbms_output.put_line (‘—–‘||’ ‘||’—————————————————-‘); dbms_output.put_line(‘ ‘); open […]

Script – Top SQL (Buffer Gets)

This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads set serverout on size 1000000 declare top5 number; text1 varchar2(4000); x number; len1 number; Cursor c1 is select buffer_gets,substr(sql_text,1,4000) from v$sqlarea order by buffer_gets desc; begin dbms_output.put_line(‘Reads’||’ ‘||’ Text’); dbms_output.put_line (‘—–‘||’ ‘||’—————————————————‘); dbms_output.put_line(‘ ‘); open […]

Script – Segments with highest I/O activity

This script will list the top 10 segments in the database that have the most number of physical reads against them. Script can also be changed to query on ‘physical writes’ instead. set pagesize 200 setlinesize 120 col segment_name format a20 col owner format a10 select segment_name,object_type,total_physical_reads from ( select owner||’.’||object_name as segment_name,object_type, value as […]

Script – List status of all submitted DBMS jobs

set pagesize 100 set linesize 120 ttitle – center ‘Submitted DBMS Jobs’ skip 2 col job format 99999 heading ‘job#’ col subu format a10 heading ‘Submitter’ trunc col lsd format a5 heading ‘Last|Ok|Date’ col lst format a5 heading ‘Last|Ok|Time’ col nrd format a5 heading ‘Next|Run|Date’ col nrt format a5 heading ‘Next|Run|Time’ col fail format 999 […]

Script – Query the RMAN catalog to list backup completion status

Note – run this query connected as the owner of the RMAN catalog set lines 80 set pages 250 ttitle “Daily Backup……..” select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),’DD/MM/YYYY HH24:MI’),’01/01/0001:00:00′) DBBKP, NVL(TO_CHAR(max(backuptype_arch),’DD/MM/YYYY HH24:MI’),’01/01/0001:00:00′) ARCBKP from ( select a.name DB,dbid, decode(b.bck_type,’D’,max(b.completion_time),’I’, max(b.completion_time)) BACKUPTYPE_db, decode(b.bck_type,’L’,max(b.completion_time)) BACKUPTYPE_arch from rc_database a,bs b where a.db_key=b.db_key and b.bck_type is not null and b.bs_key not in(Select […]

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?