Using SQL*Loader to Reorganize Oracle Tables

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Using SQL*Loader to Reorganize Oracle Tables

Don Burleson

Another approach to Oracle table reorganization is to offload the table rows using SQL*Plus into a delimited file. This file can then be sorted (or extracted with ORDER BY), and the rows reloaded into the SAP table with SQL*Loader. These step are very straightforward:

1. Extract the rows with an SQL statement in SQL*Plus.

spool extract.lst

select field1||','||field2||','||field3

from table_name

order by column_name;

spool off;

2. Reload the file using SQL*Loader. Below is the command to reload a table:

sqlload USERID=sapr3/sapr3 CONTROL=sap.ctl LOG=sap.lst ERRORS=9999999

Here is a sample SQL*Loader control file:

LOAD DATA

INFILE 'vbap.dat'

BADFILE 'vbap.bad'

DISCARDFILE 'vbap.dis'

INTO TABLE "SAPR3"."VBAP"

REENABLE

TRAILING NULLCOLS

(

field1 position (1:4) char,

field2 position (6:9) char,

field3 position (11:45) char

)

This SQL*Loader approach is not very popular because of the amount of time required to extract the row data to a flat file (called "punching" the table) and then sorting the row data.

��