Tablespace usage considering autoextend on

This script will show  tablespaces from all databases in server which are full by 85 percent.This script will take care autoextend on clause.This means it will show tablespace usage if growth is exceeding autoextended space.

1.The shell script:-

for j in ORCL1 ORCL2
do
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
export ORACLE_SID=$i
#$ORACLE_HOME/bin/sqlplus -S monitor/m0n1t0r@”$j” << EOF >> tb_check_all_`date +\%d\%m\%Y\%H`.log
$ORACLE_HOME/bin/sqlplus -S monitor/m0n1t0r@”$j” << EOF >> tb_check_all.log
@tb_check.sql
exit
EOF
done

cat tb_check_all.log | grep -v “rows” > tb1_check_all.log
awk ‘{if (++dup[$0] == 1) print $0;}’ tb1_check_all.log > tb_check_all_`date +\%d\%m\%Y\%H`.log
rm tb*all.log

2.The content of tb_check.sql

SET FEEDBACK OFF
select name DB_NAME,TABLESPACE_NAME,round((TABLESPACE_SIZE*8192)/1024/1024/1024,0) “SIZE”,round((USED_SPACE*8192)/1024/1024/1024,0) “USED”,round(USED_PERCENT,0) “%USED” from dba_tablespace_usage_metrics,v$database where used_percent>85;