ora-01652 [message #341358] |
Mon, 18 August 2008 09:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #341763 is a reply to message #341370] |
Wed, 20 August 2008 04:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #341776 is a reply to message #341766] |
Wed, 20 August 2008 05:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
Mohammad Taj
Messages: 2412 Registered: September 2006 Location: Dubai, UAE
|
Senior Member |
![star_taj%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
you'd better add some datafiles to your temp tablespace.
NOT datafile, Only TEMPFILE for temporary tablespace.
|
|
|