I am using a EOF together with sqlplus. However I find the output format is terrible
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LOCATION=/home/oracle/monitor
#find /var/tmp/ -name sysaux_detail*.log -mtime +7 -exec rm -f {} \;
SID_LIST=`ps -ef | grep "_pmon_" | grep -v grep | cut -d"_" -f3`
for SID in ${SID_LIST};
do
export ORACLE_SID=${SID}
cd $ORACLE_HOME/bin
cmd=`./sqlplus -s "user/password@${SID}"<< EOF
set linesize 300
set pagesize 1
col OCCUPANT_NAME format A7
col OCCUPANT_DESC format a70
SELECT name,OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES FROM v\\$sysaux_occupants,v\\$database where OCCUPANT_NAME ='SM/AWR';
select file_name from dba_temp_files;
exit;
EOF`
echo ${cmd}
#echo ${SID}
done
and the output is like
./testing.sh: line 34: warning: here-document at line 26 delimited by end-of-file (wanted `EOF')
TESTDB1 SM/AWR Server Manageability - Automatic Workload Repository 1536768 /u01/oradata/testdb1/temp01.dbf