Home » SQL & PL/SQL » SQL & PL/SQL » Unable to extend temp segment on tablespace (Oracle 10g)
Unable to extend temp segment on tablespace [message #289773] Wed, 26 December 2007 02:02 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

Could you please help me out on this?

A developer is getting the above SQL Exception on one of the databases used here.

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

However, when I checked out, I could not find any tablespace named MA_TMP.

For e.g. select * from dba_data_files where tablespace_name = 'MA_TMP'; returns no records.

How is this possible?

Also, could someone help me out on how to extend the tablespace?

Showstopper issue here, I would appreciate if someone could respond at the earliest.

Thanks,

Sharath
Re: Unable to extend temp segment on tablespace [message #289774 is a reply to message #289773] Wed, 26 December 2007 02:07 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Your getting error in temp segment...
ORA-01652: unable to extend temp segment by 128 in tablespace MA_TMP

and you check in data view

e.g. select * from dba_data_files where tablespace_name = 'MA_TMP'; returns no records.

check in dba_temp_files
Re: Unable to extend temp segment on tablespace [message #289776 is a reply to message #289774] Wed, 26 December 2007 02:17 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Mohammad Taj wrote on Wed, 26 December 2007 13:37

Your getting error in temp segment...
ORA-01652: unable to extend temp segment by 128 in tablespace MA_TMP

and you check in data view

e.g. select * from dba_data_files where tablespace_name = 'MA_TMP'; returns no records.

check in dba_temp_files


Thanks a lot for the reply.

Could you please also tell me how to fix this error?

This is the information from dba_temp_files.

FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ----------
2 MA_TMP 5242880 640 AVAILABLE
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ----------
1 NO 0 0 0 4194304 512

Thanks,

Sharath
Re: Unable to extend temp segment on tablespace [message #289780 is a reply to message #289776] Wed, 26 December 2007 02:22 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Did you search above error no. on forms or google.

just search you will get complete solution.
Re: Unable to extend temp segment on tablespace [message #289782 is a reply to message #289776] Wed, 26 December 2007 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To know how to fix we must know what's going on at the error time.

Anyway, to fix that you either have to enlarge the tablespace or do less work that requires temporary space.
5MB is very very tiny.

Also, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.


Regards
Michel
Re: Unable to extend temp segment on tablespace [message #289795 is a reply to message #289782] Wed, 26 December 2007 02:52 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Michel Cadot wrote on Wed, 26 December 2007 13:54

To know how to fix we must know what's going on at the error time.

Anyway, to fix that you either have to enlarge the tablespace or do less work that requires temporary space.
5MB is very very tiny.

Also, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.


Regards
Michel



Hi Michel,

Could you please tell me how to enlarge the tablespace?

Do I have to create a new datafile?

If so, how do i do it?

I am a fresher who has chosen to start off his career as a dba. I would appreciate if you could help.

Thanks,

Sharath

Re: Unable to extend temp segment on tablespace [message #289802 is a reply to message #289782] Wed, 26 December 2007 03:10 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hey Michel,

I increased the size of the data file and the issue is fixed.

Thanks,

Sharath
Re: Unable to extend temp segment on tablespace [message #289804 is a reply to message #289802] Wed, 26 December 2007 03:14 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>I increased the size of the data file and the issue is fixed.
Not datafile, you increased the size of TEMPFILE.
Re: Unable to extend temp segment on tablespace [message #289805 is a reply to message #289804] Wed, 26 December 2007 03:17 Go to previous message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Mohammad Taj wrote on Wed, 26 December 2007 14:44

>I increased the size of the data file and the issue is fixed.
Not datafile, you increased the size of TEMPFILE.



lol... yeah.. tempfile... hehe Smile
Previous Topic: About the query...
Next Topic: Created function granted to scott but not working
Goto Forum:
  


Current Time: Tue Dec 06 02:47:14 CST 2016

Total time taken to generate the page: 0.11208 seconds