how to unload a table to a flat text file

HOW TO UNLOAD A TABLE INTO SQL*LOADER FORMAT:

SCRIPT TO UNLOAD A TABLE INTO SQL*LOADER FORMAT [ID 35275.1]
		Modified 17-APR-2011     Type SCRIPT     Status PUBLISHED

Configuring the Script This script generates a SQL*Plus script to unload a table to a file and a SQL*Loader script to reload the same data. Intent is to create a faster alternative to export/import.

Initial testing indicates that the unload takes about 20% longer than EXP, but that the reload takes only 45% as long as IMP. Performance is very specific to the data distribution of the table data. Much poorer performance has been seen in the following cases:

- many long varchar columns which actually contain short data values;
- many number columns without precision or scale which are defaulted to large numbers;
- lots of null values;

All of these cases lead to inefficient use of the fixed record format of the unload file. Padding the
unload file with leading/trailing zeros or trailing blanks yields files 4X the size of an export dmp file
and unload times 4X those of export (even in these extreme test situations, the load time was still
between 80% and 90% of IMP).

This unload/reload utility has some other advantages besides speed. The unload can easily select a subset of the original table (for statistical sampling or retrieving rows for a particular department or business date for instance) whereas EXP/IMP deals with entire tables. Additionally, if desired, unload can sort the output in order to speed index builds and/or optimize cache hits on the reloaded data based on loading frequently used rows contiguously. This may provide an additional benefit in some reorg efforts. Finally, the unload might have a GROUP BY appended which would facilitate development of summary tables.

By editing the generated unload_fixed2.sql and SQL*Loader .CTL script, one could additionally remove columns or modify them with SQL (or PL/SQL functions) during the extract. Just be sure to adjust the length of the COLUMN and PAGESIZE in unload_fixed2.sql and the input field in .CTL to reflect whatever changes.

This utility can also unload data from a view which is not possible via EXP. This facility may be used to do subsets selection (specific rows), projection (specific columns), joins, GROUP BY summaries or function application without having to edit this script or its generated output scripts.

Arguments:

IN: prompts for table_owner, table_name as well as default_precision
and default_scale for columns defined as NUMBER or FLOAT
(without precision or scale defined).

OUT: none

Limitations: Doesn't handle long, raw, long raw, rowid, mlslabel datatypes. Unlike EXP/IMP, however, it will not automatically make translations when the unload file is moved from ASCII to EBCDIC or vice versa. Since all data is written in external formats, one should expect that file transfer utilities that do such conversions should work. As an alternative, one could edit the SQL*Loader .CTL script to specify the alternative encoding scheme.

If a numeric column is encountered which has no defined precision or scale, then this script will use default values (prompted for); this poses three risks: 

1) that you may overspecify the precision and thereby waste space in the unload file;
2) you may underspecify the precision and thereby get overflow indicators in the unloaded data which may not be caught prior to loading;
3) you may underspecify the scale and introduce truncation which will not be found by either the unload or load processes. For this reason, it is strongly recommended that numeric table columns be defined with appropriate precision and scale values.

