On Wed, 12 Jan 2000, Vergara, Michael (TEM) wrote:

> Hey DBAs - my turn to ask a question!
> 
> We're running SAP on Oracle 8.0.5x.  Our users started getting 
> ORA-01658 (Unable to create initial extent in tablespace TEMP).  A
> quick look at DBA_SEGMENTS showed one huge segment (with 3300+ extents)
> owned by SYS.  The TEMP tablespace is defined as type TEMPORARY.
> 
> What gives?  How come space is not being release to the system?  We
> sure don't want to bounce the instance if we can help it.
> 
> TIA,
> Mike
> 
> ---
> ======================================================================
> Michael P. Vergara     | Be good and you will be lonesome
> Oracle DBA             |                                 Mark Twain
> Guidant Corporation    |

Michael,

The space will be released to the system once the process that is using
TEMP has either finished the SQL statement or disconnects. It can take a
while for this to clear up, all depending on the activity on the database,
the size of the temporary segment, and when the background process for
cleaning up temp segments wakes up.

With 3300+ extents, you may want to:

(A) increase your SORT_AREA_SIZE to handle more of the processing in
memory -and/or-
(B) increase the default INIT / NEXT storage sizes for your TEMP
tablespace.

In either case, you may want to consider increasing the size of the TEMP
tablespace.

I would recommend tracing the SQL that is causing this (look in V$SQL_AREA
and V$SQL_TEXT) to see if you can tune the SQL causing this first.

Best regards,

-Ari Kaplan
350 Oracle Tips: www.arikaplan.com


Back to Ari Kaplan's Home Page