spool command

What is SPOOL ?
Spool Command in ORACLE is used to transfer data from oracle tables into other files.

How to SPOOL from ORACLE in CSV format ??

Set echo off;

Set Heading off;
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 EMPNAME||','||EMPID||','||EMPSAL FROM EMPLOYEE;

SQL>  Spool Off;

Set define On;
Set feedback On;

Set heading 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

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.

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