Re: ora-1652 Help Please

Yes and no.
In 7.3 and higher you can resize a datafile (alter database datafile '<filename> resize <xxx>M) and that should be preferred above adding just another file. The strange thing though is that if your initial extent is 10M and your next extent is 10M, and max extents is 121, this means that tablespace is evidently too small. In an ideal world the initial extent, next extent and max extents should be calculated so that all space (minus the header block of course) is used up. 10M initial looks like a bit too big, especially for sorts that just exceed sort_area_size.
I usually start at 1M, and tune it later. You may want to consider the tablespace a temporary tablespace (alter tablespace temp temporary) at the same time, this make sure temporary segments are allocated in the same manner as rollback segments.

Hth,

Sybrand Bakker, Oracle DBA

<hooch432_at_my-deja.com> wrote in message news:7nl511$m6k$1_at_nnrp1.deja.com...
> I have someone who is running a very large report (approx. 26hrs) and
> about 5 hours into the report around 14:00 they received the error ORA-
> 01089: immediate shutdown in progress. I know you get this when the
> database is not available. However in the alert log about the same
> time I had the error ORA-1652: UNABLE TO EXTEND TEMP SEGMENT BY 2560 IN
> TABLESPACE TEMP. I don't see anything about a shutdown until the next
> morning about 7am. Why wouldn't the user see the ORA-1652 instead of
> seeing the ORA-01089: SHUTDOWN error when the database was not
> unavailable at the this time?
>
> I have been told to solve the ORA-1652 error is to add an additional
> datafile to the TEMP tablespace is this correct? I seem to have some
> fragmentation on the TEMP tablespace (TOAD) : Total Blocks=140800,
> Empty Blocks=140799, # pieces=55, Large=2560 (same # that is in the
> error), Avg.=2560, Sm=2559.
> TEMP tablespace size: 550MB, Inital Ext.=10485760, Next=10485760, Max
> Ext=121, and %increase=0.
>
> Will adding a datafile solve my problem?
> Oracle v.7334 OpenVMS 7.1 (DBA is on vacation!)
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Jul 27 1999 - 16:39:51 CDT