Newsgroups: comp.databases.oracle
Subject: Re: Error - ORA-01652
References: <317D95E9.56D0@mail.albany.net>

Steve Rentz  writes:

>	I have just started working with Oracle (two days ago).  The 
>error ORA-01652: unable to extend temp segment by 5412 in tablespace 
>TEMPJCREW, was occuring before I started, and I am wondering what it 
>means, as I can not find it in any of the manuals.

>	I am really an application programmer, but in my new position I 
>am currently also acting as the DBA.  We are using version 7.1 on Solaris 
>2.5.  The error occurs after doing a rather large select.

>	We have the following tablespace set up:

>create tablespace tempjcrew     datafile 'temp01.tab' size 2000m;
>alter  tablespace tempjcrew add datafile 'temp02.tab' size 1000m;
>alter  tablespace tempjcrew add datafile 'temp03.tab' size 500m;
>alter  tablespace tempjcrew add datafile 'temp04.tab' size 500m;
>alter  tablespace tempjcrew default storage (pctincrease 1 next 10m);

>temp01.tab and temp02.tab are on separate disks.
>From what I have read, the pctincrease has no affect in version 7.

>We are selecting approximately 20 million records.  Are we just running 
>out of space.  Do we need to create another tablespace?
>Any advice is appreciated.



I want to wish you good luck on your new DBA role. There is much to learn, and
two days is not enough to get up to speed. This message should hopefully 
help get you going.

First of all, make sure that the user has 'TEMPJCREW' as the TEMP 
tablespace in the user profile. To do this, type:


Your problem could be as simple as not having this set, and the 
processing of the 20 million records is taking place in another smaller 
tablespace (SYSTEM, perhaps?)

Also, the pctincrease DOES have an effect in Oracle7. You have it set 
to 1. I would recommend setting it to a more even number, such as 0 or 
100. This will depend on how many extents you expect, how large your 
smallest datafile is (500m in your case), and so on. I would recommend 
starting at pctincrease 0. If it turns out that you run out of extents, then 
simply make the default INITIAL extent for the tempjcrew tablespace larger.

To find out if you DO need more space in the "tempjcrew" tablespace, 
issue the following statement while running the 20 million record query, 
preferably several times during the execution and right before the 
ORA-1652 error:

SELECT b.file_name, a.tablespace_name, a.bytes, a.blocks
FROM dba_free_space a, dba_data_files b
WHERE a.file_id = b.file_id
AND a.tablespace_name = 'TEMPJCREW';

The above query will tell you how much contiguous free space is contained 
on each datafile. If the free space keeps declining during your query, 
and eventually runs out of space, then you can conclude that you need to add
another datafile to the "tempjcrew" tablespace.
Do not add another tablespace as you suggested, because the new tablespace 
will not be used in the query. Instead, add yet another datafile to your 
"tempjcrew" tablespace.

Good luck.

-Ari Kaplan
Independent Oracle DBA Consultant

<-> Visit my Web Page: www.arikaplan.com                          <->
<->             email: akaplan@interaccess.com                    <->

Back to Ari Kaplan's Home Page if" width="16" height="18">Back to Ari Kaplan's Home Page