DAILY CHECK SQL

TABLESPACE USEAGE:

set echo off feed off term off trims off
set colsep '|'
set trimspool on
set linesize 125
 
select dbid,name,open_mode,guard_status from v$database;
 
SELECT   tablespace_name, SUM (total_amount)||'M' total_size,
         (SUM (total_amount) - SUM (free_amount))||'M' used,
         ROUND ((((SUM (total_amount) - SUM (free_amount)) / SUM (total_amount))*100
                ),
                4
               ) usedpct
FROM (SELECT   a.file_id, a.tablespace_name,
                   SUM (a.BYTES) / 1024 / 1024 total_amount,
                   SUM (b.free) / 1024 / 1024 free_amount
              FROM dba_data_files a,
                   (SELECT   file_id, tablespace_name, SUM (BYTES) free
                        FROM dba_free_space
                    GROUP BY file_id, tablespace_name) b
             WHERE a.file_id = b.file_id
               AND a.tablespace_name = b.tablespace_name
               AND a.status = 'AVAILABLE'
          GROUP BY a.file_id, a.tablespace_name, b.free
          ORDER BY 2, 1)
GROUP BY tablespace_name
ORDER BY usedpct DESC;
 

RMAN SYNC STATUS:
SELECT   DECODE (SUBSTR (handle, INSTR (handle, '/', -1) + 1, 1),
                 'A', 'ARCHIVE LOG BACKUP',
                 'F', 'FULL BACKUP'
                ) CATEGORY,
        's'|| MAX (set_count) sequence#,
        MAX(TO_CHAR (completion_time, 'yyyy-mm-dd hh24:mi:ss')) completion_time
    FROM v$backup_piece
GROUP BY SUBSTR (handle, INSTR (handle, '/', -1) + 1, 1);
 

PHYUSICAL STDBY SYNC STATUS:
SELECT   DECODE (dest_id,
                 '1', 'PRIMARY DATABASE',
                 '2', 'STANDBY DATABASE'
                ) "ARCHIVE LOG",
         MAX (sequence#) "SEQUENCE#"
    FROM v$archived_log
   WHERE dest_id = '1'
GROUP BY dest_id
UNION ALL
SELECT   DECODE (dest_id,
                 '1', 'PRIMARY DATABASE',
                 '2', 'STANDBY DATABASE'
                ) "ARCHIVE LOG",
         MAX (sequence#) "SEQUENCE#"
    FROM v$archived_log
   WHERE dest_id = '2' AND applied = 'YES'
GROUP BY dest_id;