#!/bin/sh

 
#
# (c) spd_at_daphne.cps.unizar.es
# Fri Oct  7 11:14:43 MEST 2005
# License: Cantoware - Si te gusta ya te puedes dar con un canto en los dientes
#
# This script list tablespaces
# Needs: root privileges to be able to "su oracle"
# Use: ./ora-df.sh
# Depends: ora-common.sh
#

. $HOME/usr/sbin/ora-common.sh

su $ORACLE_OWNER -c "$CMD $ARGS" 2>&1 <<++
$INIT
column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999

select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;
++