Re: ORACLE Import

Oscar,

First, if the table already exists and you are using the IGNORE=Y clause of the import command, then the default tablespace storage parameters will not be used. Otherwise, it looks like the table will be 50k initial, 50k next. Since you are running out of extents at 50, your table will be at least 50k*50 = 2.5M, if not more. So, change the storage parameters to 3M initial 3M next and try again. You should create the table with the appropriate size and no data, then import with IGNORE=Y

You can set the MAXEXTENTS to UNLIMITED if you have Oracle 7.2 or higher, but having lots of extents is bad for performance and maintenance.

You could also export the table with the COMPRESS=Y option. That way, when you import the table, the INITIAL extent will sufficient for the entire table.

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 105+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://homepage.interaccess.com/~akaplan          <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

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;
:
: Thanks
:
: Oscar
:
:
:
  Received on Wed Sep 03 1997 - 00:00:00 CDT