| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORACLE Import
Oscar Ssentoogo <impala_at_enterprise.net> wrote . . .
> I am trying to import loads of records to an ORacle table but keep having
> the error (in .log file)
> ' ORA-01631: max # extents (50) reached in table TRAINS_DATA
> LOAD discontinued'
>
> The maxextents parameter is set to 50 for this table. Can I set
Maxextents
> to unlimited?
>
> Is there a problem with the size of the table itself or INITIAL etc
> settings? What about PCTINCREASE?
>
> Here is the create table statement:
>
> CREATE TABLESPACE boxsheet
> DATAFILE '/oradata/boxt/box_data' size 100M
> DEFAULT STORAGE (
> INITIAL 50K
> NEXT 50K
> MINEXTENTS 2
> MAXEXTENTS 50
> PCTINCREASE 0)
> ONLINE;
Actually, that is the CREATE TABLESPACE command, not the CREATE TABLE
command. But it is relevant in that unless you explicitly specify storage
parameters in your CREATE TABLE statement, the tablespace defaults defined
above will apply.
The problem is with your number and size of extents. Set the way you have it above, you are limiting the table to 50 extents of 50 kb each, or only 2.5 MB, hardly enough to contains "loads of data". Your PCTINCREASE value of 0 is fine.
The good news is that you have several options. You can explicitly set storage parameters for this table by pre-creating it and then loading the data, or change the defaults for the tablespace. Choose bigger initial and next values, and set maxextents to either UNLIMITED (if you're on 7.3) or to as high as you can go (depends on your blocksize) in earlier versions, i.e., 121 for 2k blocks, 249 for 4k blocks, 515? for 8k blocks. Actually, set it a bit lower than the max so that if it STILL runs out of space you can allocate another large extent (using ALTER TABLE XXX ALLOCATE EXTENT).
Or you can empty the table now, change NEXT to a larger value, and change MAXEXTENTS to a large value as described above. Make some space calculations based on the size of the import dmp file so you'll know how much to allocate.
Have fun!
Chris
 |
 |