SQL*Loader halt when loading large varray object

I was using sql*loader of Oracle 8i NT, trying to load some records with varray (some records have lots of array elements, up to several thousands). however, after several hundreds records loaded, sql*loader became very slow, and virtually stopped at some point. I watched the system resouces taken by sql*loader, it simply drove my NT out of physical memory (it occupied hundreds meg of physical mem). I set virtual memory to very large, and didn't help neither. My whole datafile is only 60MB, although several lines have 250K chars in single line/record. but such record only takes sql*loader one minute to load if I load it individually. However, if I load the records 100 after 100 in "append" mode (loading 100, then load next 100 with skipping previous loaded records), it works fine, the loader only occupied 60 meg physical mem, and released the mem when I started next 100 manually. This is really bizzare for that sql*loader seems doesn't know how to unload the memory if I chose to load the whole data file automatically. I tried to manipulate the ROWS and BINDSIZE options, doesn't help much.

Does anyone has any idea about this strange thing? Is there any other way to load data into Oracle tables? I can't believe sql*loader will take several days to load only 60mb exteral text file.

Thanks a lot!!

John Received on Mon Aug 14 2000 - 10:04:38 CDT