Oracle Spool to File in SQLPlus

I will explain Oracle Spool to File in SQLPlus in this post.

Oracle Spool to File

Sometimes you want to log the output of a MaxL Shell session to a file. Send standard output, informational messages, error messages, and/or warning messages generated by the execution of MaxL statements to a file.

If specified filename does not exist, it is created. If filename already exists, it is overwritten. If a directory path is not specified for filename, filename is created in the current directory of the MaxL Shell. Directories cannot be created using the spool command.

Syntax

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

Stores query results in a file, or optionally sends the file to a printer.

Terms

file_name[.ext]

Represents the name of the file to which you wish to spool. SPOOL followed by file_name begins spooling displayed output to the named file. If you do not specify an extension, SPOOL uses a default extension (LST or LIS on most systems). The extension is not appended to system files such as /dev/null and /dev/stderr.

CRE[ATE]

Creates a new file with the name specified.

REP[LACE]

Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file. This is the default behavior.

APP[END]

Adds the contents of the buffer to the end of the file you specify.

OFF

Stops spooling.

OUT

Stops spooling and sends the file to your computer’s standard (default) printer. This option is not available on some operating systems.

Enter SPOOL with no clauses to list the current spooling status.

Output of logging begins with spool on and ends with spool off as follows.

SQL> 
SQL> spool /home/oracle/instance.txt
SQL> 
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION VERSION_LEGACY VERSION_FULL STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO CON_ID INSTANCE_MO EDITION FAMILY DATABASE_TYPE
--------------- ---------------- ---------------------------------------------------------------- ----------------- ----------------- ----------------- ------------------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- --- ---------- ----------- ------- -------------------------------------------------------------------------------- ---------------
8 MSDB1 msdbadm01 19.0.0.0.0 19.0.0.0.0 19.8.0.0.0 25-05-2021 13:31:27 OPEN YES 8 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE RAC

SQL> 
SQL> spool off
SQL> 
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

[MSDB1]/home/oracle $ 
[MSDB1]/home/oracle $ cat instance.txt 
SQL> 
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION VERSION_LEGACY VERSION_FULL STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO CON_ID INSTANCE_MO EDITION FAMILY DATABASE_TYPE 
--------------- ---------------- ---------------------------------------------------------------- ----------------- ----------------- ----------------- ------------------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- --- ---------- ----------- ------- -------------------------------------------------------------------------------- --------------- 
8 MSDB1 msdbadm01 19.0.0.0.0 19.0.0.0.0 19.8.0.0.0 25-05-2021 13:31:27 OPEN YES 8 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE RAC

SQL> 
SQL> spool off
[MSDB1]/home/oracle $

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )