Re: SQLLOAD / Rollback

I believe SQL*Loader executes commits every n records based on the size of the bind array. You should see messages saying "commit point reached xxxx records" in your SQL*Loader output. The Oracle utilities manual suggests increasing the size of the bind array to the size of 100 rows (row size * 100). If you want to avoid the commit process altogether you can use the direct=true option in your control file. This option bypasses the conventional Oracle load path and loads data directly to the table and indexes. There is a warning regarding failure to load all rows successfully causing indexes to be unusable in the Oracle utilities manual if you use direct loading option, however. In that case you have to drop and create the indexes again in order for the table to be usable.

Another factor may be causing your problems, however. Are you using the replace or append option for loading? Replace (the default) causes SQL*Loader to delete all rows from the table being loaded (using rollback segments) prior to beginning the load. If your table already contains a large number of rows this could be why you rollback area is running out of space. If you really want to reload this table from scratch use the trunc command in v7 or drop and create the table prior to running SQL*Loader. Otherwise use the append option. Hope this helps. Received on Thu Jul 27 1995 - 00:00:00 CEST