Re: oracle error 1658

when oracle 'extends' a datafile, it takes time to allocate the new extent and prepare the blocks in the new extent... the performance effect of autoexetend is that the process that needs the space must wait while oracle performs (in effect) an " alter datafile ... resize " statement.

a good dba can monitor the tablespaces, and can make more space available, either by resizing the existing datafiles, adding an additional file to the tablespace, or possibly relocating objects to other tablespaces, and can schedule this at a time when the database will be less busy.

with 'autoextend', oracle will allocate additional space, as it is needed, exactly when it is needed. and this is most likely to be when oracle is already busy with a process that is inserting lots of rows, and that process will end up having to wait. that's the immediate performance impact.

btw... be careful with the 'COMPRESS' option of export.. by the keyword you might think that this is going to save space on the generated export file... but it does _not_ have this effect. rather, it tells oracle that when a table is Asubsequently imported (and does not already exist) that the previous INITIAL extent size of table will be ignored, and instead, the import will attempt to allocate all of the required space for a table in a single large INITIAL extent. This can present a problem for large tables. For example, consider a table that over 800MB, being imported into a tablespace with two datafiles of 500MB each. Even with nothing else in the tablespace, there is not enough contiguous free space to allocate an required INITIAL extent of 800MB.

HTH "Erika Grondzakova" <Erika.Grondzakova_at_cern.ch> wrote in message news:39CA281E.2040ACDB_at_cern.ch...
> Hello,
>
> Why opt No.1 would mean a lost of performance in comparison with opt
> No.2 ?
>
> Thank in advance,
>
> Erika
>
>
> "Howard J. Rogers" wrote:
> >
> > Failure to create the *initial* extent is almost always a sign that the
> > tablespace involved is too small. Make it bigger by the three 'approved'
> > methods:
> >
> > 1. Alter database datafile 'existing_datafile_path_&_filename' autoextend
> > on'; (not recommended)
> > 2. Alter database datafile 'existing_datafile_path_&_filename' resize Xm
> > (make it bigger -that's OK)
> > 3. Alter tablespace X add datafile 'newone' size Xm (add new
> > datafile -recommended within reason)
> >
> > Too many datafiles mean checkpoints take longer; too big datafiles mean
> > backups and recoveries take longer. Strike a balance between 2 and 3. And
> > opt for No.1 if you suffer from 'lazy DBA syndrome', and want Oracle to dig
> > you out of holes (at the cost of performance).
> >
> > Regards
> > HJR
> >
> > --
> > --------------------------------------------------------------------------
> > Opinions expressed are my own, and not those of Oracle Corporation
> > Oracle DBA Resources: http://www.geocities.com/howardjr2000
> > --------------------------------------------------------------------------
> >
> > <marima_at_my-deja.com> wrote in message news:8pm2qe$bjg$1_at_nnrp1.deja.com...
> > > i am importing tables and i get error 1658
> > > unable to create initial extent for segment in tablespace user_data
> > > how do i fix this
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
>
Received on Thu Sep 21 2000 - 00:00:00 CDT