#!/bin/ksh

ORACLE_SID=ORA11DB
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
PATH=$PATH:$ORACLE_HOME/bin

sqlplus -S "system/passwd@ORADB as sysdba" << EOF

-- Database name and size
select name as Database, open_mode from v\$database;
set linesize 110
column dummy noprint
column  name    format a19      heading "Tablespace Name"
column  Mbytes   format 999,999,999    heading "Size(MB)"
column  used    format 999,999,999   heading "Used"
column  free    format 999,999,999  heading "Free"
column  pct_used format 999.9       heading "%|Used"
break   on report
compute sum of Mbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,
        Mbytes_alloc Mbytes,
       Mbytes_alloc-nvl(Mbytes_free,0) used,
       nvl(Mbytes_free,0) free,
       ((Mbytes_alloc-nvl(Mbytes_free,0))/ Mbytes_alloc)*100 pct_used
from ( select sum(bytes)/1024/1024 Mbytes_free, tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024 Mbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name
       union all
      select sum(bytes)/1024/1024 Mbytes_alloc,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by name
/
EOF