Need help formatting in stream SQL output

My objective is to execute a series of O/S commands and spooled SQL queries
from within a single script without calling out to any secondary command files or scripts. I want to use just 1 file.

Here is what I WAS doing using multiple command files...

*********************
Main Script
*********************
tms: /util # cat extents1
### Creates /tmp/extents report for MTPPROD and mails it to the DBA

## Only interested in Production instance
export ORACLE_SID=MTPPROD

## Show tables with extents > 25
sqlplus username/password @textents.sql

## Show indexes with extents > 25
sqlplus username/password @iextents.sql

## Show RowCount Info
sqlplus username/password @rowcount.sql

## Cleanup and send report
mv /tmp/tab_extents.lis /tmp/extents
cat /tmp/idx_extents.lis >> /tmp/extents
cat /tmp/rowcount.lis >> /tmp/extents
rm /tmp/*extents.lis
rm /tmp/rowcount.lis
mail -s "MTPPROD Extents" rwestbrook@dollargeneral.com </tmp/extents
*****************
textents.sql
*****************
set linesize 80
set feedback off

COLUMN SEGMENT_NAME FORMAT A25 HEADING 'NAME'

SPOOL /tmp/tab_extents.lis

SELECT A.SEGMENT_NAME, A.SEGMENT_TYPE TYPE,
MAX(A.EXTENT_ID) EXTENTS, b.max_extents MAX,
(b.max_extents - MAX(A.EXTENT_ID)) REMAINING
FROM DBA_EXTENTS A, DBA_TABLES B
WHERE A.OWNER = 'MTPMGR' AND
A.SEGMENT_TYPE IN ('TABLE','INDEX') AND
B.TABLE_NAME = A.SEGMENT_NAME
GROUP BY A.OWNER,SEGMENT_TYPE,A.SEGMENT_NAME,B.MAX_EXTENTS
HAVING MAX(A.EXTENT_ID) > 25;

spool off
exit
****************
iextents.sql
****************
set linesize 80
set feedback off

COLUMN SEGMENT_NAME FORMAT A25 HEADING 'NAME'

SPOOL /tmp/idx_extents.lis

SELECT A.SEGMENT_NAME, A.SEGMENT_TYPE TYPE,
MAX(A.EXTENT_ID) EXTENTS, b.max_extents MAX,
(b.max_extents - MAX(A.EXTENT_ID)) REMAINING
FROM DBA_EXTENTS A, DBA_INDEXES B
WHERE A.OWNER = 'MTPMGR' AND
A.SEGMENT_TYPE IN ('TABLE','INDEX') AND
B.INDEX_NAME = A.SEGMENT_NAME
GROUP BY A.OWNER,SEGMENT_TYPE,A.SEGMENT_NAME,B.MAX_EXTENTS
HAVING MAX(A.EXTENT_ID) > 25;

spool off
exit
****************
rowcount.sql
****************
set linesize 80
set feedback off
spool /tmp/rowcount.lis
select count(*) Load_Stop from load_stop;
select count(*) Status_History from status_history;
spool off
exit
****************

My output looks nice and clean like this...(and this is the way I want it to look)

*******************
NAME                      TYPE                 EXTENTS        MAX  REMAINING

------------------------- ----------------- ---------- ---------- ----------

INCOMPAT_TRLR_TYPE        TABLE                    120        249        129

INTFC_LTL_RATE            TABLE                     52        249        197

LOAD                      TABLE                    129        249        120

LOAD_STOP                 TABLE                    242        300         58

LTL_BASE                  TABLE                    132        249        117

LTL_RATE                  TABLE                     50        249        199

MOD_LIST                  TABLE                     72        249        177

ORD                       TABLE                    100        249        149

ORD_LOAD                  TABLE                     68        249        181

ORD_LOAD_SEQ              TABLE                     36        249        213

STATUS_HISTORY            TABLE                     79        250        171

TL_RATE_CB                TABLE                     52        249        197

ZONE_NETWORK_DET          TABLE                    199        249         50
 

NAME                      TYPE                 EXTENTS        MAX  REMAINING

------------------------- ----------------- ---------- ---------- ----------

FK_LOAD_STOP_1_IDX        INDEX                     40        249        209

PK_LOAD                   INDEX                     48        249        201

PK_LOAD_STOP              INDEX                    190        249         59

PK_LTL_BASE               INDEX                     69        249        180

PK_MOD_LIST               INDEX                    126        249        123

PK_ORD                    INDEX                     78        249        171

PK_ORD_LOAD_SEQ           INDEX                    103        249        146

PK_ORD_QUANTITY           INDEX                    111        249        138

PK_STATUS_HISTORY         INDEX                    251        300         49

PK_TL_RESOURCE_CARRIER    INDEX                     34        249        215

PK_TL_RESOURCE_TRLR       INDEX                     54        249        195
 

 LOAD_STOP
----------
     13736

STATUS_HISTORY
--------------
         23750
********************

But I want to run it all from a single script.. so I combined everything
into one single file/script like this...

*****************
Main Script
*****************
### Creates /tmp/extents report for MTPPROD and mails it to the DBA

## Only interested in Production instance
export ORACLE_SID=MTPPROD

## Show tables with extents > 25
sqlplus username/password <<EOF

set linesize 80
set feedback off
COLUMN SEGMENT_NAME FORMAT A25 HEADING 'NAME';

SPOOL /tmp/tab_extents.lis

SELECT A.SEGMENT_NAME, A.SEGMENT_TYPE TYPE,
MAX(A.EXTENT_ID) EXTENTS, b.max_extents MAX,
(b.max_extents - MAX(A.EXTENT_ID)) REMAINING
FROM DBA_EXTENTS A, DBA_TABLES B
WHERE A.OWNER = 'MTPMGR' AND
A.SEGMENT_TYPE IN ('TABLE','INDEX') AND
B.TABLE_NAME = A.SEGMENT_NAME
GROUP BY A.OWNER,SEGMENT_TYPE,A.SEGMENT_NAME,B.MAX_EXTENTS
HAVING MAX(A.EXTENT_ID) > 25;

spool off;

EOF

## Show indexes with extents > 25
sqlplus username/password <<EOF

set linesize 80
set feedback off
COLUMN SEGMENT_NAME FORMAT A25 HEADING 'NAME';

SPOOL /tmp/idx_extents.lis

SELECT A.SEGMENT_NAME, A.SEGMENT_TYPE TYPE,
MAX(A.EXTENT_ID) EXTENTS, b.max_extents MAX,
(b.max_extents - MAX(A.EXTENT_ID)) REMAINING
FROM DBA_EXTENTS A, DBA_INDEXES B
WHERE A.OWNER = 'MTPMGR' AND
A.SEGMENT_TYPE IN ('TABLE','INDEX') AND
B.INDEX_NAME = A.SEGMENT_NAME
GROUP BY A.OWNER,SEGMENT_TYPE,A.SEGMENT_NAME,B.MAX_EXTENTS
HAVING MAX(A.EXTENT_ID) > 25;

spool off;

EOF

## Show RowCount Info

sqlplus username/password <<EOF

set linesize 80
set feedback off

spool /tmp/rowcount.lis

select count(*) Load_Stop from load_stop;
select count(*) Status_History from status_history;

spool off;

EOF

## Cleanup and send report
mv /tmp/tab_extents.lis /tmp/extents
cat /tmp/idx_extents.lis >> /tmp/extents
cat /tmp/rowcount.lis >> /tmp/extents
rm /tmp/*extents.lis
rm /tmp/rowcount.lis

mail -s "MTPPROD Extents" rwestbrook@dollargeneral.com </tmp/extents
****************

However, even though I'm using the same SQL with the same options, my output
looks like this...

****************
SQL> SELECT A.SEGMENT_NAME, A.SEGMENT_TYPE TYPE,
  2  MAX(A.EXTENT_ID) EXTENTS, b.max_extents MAX,
  3  (b.max_extents - MAX(A.EXTENT_ID)) REMAINING
  4  FROM DBA_EXTENTS A, DBA_TABLES B
  5  WHERE A.OWNER = 'MTPMGR' AND
  6  A.SEGMENT_TYPE IN ('TABLE','INDEX') AND
  7  B.TABLE_NAME = A.SEGMENT_NAME
  8  GROUP BY A.OWNER,SEGMENT_TYPE,A.SEGMENT_NAME,B.MAX_EXTENTS
  9  HAVING MAX(A.EXTENT_ID) > 25;

NAME                      TYPE                 EXTENTS        MAX  REMAINING

------------------------- ----------------- ---------- ---------- ----------

INCOMPAT_TRLR_TYPE        TABLE                    120        249        129

INTFC_LTL_RATE            TABLE                     52        249        197

LOAD                      TABLE                    129        249        120

LOAD_STOP                 TABLE                    242        300         58

LTL_BASE                  TABLE                    132        249        117

LTL_RATE                  TABLE                     50        249        199

MOD_LIST                  TABLE                     72        249        177

ORD                       TABLE                    100        249        149

ORD_LOAD                  TABLE                     68        249        181

ORD_LOAD_SEQ              TABLE                     36        249        213

STATUS_HISTORY            TABLE                     79        250        171

TL_RATE_CB                TABLE                     52        249        197

ZONE_NETWORK_DET          TABLE                    199        249         50

SQL> spool off;
SQL> SELECT A.SEGMENT_NAME, A.SEGMENT_TYPE TYPE,
  2  MAX(A.EXTENT_ID) EXTENTS, b.max_extents MAX,
  3  (b.max_extents - MAX(A.EXTENT_ID)) REMAINING
  4  FROM DBA_EXTENTS A, DBA_INDEXES B
  5  WHERE A.OWNER = 'MTPMGR' AND
  6  A.SEGMENT_TYPE IN ('TABLE','INDEX') AND
  7  B.INDEX_NAME = A.SEGMENT_NAME
  8  GROUP BY A.OWNER,SEGMENT_TYPE,A.SEGMENT_NAME,B.MAX_EXTENTS
  9  HAVING MAX(A.EXTENT_ID) > 25;

NAME                      TYPE                 EXTENTS        MAX  REMAINING

------------------------- ----------------- ---------- ---------- ----------

FK_LOAD_STOP_1_IDX        INDEX                     40        249        209

PK_LOAD                   INDEX                     48        249        201

PK_LOAD_STOP              INDEX                    190        249         59

PK_LTL_BASE               INDEX                     69        249        180

PK_MOD_LIST               INDEX                    126        249        123

PK_ORD                    INDEX                     78        249        171

PK_ORD_LOAD_SEQ           INDEX                    103        249        146

PK_ORD_QUANTITY           INDEX                    111        249        138

PK_STATUS_HISTORY         INDEX                    251        300         49

PK_TL_RESOURCE_CARRIER    INDEX                     34        249        215

PK_TL_RESOURCE_TRLR       INDEX                     54        249        195

SQL> spool off;
SQL> select count(*) Load_Stop from load_stop;

 LOAD_STOP
----------
     13707
SQL> select count(*) Status_History from status_history;

STATUS_HISTORY
--------------
         23756
SQL> spool off;
**********************

My question is this;  How can I run my queries and commands from one single
file and still have my output the way I want it?  ie just the headings and data without the query echos.

Thanks in advance!
Roger Westbrook, DBA/Sys Admin
Dollar General Corp.
rwestbrook@dollargeneral.com Received on Sat Sep 25 1999 - 01:43:04 CDT