一、获取Oracle数据库表空间信息的原始脚本和自动发现脚本

 Oracle表空间的信息需要SQL语句查询得到，因此，我们首先创建一个获取表空间信息的原始脚本/home/oracle/tablespace.sh，这个脚本由oracle用户去执行，脚本编写如下： 

确认环境变量：

[root@racdb1 tmp]# cat /home/oracle/.bash_profile
# .bash_profile# Get the aliases and functions
if [ -f ~/.bashrc ]; then. ~/.bashrc
fi# User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATH
################OracleBegin#########################
umask 022
export TMP=/tmp
export TMPDIR=$TMP
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db
export ORACLE_HOSTNAME=p19c01
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=plm1
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
alias sas='sqlplus / as sysdba'
export PS1="[`whoami`@`hostname`:"'$PWD]$ '
################OracleEnd#########################

[root@racdb1 ~]# cat /home/oracle/tablespace.sh
#!/bin/bash#export ORACLE_BASE=/u01/app/oracle
#export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db
#export PATH=$ORACLE_HOME/bin:$PATH
#export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
source ~/.bash_profile  #执行此步可确保定时任务能执行成功，否则任务可能找不到变量sqlplus  -S / as sysdba <<EOF
set heading off
set pagesize 0
set feedback off
set linesize 200spool /tmp/ora_tablespace.txtSELECT aa.tablespace_name,round(aa.usd / bb.maxs * 100, 2) "USED%",round((bb.maxs - aa.usd) / 1024 / 1024 / 1024, 2) "FREE(G)",round(bb.maxs / 1024 / 1024 / 1024, 2) "MAX(G)",round(aa.usd / 1024 / 1024 / 1024, 2) "USED(G)"FROM (SELECT b.Tablespace_Name, (b.Bytes - NVL(a.Bytes, 0)) "USD"FROM (SELECT tablespace_name, SUM(bytes) bytesFROM Dba_Free_SpaceGROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(bytes) bytesFROM Dba_Data_FilesGROUP BY tablespace_name) bWHERE b.tablespace_name = a.tablespace_name(+)) aa,(SELECT tablespace_name,SUM(CASEWHEN maxbytes = 0 THENuser_bytesELSEmaxbytes