Tuesday, 27 January 2015

More tips for DBAs

 More tips for DBAs!

Tip 1: Remove colour coding in Linux command output

ls -alt
\ls -alt

Tip 2: Quickly check the status of Data guard using following query.

select Thread#, max(sequence#) from v$log_history
group by Thread#;


select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  from (select thread# thrd, max(sequence#) almax
                   from v$archived_log
                   where resetlogs_change#=(select resetlogs_change# from v$database)
                   group by thread#) al,
                  (select thread# thrd, max(sequence#) lhmax
                   from v$log_history
                   where resetlogs_change#=(select resetlogs_change# from v$database)
                   group by thread#) lh
  where al.thrd = lh.thrd;

Tip 3: Script to check tablespace usage by considering auto growth.

SELECT a.tablespace_name,
       CASE tbs_auto.autoextensible
          WHEN 'YES'
             THEN 'YES'
          ELSE 'NO'
       END AS autoextensible,
       files.tbs_files files_in_tablespace,
       round(AUTOEXTEND.total_growth_tbs/1024/1024,2) max_size_MB,
       round(files.total_tbs_bytes/1024/1024,2) space_Allo_MB,
       round((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/1024/1024,2) used_space_MB,
       round((AUTOEXTEND.total_growth_tbs - (files.total_tbs_bytes - fragments.total_tbs_free_bytes))/1024/1024,2) free_MB,
       round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes) / AUTOEXTEND.total_growth_tbs ) * 100),2) used_pct,
       round(((AUTOEXTEND.total_growth_tbs - (files.total_tbs_bytes - fragments.total_tbs_free_bytes)) / AUTOEXTEND.total_growth_tbs ) * 100,2) free_pct
  FROM dba_tablespaces a,(SELECT   tablespace_name, COUNT (*) tbs_files,
               SUM (BYTES) total_tbs_bytes
          FROM dba_data_files
      GROUP BY tablespace_name) files,(SELECT   tablespace_name, COUNT (*) tbs_fragments,
               SUM (BYTES) total_tbs_free_bytes,
               MAX (BYTES) max_free_chunk_bytes
          FROM dba_free_space
      GROUP BY tablespace_name) fragments,(SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs
          FROM (SELECT   tablespace_name, SUM (maxbytes) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'YES'
                GROUP BY tablespace_name
                SELECT   tablespace_name, SUM (BYTES) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'NO'
                GROUP BY tablespace_name)
      GROUP BY tablespace_name) AUTOEXTEND,(SELECT DISTINCT tablespace_name, autoextensible
                 FROM dba_data_files
                WHERE autoextensible = 'YES') tbs_auto
WHERE a.tablespace_name = files.tablespace_name
   AND a.tablespace_name = fragments.tablespace_name
   AND a.tablespace_name = AUTOEXTEND.tablespace_name
   AND a.tablespace_name = tbs_auto.tablespace_name(+)
   order by used_pct desc;

Posted by Myriad IT  - 27th January 2015