Database size script

Here is a nice little script I like to use for showing the database size 
for various components.
#!/bin/ksh ############################################################################ 
# Script Name..: db_overall_size.ksh 
# Description..: 
# Author.......:
# Date.........: 04/10/2013 
# Version......: 
# Modified By..: 
# Date Modified: 
# Comments.....: 
# Schema owner.: 
# Login User...: 
# Run Order....: 
# Dependent on.: 
# Script type..: 
# ############################################################################
sqlplus -s "/ as sysdba" <<EOF 
set lines 200 
set pages 200
spool db_size.txt set feed off 
column systot   format 99,990.99 heading "System |Alloc|GB" 
column sysused  format 99,990.99 heading "System |Used|GB" 
column systm    format 99,990.99 heading "System Tbsp|MB" 
column sysax    format 99,990.99 heading "Sys Aux|GB" 
column systmusd format 99,990.99 
column sysused  format 99,990.99 
column undotb   format 99,990.99 heading "Undo|GB" 
column datatot  format 99,990.99 heading "Data|Alloc|GB" 
column dataused format 99,990.99 heading "Data|Used|GB" 
column tmptot   format 99,990.99 heading "TEMP|Alloc|GB" 
column redotot  format 99,990.99 heading "Online|Redo|MB" 
column ctltot   format 99,990.99 heading "Ctrl File|MB" 
column systall  format 99,990.99 column systused format 99,990.99 
column fratot   format 99,990.99 column fraused  format 99,990.99 
column total_gb format 99,990.99 column free_gb  format 99,990.99 

select name from v\$database;
select s.system_size            SYSTOT,        
       s.system_size-f1.free_mb SYSUSED,        
       st.system_size           SYSTM,        
       sa.system_size           SYSAX,        
       ud.system_size           UNDOTB,        
       d.data_size              DATATOT,        
       d.data_size-f2.free_mb   DATAUSED,        
       t.temp_size              TMPTOT,        
       r.redo_size              REDOTOT,        
       c.controlfile_size       CTLTOT,        
       s.system_size+t.temp_size+r.redo_size+c.controlfile_size            SYSTALL,
       s.system_size-f1.free_mb+t.temp_size+r.redo_size+c.controlfile_size SYSTUSED,        
       fra.fratot               FRATOT,        
       fra.fraused              FRAUSED   
       from       (select sum(bytes)/1024/1024/1024 system_size               
                     from dba_data_files         
                    where tablespace_name like 'SYSTEM' or               
                          tablespace_name like 'SYSAUX' or               
                          tablespace_name like 'UNDO%'  or               
                          tablespace_name like 'TOOLS'  or                
                          tablespace_name like 'USERS') s,       
                  (select sum(bytes)/1024/1024/1024 data_size          
                     from dba_data_files         
                    where tablespace_name not like 'SYSTEM' and               
                          tablespace_name not like 'SYSAUX' and               
                          tablespace_name not like 'UNDO%'  and               
                          tablespace_name not like 'TOOLS'  and               
                          tablespace_name not like 'USERS') d,       
                  (select sum(bytes)/1024/1024 system_size          
                     from dba_data_files         
                    where tablespace_name like 'SYSTEM' ) st,       
                  (select sum(bytes)/1024/1024/1024 system_size          
                     from dba_data_files         
                    where tablespace_name like 'SYSAUX' ) sa,       
                  (select sum(bytes)/1024/1024/1024 system_size          
                    from dba_data_files         
                   where tablespace_name like 'UNDO%' ) ud,       
                  (select nvl(sum(bytes),0)/1024/1024/1024 temp_size          
                     from dba_temp_files) t,       
                  (select sum(bytes)/1024/1024 redo_size          
                     from sys.v\$log) r,       
                  (select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size        
                     from v\$controlfile) c,       
                  (select sum(bytes)/1024/1024/1024 FREE_MB          
                     from dba_free_space         
                    where tablespace_name like 'SYSTEM' or               
                          tablespace_name like 'SYSAUX' and               
                          tablespace_name like 'UNDO%'  and               
                          tablespace_name like 'TOOLS'  and               
                          tablespace_name like 'USERS') f1,       
                  (select sum(bytes)/1024/1024/1024 FREE_MB          
                     from dba_free_space         
                    where tablespace_name not like 'SYSTEM' and              
                          tablespace_name not like 'SYSAUX' and              
                          tablespace_name not like 'UNDO%'  and              
                          tablespace_name not like 'TOOLS'  and               
                          tablespace_name not like 'USERS') f2,       
                  (select name, (SPACE_LIMIT)/1024/1024/1024 FRATOT, (space_used)/1024/1024/1024 FRAUSED          
                     from V\$RECOVERY_FILE_DEST) fra;
spool off 
EOF

Leave a Reply

Your email address will not be published. Required fields are marked *