Tuesday, January 10, 2012

Oracle spool command


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;
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 COL1||','||COL2||','||COL3 FROM TABLE_NAME;

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

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




0 comments:

Post a Comment

Auto Scroll Stop Scroll