How to Append Command Outputs To Oracle SQLPLUS Spool File

Generally we use sqlplus spool command to record the sql command outputs to a file. It will record the command outputs between spool on and spool off. Generally once you done with the spool off command the output file will be closed from the further logging.

Oracle 10g onwards one more option added to the spool command to append the sql command to an existing file.

Syntax: spool filename append

See an example here.

SQL> spool output.log

SQL> l

  1* select * from tab

SQL> /

 

TNAME                          TABTYPE  CLUSTERID

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

BIN$6uc9U2cLQPrgQ55BKAoOFg==$0 TABLE

DEPT                           TABLE

HINT_TEST                      TABLE

MV_DEPT                        TABLE

OBJ_LIST                       TABLE

RUPD$_DEPT                     TABLE

VW_EMP                         VIEW

VW_EMP_DEPT                    VIEW

VW_FORCE_INVALID               VIEW

 

9 rows selected.

 

SQL> spool off

SQL> spool output.log append

SQL> show user

USER is "STHOMAS"

SQL> select * from dept;

 

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

SQL> spool off

 

Printing the content of the file.

SQL> !cat output.log

SQL> l

  1* select * from tab

SQL> /

 

TNAME                          TABTYPE  CLUSTERID

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

BIN$6uc9U2cLQPrgQ55BKAoOFg==$0 TABLE

DEPT                           TABLE

HINT_TEST                      TABLE

MV_DEPT                        TABLE

OBJ_LIST                       TABLE

RUPD$_DEPT                     TABLE

VW_EMP                         VIEW

VW_EMP_DEPT                    VIEW

VW_FORCE_INVALID               VIEW

 

9 rows selected.

 

SQL> spool off

SQL> show user

USER is "STHOMAS"

SQL> select * from dept;

 

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

SQL> spool off
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google