|
 |
|
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.
|
