tablespace的usage使用情况

tablespace_usage.sh:

#!/bin/sh
# Oracle specific environment settings
export ORACLE_BASE=/ora01
export ORACLE_HOME=$ORACLE_BASE/9i
export ORACLE_SID=srm

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:.
datadate=`date +%D`
datatime=`date +%R`
datapath=/backup/scrīpt/tunning/tablespace_usage
data=tablespace_usage.data-`date +%Y-%m`
sqlplus '/as sysdba' @$datapath/tablespace_usage.sql >$datapath/tablespace_usage.log
values=`/bin/cat $datapath/tablespace_usage.log |egrep -v "SQL|Oracle|JServer|With|Disconnect|Connect|Total|-|FILE|rows|LEFT"|sed '/^$/d'| awk '{print $2,$5}'`
echo $datadate $datatime $values >> $datapath/$data

tablespace_usage.sql

select b.file_id,b.tablespace_name,b.bytes,sum(nvl(a.bytes,0)) left,sum(nvl(a.bytes,0))/(b.bytes)*100 left_percent from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id;
exit;