Home » RDBMS Server » Server Administration » ora-01652 (Oracle 9i)
ora-01652 [message #341358] Mon, 18 August 2008 09:27 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear sir

I am getting the below error in the user while trying to add a hint or creating any object

ORA-01652-UNABLE TO EXTEND temp segment by 128 in the tablespace temp

While checking at the dba_data_files
select tablespace_name,next_extent,max_extents from dba_tablespaces where tablespace_name='TEMP';

TABLESPACE_NAME                NEXT_EXTENT MAX_EXTENTS
------------------------------ ----------- -----------
TEMP                               1048576 



select tablespace_name,extent_size,total_extents,total_blocks,max_size,max_blocks from
  2  v$sort_segment;

TABLESPACE_NAME                 EXTENT_SIZE TOTAL_EXTENTS TOTAL_BLOCKS   MAX_SIZE MAX_BLOCKS
------------------------------- ----------- ------------- ------------ ---------- ----------
TEMP                                    128         12288      1572864      12288    1572864


Please help what can be done to resolve the below issue

Regards
Re: ora-01652 [message #341367 is a reply to message #341358] Mon, 18 August 2008 10:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
just search the forum before posting.
This is been handled many times.
Re: ora-01652 [message #341370 is a reply to message #341358] Mon, 18 August 2008 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also try to post in appropriate forum instead of randomly picking one.

Regards
Michel
Re: ora-01652 [message #341763 is a reply to message #341370] Wed, 20 August 2008 04:12 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Now the problem here is while taking the backup also it is giving me the below error


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user IRS2007_cis_C3 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user IRS2007_cis_C3 
About to export IRS2007_cis_C3's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 1652 encountered
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-06512: at "SYS.DBMS_LOB", line 424
ORA-06512: at "SYS.DBMS_METADATA", line 1140
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully


Any help would help to resolve the below issue

Regards
Re: ora-01652 [message #341766 is a reply to message #341358] Wed, 20 August 2008 04:21 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

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

Did you still search for above error on forums?

Re: ora-01652 [message #341776 is a reply to message #341766] Wed, 20 August 2008 05:04 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

As per the forum which is written it is written that this error comes because there is no space available in the temporary segment. The second cause of ORA-01652 may have to do with the local temp segment not being able to extent space even though there is space in other instances.

But when I fire this command
select sum(free_blocks)/1024/1024 ||' size as MB '
from v$sort_segment
WHERE TABLESPACE_NAME='TEMP'
SUM(FREE_BLOCKS)/1024/1024
-----------------------
1.5 SIZE AS MB

But I can see that my tablespace is getting autoextend depends on the size of the data

Also in my alert log file I am getting the below error

Do you wanted me to add some datafiles in the temp tablespace or there is any other alternative?

Regards

[Updated on: Wed, 20 August 2008 05:04]

Report message to a moderator

Re: ora-01652 [message #342330 is a reply to message #341776] Fri, 22 August 2008 01:15 Go to previous messageGo to next message
icdb023
Messages: 2
Registered: August 2008
Junior Member
The message means that you have not enough space in the TEMP tablespace to satisfy your export request; if you want that export to succeed you'd better add some datafiles to your temp tablespace.
Re: ora-01652 [message #342588 is a reply to message #342330] Fri, 22 August 2008 23:46 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

you'd better add some datafiles to your temp tablespace.

NOT datafile, Only TEMPFILE for temporary tablespace.
Previous Topic: New instance of OracleXE
Next Topic: Rebuild and Move index to another Tablespace
Goto Forum:
  


Current Time: Sun Dec 04 16:46:05 CST 2016

Total time taken to generate the page: 0.06112 seconds