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.

Primary:

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

group by Thread#;

Secondary:

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;

 

  9i

  SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

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

                UNION

                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

www.myriad-it.com