Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need help formatting in stream SQL output

Re: Need help formatting in stream SQL output

From: Eric <admin_at_t10.net>
Date: Sat, 25 Sep 1999 16:32:03 -0600
Message-ID: <0XbH3.1500$Pv.78926@news.uswest.net> 





You can use perl and the Oracle dbi driver to it's all
in one file.


http://theoryx5.uwinnipeg.ca/mod_perl/cpan-search?join=and&arrange=fi=

le&download=auto&stem=no&case=clike&site=ftp.funet.fi&age=&dist=
info=338




    Sidhe wrote in message <37EC6F02.C4E28D8F_at_home.net>...
    Hello all! 


    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_at_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_at_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_at_dollargeneral.com 



Received on Sat Sep 25 1999 - 17:32:03 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US