I recently was in a process to create a script that rebuilds indexes on a weekly basis and I had a unix shell script that dynamically created the alter statements and spools it out to a file and then runs it. I was trying to figure out how to spool only the results and stop the heading and feedbacks etc.. This is what I had and it kept on putting the sql statement and spool off in the spool file:#!/usr/bin/ksh
. ~/.profile_PRODDB
echo $ORACLE_SID
echo $ORACLE_HOME
echo " "
$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF
set feedback off
set verify off
set pagesize 0
set term off
set termout off
set heading off
set echo off
spool rebuild_ecom_indexes.sql
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where owner='ECOM' and TEMPORARY='N';
spool off
set timing on
set term on
spool /oracle/dba/scripts/logs/rebuild_indexes.log
@rebuild_ecom_indexes.sql
EOF
When I run this unix shell script it kept on putting the following in the rebuild_ecom_indexes.sql script:
SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where owner='ECOM' and TEMPORARY='N';
alter index ECOM.IDX$$_17CE30002 rebuild;
alter index ECOM.SYS_C007048 rebuild;
alter index ECOM.ADD_ARC_IDXSYSID rebuild;
alter index ECOM.ADD_ARC_EXPORDERS2 rebuild;
alter index ECOM.CUST_4413_5487__PRIMARYKEY rebuild;
SQL> spool off
I couldn't figure out why the item in red above kept coming into the spool file. I had set the following in the script:
set feedback off
set verify off
set pagesize 0
set term off
set termout off
set heading off
set echo off
And still no good. I started searching google and couldn't figure it out ... there were others looking for the same soultion and i found out that in the sqlplus command we need to issue a -s and it will stop the sql commands from being spooled out to the spool file.
So it should look like this:
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF
set feedback off
set verify off
set pagesize 0
set term off
set termout off
set heading off
set echo off
spool rebuild_ecom_indexes.sql
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where owner='ECOM' and TEMPORARY='N';
spool off
set timing on
set term on
spool /oracle/dba/scripts/logs/rebuild_indexes.log
-- @rebuild_ecom_indexes.sql
EOF
Hope this helps.
Good luck,
Thanks,