/* run this script as a dba user on the db server, from the directory where this script is stored: set ORACLE_SID= sqlplus "/ as sysdba" @db_info.sql exit this will generate a logfile in the current directory */ set pagesize 9999 set linesize 150 set serveroutput on set trimspool on set echo off set feedback off spool &1 --------------------------------------------- DB ---------------------------------------------------------------------- column db_unique_name format a15 column platform_name format a20 column host_name format a30 column name format a10 column log_mode format a15 column force_logging format a3 heading FL column protection_mode format a25 column protection_level format a25 column flashback_on format a3 heading FO select name, log_mode, force_logging, protection_level, protection_mode, database_role from v$database; --------------------------------------------- DB PARAMETERS ------------------------------------------------------------ set linesize 180 set pagesize 9999 COLUMN display_value FORMAT a15 word_wrapped COLUMN value FORMAT a75 word_wrapped COLUMN name FORMAT a35 column type format 999 select x.inst_id inst_id,ksppinm name,ksppity type, ksppstvl value, ksppstdf isdefault from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ( ksppstdf = 'FALSE' and translate(ksppinm,'_','#') not like '##%' or (ksppstdf = 'FALSE' and translate(ksppinm,'_','#') like '#%' and translate(ksppinm,'_','#') not like '##%') ) order by x.inst_id, ksppinm; column property_value format a40 select property_name, property_value from database_properties order by property_name; --------------------------------------------- INSTALLED OPTIONS -------------------------------------------------------- column comp_name format a50 select comp_name, version, status from dba_registry order by comp_name; --------------------------------------------- DB SIZES ------------------------------------------------------------------ column name format a25 heading "tablespace name" column space_mb format 99g999g990D99 heading "curr df mbytes" column maxspace_mb format 99g999g990D99 heading "max df mbytes" column used format 99g999g990D99 heading "used mbytes" column df_free format 99g999g990D99 heading "curr df free mbytes" column maxdf_free format 99g999g990D99 heading "max df free mbytes" column pct_free format 990D99 heading "% free" column pct_maxfile_free format 990D99 heading "% maxfile free" break on report compute sum of space_mb on report compute sum of maxspace_mb on report compute sum of df_free on report compute sum of maxdf_free on report compute sum of used on report prompt prompt DB - Sizes prompt __________ select df.tablespace_name name, df.space space_mb, df.maxspace maxspace_mb, (df.space - nvl(fs.freespace,0)) used, nvl(fs.freespace,0) df_free, (nvl(fs.freespace,0) + df.maxspace - df.space) maxdf_free, 100 * (nvl(fs.freespace,0) / df.space) pct_free, 100 * ((nvl(fs.freespace,0) + df.maxspace - df.space) / df.maxspace) pct_maxfile_free from ( select tablespace_name, sum(bytes)/1024/1024 space, sum(greatest(maxbytes,bytes))/1024/1024 maxspace from dba_data_files group by tablespace_name ) df, ( select tablespace_name, sum(bytes)/1024/1024 freespace from dba_free_space group by tablespace_name ) fs where df.tablespace_name = fs.tablespace_name(+) order by name; clear breaks --------------------------------------------- TABLESPACE INFO -------------------------------------------------------------- prompt prompt tablespace info prompt _______________ column max_mb format 9G999G990D99 column curr_mb format 9G999G990D99 column free_mb format 9G999G990D99 column pct_free format 900D99 heading "%FREE" column NE format 999999D99 column SSM format a6 column AT format a8 column tablespace_name format a20 column EM format a10 column contents format a15 column block_size format 99999 heading bsize select A.tablespace_name, block_size, A.contents, extent_management EM, allocation_type AT, segment_space_management ssm, decode(allocation_type, 'UNIFORM',next_extent/1024,'') NE, B.max_mb, B.curr_mb, (B.max_mb - B.curr_mb) + nvl(c.free_mb,0) free_mb, ((100/B.max_mb)*(B.max_mb - B.curr_mb + nvl(c.free_mb,0))) pct_free from dba_tablespaces A, ( select tablespace_name, sum(bytes)/1024/1024 curr_mb, sum(greatest(bytes, maxbytes))/1024/1024 max_mb from dba_data_files group by tablespace_name union all select tablespace_name, sum(bytes)/1024/1024 curr_mb, sum(greatest(bytes, maxbytes))/1024/1024 max_mb from dba_temp_files group by tablespace_name ) B, ( select tablespace_name, sum(bytes)/1024/1024 free_mb from dba_free_space group by tablespace_name ) C where A.tablespace_name = B.tablespace_name and A.tablespace_name = C.tablespace_name(+) order by tablespace_name; --------------------------------------------- DF DETAILS ------------------------------------------------------------------ column curr_mb format 9G999G990D99 column max_mb format 9G9999990D99 column incr_mb format 9G999G990D99 column file_name format a70 --column file_name format a60 column tablespace_name format a20 break on tablespace_name skip 1 set linesize 150 set pagesize 999 prompt prompt datafiles info prompt ______________ select A.tablespace_name, file_id, file_name, bytes/1024/1024 curr_mb, autoextensible, maxbytes/1024/1024 max_mb, (increment_by * block_size)/1024/1024 incr_mb from ( select tablespace_name, file_id, file_name, bytes, autoextensible, maxbytes, increment_by from dba_data_files union all select tablespace_name, file_id, file_name, bytes, autoextensible, maxbytes, increment_by from dba_temp_files ) A, dba_tablespaces B where A.tablespace_name = B.tablespace_name order by A.tablespace_name, file_name; clear breaks; --------------------------------------------- ONLINE REDO INFO ---------------------------------------------------------------- column member format a65 column type format a10 column status format a15 column arch format a4 break on type on thread# nodup skip 1 on type nodup on GROUP# nodup prompt prompt online redo info prompt ________________ select type, A.thread#, A.group#, B.member, A.bytes/1024/1024 mb,A.status, arch from ( select group#, thread#, bytes, status, archived arch from v$log union all select group#, thread#, bytes, status, archived arch from v$standby_log ) A, v$logfile B where A.group# = B.group# order by type, A.thread#, A.group#, B.member; clear breaks --------------------------------------------- REDO SIZES ------------------------------------------------------------------ column day_arch# format 999G999 column graph format a15 column dayname format a12 column day format a12 prompt prompt redo sizes prompt __________ column start_day format a22 column end_day format a22 column days_between format 99 column avg_archived_per_day format a13 heading avg_gen select to_char(min(dag), 'DD/MM/YYYY HH24:MI:SS') start_day, to_char(max(dag) + 1 - 1/(24*60*60), 'DD/MM/YYYY HH24:MI:SS') end_day, (max(dag) - min(dag) + 1) days_between, to_char(avg(gen_archived_size),'9G999G999D99') avg_archived_per_day from ( select trunc(completion_time) dag, sum(blocks * block_size)/1024/1024 gen_archived_size from v$archived_log where standby_dest = 'NO' and months_between(trunc(sysdate), trunc(completion_time)) <= 1 and completion_time < trunc(sysdate) group by trunc(completion_time) ); /* archived redo over the (max) last 10 days */ column day_arch_size format 99G999D99 column day_arch# format 999G999 column graph format a15 column dayname format a12 column day format a12 select to_char(day, 'DD/MM/YYYY') day, to_char(day,'DAY') dayname, day_arch_size, day_arch#, graph from ( select trunc(completion_time) day, sum(blocks * block_size)/1024/1024 day_arch_size, count(*) day_arch#, rpad('*',floor(count(*)/10),'*') graph from v$archived_log where standby_dest = 'NO' and completion_time >= trunc(sysdate) - 10 group by trunc(completion_time) order by day ); /* archived redo per hour over the (max) last 2 days */ column hour_arch_size format 99G999D99 column hour_arch# format 9G999 column graph format a15 column dayname format a12 column dayhour format a18 break on dayname skip 1 select to_char(dayhour,'DAY') dayname, to_char(dayhour, 'DD/MM/YYYY HH24:MI') dayhour, hour_arch_size, hour_arch#, graph from ( select trunc(completion_time, 'HH') dayhour, sum(blocks * block_size)/1024/1024 hour_arch_size, count(*) hour_arch#, rpad('*',floor(count(*)/4),'*') graph from v$archived_log where standby_dest = 'NO' and completion_time >= trunc(sysdate) - 2 group by trunc(completion_time, 'HH') order by dayhour ); clear breaks; spool off exit