Unable to extend temp segment

Hi,

I am using Oracle 8.0.....

I am getting this error...
Microsoft OLE DB Provider for Oracle error '80004005'
ORA-01652: unable to extend temp segment by 40964 in tablespace TEMPORARY_DATA

I could not able to find temp segment in tablespace TEMPORARY_DATA

Where can I find this "temp segment " and how to increase memory.


Thnx in advance,

LVL 8
rk_radhakrishnaAsked:
Who is Participating?
 
MohanKNairConnect With a Mentor Commented:
It is not possible to view temp segment.

>> ORA-01652: unable to extend temp segment by 40964 in tablespace TEMPORARY_DATA
1) Increase the size of the tablespace TEMPORARY_DATA by adding more datafiles
2) Tune the SQL query to use indexes
3) Optimize join criteria

0
 
sathya_sCommented:
Enable the Automatically extend when database is full option for TEMPORARY_DATA

Regards,
Sathya
0
 
rk_radhakrishnaAuthor Commented:
>>Enable the Automatically extend when database is full option for TEMPORARY_DATA

In TEMPORARY_DATA table space there are two data files:

1) TMP1ORACL.ORA   size -- 75MB   increased to   150 MB
2) ORATEMP                    -- 65MB    increased to  150 MB

Still I am getting error this time segment memory has been increased

                 ORA-01652: unable to extend temp segment by 61224 in tablespace TEMPORARY_DATA



Thanx
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
MohanKNairCommented:
What is the total DB size? What about the tables involved in the Query? A value of 1GB for temporary tablespace is normal
0
 
rk_radhakrishnaAuthor Commented:
>> What is the total DB size?
 9.49 GB

>> What about the tables involved in the Query?
I dont know the exact memory for tables which I used in the query [Frankley I dont know, how to check]
But I know tablespace memory (Its consits more than 40 tables including tables in query) --- > 500542 KB


>>A value of 1GB for temporary tablespace is normal

Is TEMPORARY_DATA different from temporary tablespace [[remember I am using Oracle 8.0.6.0.0]]


To check and increase sizes of tablespaces I used Oracle Storage manager

It contains 3 directories
 1) Tablespaces
 2) Datafiles
 3) Rollback segments


Thnx
Radhakrishna
0
 
MohanKNairCommented:
>> Is TEMPORARY_DATA different from temporary tablespace

TEMPORARY_DATA may be the name of the temporary tablespace.

>> remember I am using Oracle 8.0.6.0.0
Change the default storage parameters for temporary tablespace
SQL> alter tablespace TEMPORARY_DATA default storage(initial 2564K next 256K pctincrease 0 minextents 1 maxextents unlimited);
0
 
rk_radhakrishnaAuthor Commented:
>> alter tablespace TEMPORARY_DATA default storage(initial 2564K next 256K pctincrease 0 minextents 1 maxextents unlimited);

I alterted using above command,  got same error but it error memory is decreased to 128

ORA-01652: unable to extend temp segment by 128 in tablespace TEMPORARY_DATA


Thnx
0
 
MohanKNairCommented:
Try this command

SQL> alter tablespace TEMPORARY_DATA default storage(initial 64K next 64K pctincrease 0 minextents 1 maxextents unlimited);
0
 
gvsbnarayanaCommented:
Hi,
  Just a doubt... Is it advisable to set maxextents unlimited for temporary tablespace?
Thanks and Regards,
Badri.
0
 
rk_radhakrishnaAuthor Commented:
thnx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.