SQLLOADER
Date: 1995/07/30
Message-ID: <3ves8e$oen_at_news1.mnsinc.com>#1/1
I am using 7.1.4 on HP UNIX.
SQL*loader with dirct path, indexes on, with sorted indexes parameter,
presorted data, among others.
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
with the error
ORA
The following index(es) on table TEST_01 were processed:
Index TEST01_IN_CDHOLDERACC was left in Direct Load State due to
ORA-01652: unable to extend temp segment by 5120 in tablespace TEST_01
After trial and error , I figured out that the loader is trying to
create a temporary segment of size of the initial segment.(rather than the next segment). I tried to alter the tablespace /index storage parameters, but the
same problem persists.
Is it a bug or feature?
When Oracle support was asked about it, they reply that
"I require free space equal to double the index in the INDEX TABLESPACE ITSELF."
Loader , in this case does not use my 1GB TEMP tablespace.
To solve the above, I dropped my index and altered my index tablespace with initial 10m, next 10m. Same parameters for the actual index also. Now, everything is going smoothly. No hanging index at the end of the Load. BUT, when I looked into the db_extents, I found that, a temporary segment of 10m is created every time I load the data. The creation of temporary segment is fine but the problem is that they are staying back. I made attempts with checkpoint forcing and forcing redologs. But , no success. When I repeated the load for 9 times, again the loader could not find temp segment space and the index is left in DIRECT load state.(ORA1652). Anybody can suggest a way to get rid of the the TEMPORARY segments created in the index tablespace. I hope that this will solve my original problem. The entry in the dba_segments appears as:
OWNER
SEGMENT_NAME
SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID ----------------- ------------------------------ ---------- ---------- BLOCK_ID BYTES BLOCKS
---------- ---------- ----------
CADRE
19.30722
TEMPORARY TEST_01NDX 0 19
30722 10485760 5120
Thanks in advance.
Sorry for the lengthy posting.
--Mohammed Ghouse
Received on Sun Jul 30 1995 - 00:00:00 CEST