>Hello,
>I try to realize a query on my oracle base.
>This query is made of 4 joins concerning about 10 000 tuples.
>The answer is about 10 tuples.
>Unfortunately, I receive the following answer :
>"ORA-01658: unable to create INITIAL extent for segment in tablespace
>TEMP"
>I feel quite uncomfortable, as it wasn't happening in the beginning
>of the day...
>I have been testing some proC programs, and sometimes I had to kill
>them. Could it have polluted the temp file?
>Is there anyway to clean it or enlarge it?
>I must tell you that our DBA is on holidays...
>All help Welcome!!!!
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>~ Matthieu EXBRAYAT | ~
>~ LISI - INSA Lyon | e-mail : exbrayat@lisiflory.insa-lyon.fr
>~ 69621 Villeurbanne Cedex |
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Newsgroups: comp.databases.oracle.server
Subject: Re: Help ! problems with temp file
References: <32ECD18A.41C67EA6@lisiflory.insa-lyon.fr>
Matthieu,
What is happening is Oracle cannot handle processing the SQL statement in
memory (the size defined by the SORT_AREA_SIZE initialization parameter),so
it is using the TEMP tablespace. Oracle will create a temporary object
similar in theory to swapping for an operating system, and then clean in
up when finished with the SQL.
What is happening to you is that Oracle cannot find space in your TEMP
tablespace to even begin writing the temporary segment. This could be
fixed with two methods.
Look at the default INITIAL EXTENT storage option for your TEMP
tablespace:
select initial_extent, next_extent, tablespace_name from user_tablespaces
where tabelspace_name = 'TEMP';
The above will tell you the INITIAL and NEXT for the temporary segments
that Oracle will need to create. Next you need to find out how much free
space is left:
select sum(bytes) from dba_free_space where tablespace_name = 'TEMP';
The problem you are having is that the INITIAL is more than the free
space.
Two options:
1) Reduce the size of the INITIAL (and possibly NEXT) for the tablespace:
alter tablespace temp default storage (INITIAL 1M NEXT 1M);
Please substitute "1M" for something smaller than the free space found
above...maybe 20% of the free space. Choose this option initially and see
if your problem is resolved. If not, try...
2) Increase the size of the tablespace:
alter tablespace TEMP add datafile '/disk1/oracle/temp_02.dbf' size 200M;
You will need to see what size is appropriate and what directory to put
the file in. A good size if you can fit it on disk is the size of the
previous TEMP tablespace. The path and name in the above statement needs
to be changed to whatever the standard at your site is.
Good luck....
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For more Oracle tips, visit my Web Page: <->
<-> www.arikaplan.com <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page