The generated SQL*Loader script assumes that fields of blanks should be loaded as NULLS...if the table has columns for which SPACES are valid values, then it will be necessary to edit the generated unload_fixed2.sql script to concatenate double quotes before and after the affected column(s) along with changing the length (pagesize in unload_fixed2.sql and the individual field's length in the generated .CTL file) by two bytes.

Running the Script

Execution Environment:
SQL*Plus

Access Privileges:
User must have DBA privileges

Usage:
sqlplus / @unload_fixed.sql

Instructions:

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail
packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script
may not be in an executable state when you first receive it. Check over the script to ensure that errors
of this type are corrected. The script will produce output files named unload_fixed2.sql and
..ctl. These files can be viewed in a browser or uploaded for support analysis.

Caution

This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Script

SQL*Plus script unload_fixed.sql:

set tab off
set heading off feedback off echo off verify off
set space 1 pagesize 0 linesize 120

accept owner prompt 'What schema owns the table to be unloaded? '
accept table_name prompt 'What table is to be unloaded? '
accept default_precision prompt 'No. of digits for precision: '
accept default_scale prompt 'No. of digits for scale? '

-- Generate the unload script

spool unload_fixed2.sql
select 'SET HEADING OFF FEEDBACK OFF ECHO OFF VERIFY OFF'||chr (10)||
'SET SPACE 0 PAGESIZE 0 TERMOUT OFF TRIMSPOOL ON'
from dual;

-- Calculate the sum of all output field lengths and set the output record size
select 'SET LINESIZE '||
(sum (decode (data_type,
'CHAR', data_length,
'VARCHAR', data_length,
'VARCHAR2', data_length,
'DATE', 14,
'NUMBER', decode (data_precision, '',
&default_precision + 2,
greatest (data_precision - data_scale, 1) +
decode (data_scale, 0, 0, 1) + data_scale) + 1,
'FLOAT', &default_precision + 2,
data_length)))
from dba_tab_columns
where owner = upper ('&&owner') and
table_name = upper ('&&table_name');

-- Generate an appropriate SQL*Plus COLUMN command to control
-- formatting of each output field
select 'COLUMN '||rpad ('"'||column_name||'"', 32)||' FORMAT '||
rpad (decode (data_type, 'CHAR', 'A'||data_length, 'VARCHAR2', 'A'||data_length,
'VARCHAR', 'A'||data_length, 'DATE', 'A14', 'NUMBER', decode (data_precision, '',
rpad ('0', &default_precision - &default_scale, '9')||'.'||rpad ('9', &default_scale, '9'),
rpad ('0', greatest (data_precision - data_scale, 1), '9')||decode (data_scale, 0, '',
'.')||decode (data_scale, 0, '', rpad ('9', data_scale, '9'))), 'FLOAT', rpad ('0', &default_precision - &default_scale, '9')||'.'||rpad ('9', &default_scale, '9'), 'ERROR'),40)||' HEADING ''X'''
from dba_tab_columns
where owner = upper ('&&owner') and
table_name = upper ('&&table_name')
order by column_id;

-- Generate the actual SELECT statement to unload table data

select 'SPOOL &&owner..&&table_name..DAT' from dual;

column var1 noprint
column var2 noprint
select 'a' var1, 0 var2, 'SELECT '
from dual
union
select 'b', column_id, decode (column_id, 1, ' ', ' , ')||
decode (data_type, 'DATE', 'to_char ('||'"'||column_name||
'"'||', ''YYYYMMDDHH24MISS'') '||'"'||column_name||'"','"'||column_name||'"')
from dba_tab_columns
where owner = upper ('&&owner') and
table_name = upper ('&&table_name')
union
select 'c', 0, 'FROM &&owner..&&table_name'
from dual
union
select 'd', 0, ';'
from dual
order by 1,2;

select 'SPOOL OFF' from dual;
select 'SET TERMOUT ON' from dual;

spool off

-- Generate the SQL*Loader control file

set lines 120 pages 0
spool &&owner..&&table_name..CTL
select 'a' var1, 0 var2, 'OPTIONS(DIRECT=TRUE)'
from dual
union
select 'b', 0, 'LOAD DATA'
from dual
union
select 'c', 0, 'INFILE ''&&owner..&&table_name..DAT'''
from dual
union
select 'd', 0, 'BADFILE &&owner..&&table_name..BAD'
from dual
union
select 'e', 0, 'DISCARDFILE &&owner..&&table_name..DSC'
from dual
union
select 'f', 0, 'DISCARDMAX 999'
from dual
union
select 'm', 0, 'INTO TABLE &&owner..&&table_name'
from dual
union
select 'n', column_id, rpad (decode (column_id, 1, '(', ',')||
'"'||column_name||'"', 31)||
decode (data_type,
'CHAR', 'CHAR('||data_length||')',
'VARCHAR', 'CHAR('||data_length||')',
'VARCHAR2', 'CHAR('||data_length||')',
'DATE', 'DATE(14) "YYYYMMDDHH24MISS"',
'NUMBER', 'DECIMAL EXTERNAL ('||decode (data_precision,
'', &default_precision + 2,
greatest (data_precision - data_scale, 1) +
decode (data_scale, 0, 0, 1) + data_scale + 1)||')',
'FLOAT', 'DECIMAL EXTERNAL ('||to_char (&default_precision +
2)||')', 'ERROR--'||data_type)||' NULLIF ("'||
column_name||'" = BLANKS)'
from dba_tab_columns
where owner = upper ('&&owner') and
table_name = upper ('&&table_name')
union
select 'z', 0, ')'
from dual
order by 1, 2;

spool off

-- Cleanup

clear column
clear break
clear compute
undef owner
undef table_name
undef default_precision
undef default_scale