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