Re: SQLLOADER

In article <3ves8e$oen_at_news1.mnsinc.com>, cadre_at_neuraltech.com says...
>
>I created a tablespace for the index , size 100m;initial 70m;next 30m;
>When I load the data for the first tiem, everything is OK.
>When I try to load even a 1m data next time, the load is succesful
>but the index is kep in direct load state
>Is it a bug or feature?

it is not clear: the loader is doing what oracle said it would do in direct load. it must create a temporary segment; and it must create extents on your table equivalent to initial + next (if necessary). apparently it treats each direct load occurrence as a creation of a new segment, using the rules of your existing table. it then concatenates the segments.

this has another effect you didn't mention: at the end of each direct load segment, there's a chunk of empty space. this space will not be used up by the next direct load. you can use up all of your disk due to a load which just barely overflows the initial extent size: the next extent will be almost empty. likewise if your direct load is a lot less than the initial extent size. this is NOT documented anywhere.

the temporary segments are released by restarting the database; you might also try taking the tablespaces offline and online instead. you cannot delete them in any other legitimate way.

as for the problem of running out of tablespace due to the extent allocations, i recommend specifying the data file to which the segments will be written (a new option in 7.1 sqlloader), so that you can control how the extents are consumed. also, estimate how much data each sqlload batch will produce (e.g., bytes per record) and set your initial extent size of your table accordingly, to avoid extra extents or nearly-empty extents.

-- 
Dave Mausner, Sr. Consultant, Braun Technology Group, Chicago.