-
Hi,
I am trying to load around 3 million records in a table using sql loader (Oracle 8.1.7). It is a simple load of a comma delimited file.
I was able to load the same data before with the speed atleast 100 times faster
I dont know what happenned and what do do to fix this thing.
I have to deliver it today, so if please if someone can help me out with it.
Infact I even tried direct=y in the parfile but the load does not progress at all, it just hangs.
Thanks
Ronnie
Here is the control file
--------------------------
load data
infile 'loadhfod.dat'
badfile 'hfod.bad'
discardfile 'hfod.dcs'
truncate
into table ofas
fields terminated by ","
(company,
time,
region,
lineitem,
actual
nullif (actual = BLANKS),
actualus
nullif (actualus = BLANKS),
year_quarter)
Parameter File
----------------
userid=hfod/hfod@louise_company
control=loaddata.ctl
errors=25000
log=loaddata.log
Sample Data
--------------
MBOC,1996,REGROLL,COFYE, 12.000000, 12.000000,YR
MBOC,1996,REGROLL,COLOP, 12.000000, 12.000000,YR
MBOC,1996,REGROLL,COEPAM, 1.000000, 1.000000,YR
MBOC,1996,REGROLL,COCAI, 1.000000, 1.000000,YR
MBOC,1996,REGROLL,COAAER, 1.000000, 1.000000,YR
MBOC,1996,REGROLL,COYEER, 1.000000, 1.000000,YR
Table Structure
----------------
SQL> desc ofas
Name Null? Type
----------------------------------------- -------- -----------------
COMPANY VARCHAR2(10)
TIME VARCHAR2(10)
REGION VARCHAR2(10)
LINEITEM VARCHAR2(15)
ACTUAL NUMBER(20,6)
ACTUALUS NUMBER(20,6)
YEAR_QUARTER VARCHAR2(5)
important init.ora parameters
---------------------------------
db_block_buffers = 13124
shared_pool_size = 35837337
large_pool_size = 614400
log_buffer = 32768
db_block_size = 8192
compatible = 8.1.0
sort_area_size = 65536
sort_area_retained_size = 65536
The database is in no archive mode.
PLEASE HELP
-
Hi,
I am not sure about the syntax of the control file...
You are using TRUNCATE INTO [table_name]
Should it be INSERT or APPEND or REPLACE
Please check the syntax
Radha.
-
s_radhak,
Its truncating the data in the table before loading the recordsAny suggesstions why it is sooooooooo sloooooooooooow
Ronnie
-
Ronnie,
It might be because of the table's referential integrity constraints.
Radha.
-
There were no indexes or constraints on the table.
Anyways I dropped and recreated the table and now its fine.
I have no clue why. Any Ideas?????
Ronnie
-
how many extents does the table has? b4 u dropped it?
-
Do you have any triggers against the table? which you have forgot to disable?
Sam
