I have a script which gives the output of schemas in Oracle DB :
#!/bin/ksh
ps -ef | grep -v grep | grep ora_pmon_$1 | wc -l | while read CONTROL
do
if [ "$CONTROL" -gt 0 ] ; then
ORACLE_HOME=/u01/app/oracle/product/9.2.0
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH:/bin:/usr/bin:usr/local/bin:.
export PATH
NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_LANG
ORACLE_SID=$1
export ORACLE_SID
cd $2
sqlplus "/ as sysdba" <<EOF
set echo off
set heading off
set feedback off
set termout off
spool stats.sql
select 'execute DBMS_STATS.GATHER_SCHEMA_STATS('''||username||''') ;' from dba_users
where username <> 'SYS'
/
spool off
@stats.sql
exit
EOF
fi
done
The code fails at the line @stats.sql . although I have tried giving absolute pathnames to the spooled file but the result is the same.
The script creates the stats.sql file but fails to execute it (--@stats.sql)
and disconnects from oracle..
Any thoughts?
rte