What is SPOOL ?Spool Command in ORACLE is used to print data from oracle tables into other files, meaning you can send all the sql outputs into any file you wish to.
How to SPOOL from ORACLE in CSV format ??
Login to sqlplus
Set echo off;
How to SPOOL from ORACLE in CSV format ??
Login to sqlplus
Set echo off;
Set Heading off;
Set define Off;
Set feedback Off;
set verify off;
Set serveroutput On;
SET PAGESIZE 5000
SET LINESIZE 120
Set define Off;
Set feedback Off;
set verify off;
Set serveroutput On;
SET PAGESIZE 5000
SET LINESIZE 120
SQL > Spool c:\file.csv (Windows)
SQL > SELECT COL1||','||COL2||','||COL3 FROM TABLE_NAME;
SQL> Spool Off;
Set define On;
Set feedback On;
Set feedback On;
Set heading on;
Set verify on;
Set verify on;
Ex: Recently i written a spool command for making all the tables and indexes max extent sizes to unlimited because lot of tables and indexes max extent size have NULL value
Set echo off;
Set Heading off;
Set define Off;
Set feedback Off;
Set verify off;
Set serveroutput On;
SET PAGESIZE 5000
SET LINESIZE 120
Set define Off;
Set feedback Off;
Set verify off;
Set serveroutput On;
SET PAGESIZE 5000
SET LINESIZE 120
SQL> Spool extent.sql
SQL> select 'alter '|| object_type||’ ‘||object_name||’ '||’ storage (maxextents unlimited);'
from dba_objects where object_type in ('TABLE','INDEX') and owner = 'XXX';
spool off
SQL> @extent.sql (for executing spool command)
If u didn’t specify anything after the file name(ex: extent instead of extent.sql) then oracle by default generates output file as ‘.log’ extention(i.e., extent.log)
If we have very few tables in the database instead of writing spool command we can do manually one after another using
SQL > alter table tab_name move storage (maxextents unlimited);
Table altered.
Table altered.
Or
SQL> alter index ind_name move storage (maxextents unlimited);
Index altered.
Using single command we can write dynamic sql script to do the same changes for all the objects
NOTE:
In Linux the output can be seen in the Directory from where you entered into the SQLPLUS
In Windows the output file is located where you specified in the spool
APPEND:
If you want the sql output to append into any existing file then you can do the below
login to sqlplus
SQL > spool /opt/oracle/File.log append
