APRIL 24
Newsgroups: comp.databases.oracle
Subject: Re: Error - ORA-01652
References: <317D95E9.56D0@mail.albany.net>
Steve Rentz writes:
>Hello,
> 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.
>Steve
Steve,
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:
ALTER USER username TEMPORARY TABLESPACE tempjcrew;
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