Home » RDBMS Server » Server Utilities » Loading 3,500,000 records
Loading 3,500,000 records [message #187605] Mon, 14 August 2006 12:02 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Hello:

I'm loading about 3,500,000 records in a table with sql loader, but when it loads about 100,000 the load fails with the follow message error:

ORA-01632: max # extents (505) reached in index DWH_MTP.DM08_PK_PERSONA

SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

Specify SKIP=101991 when continuing the load.

Table "DM08_PERSONA":
101991 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

I did:

alter table DM08_PERSONA storage (maxextents unlimited);

But I'm still having the error... Does anyone know why? My options are ok?

They are:
OPTIONS ( ERRORS=500, BINDSIZE=1000000, ROWS=1000000, READSIZE=1000000)


Thanks
Re: Loading 3,500,000 records [message #187606 is a reply to message #187605] Mon, 14 August 2006 12:14 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
superoscarin@hotmail.com wrote on Mon, 14 August 2006 13:02

Hello:
ORA-01632: max # extents (505) reached in index DWH_MTP.DM08_PK_PERSONA


I did:

alter table DM08_PERSONA storage (maxextents unlimited);

But I'm still having the error... Does anyone know why? My options are ok?



But the error says maximum number of extents reached in INDEX, not TABLE.
Re: Loading 3,500,000 records [message #187607 is a reply to message #187606] Mon, 14 August 2006 12:24 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Ups:

The syntax should be something like that??

alter index DWH_MTP.DM08_PK_PERSONA storage (maxtextents unlimited);

Thanks Joy
Re: Loading 3,500,000 records [message #187608 is a reply to message #187607] Mon, 14 August 2006 12:27 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, exactly. Sorry, I thought I typed that in.
Re: Loading 3,500,000 records [message #187609 is a reply to message #187608] Mon, 14 August 2006 12:33 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

thanks Joy: only that in my last post write maxtextents... The alter is this...

alter index DWH_MTP.DM08_PK_PERSONA storage (maxextents unlimited)
Re: Loading 3,500,000 records [message #190678 is a reply to message #187605] Thu, 31 August 2006 12:57 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hello:

I'm loading about 3,500,000 records in a table with sql loader, but when it loads about 100,000 the load fails with the follow message error:

ORA-01632: max # extents (505) reached in index DWH_MTP.DM08_PK_PERSONA

SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

Specify SKIP=101991 when continuing the load.

Table "DM08_PERSONA":
101991 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

I did:

alter table DM08_PERSONA storage (maxextents unlimited);

But I'm still having the error... Does anyone know why? My options are ok?

They are:
OPTIONS ( ERRORS=500, BINDSIZE=1000000, ROWS=1000000, READSIZE=1000000)

or

options(target=true)

It will load all the data from flat file






Thanks
Re: Loading 3,500,000 records [message #190766 is a reply to message #190678] Fri, 01 September 2006 02:56 Go to previous message
aorehek
Messages: 52
Registered: August 2006
Member
Look at the extent number in your tablespace definition.
Previous Topic: IMPORT
Next Topic: to accept the column which has newline character into the table using sql loader
Goto Forum:
  


Current Time: Sat Dec 10 14:26:56 CST 2016

Total time taken to generate the page: 0.10456 seconds