Outputting Spool correct in oracle. Spooling only results, spool output in oracle

Admin — Wed, 09 Dec 2009 13:27:36 GMT

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,

FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / Oracle Database Administration / Outputting Spool correct in oracle. Spooling only results, spool output in oracle / Latest Posts

Outputting Spool correct in oracle. Spooling only results, spool output in oracle