ORA-1652: unable to extend temp segment by 38400 in tablespace

Hi

ORA-1652: unable to extend temp segment by 38400 in tablespace MST.

How to resolve the above error. This error populating very frequently in alert.log. The mentioned tablespace is categorized as permanent tablespace. How to find out which segment is causing the problem. MST tablespace is enough free space.


Thanks in Advance.

One of your sessions must be failing -- which one is getting the error?

Thanks.

None of the sessions failed. Infact no complients from users also. How to trace which one is causing this?

Thanks In Advance

do you use that for temporary tablespace?

do you rebuild index to that tablespace?

do you move tables to that tablespace?

Quote:

---

Originally posted by pando
do you use that for temporary tablespace?

do you rebuild index to that tablespace?

do you move tables to that tablespace?

---

No. Users temporary tablespace designated as TEMP.
No. No rebuilding indexes
No. not moving any tables.

But I extracted one particular query during the error which is

SELECT /*+ ordered USE_NL(PIR_DATES_TXN) USE_NL(b) INDEX(GL_AUDIT_TXN
XFR_GLAUDIT_TXN_REF_NMBR) */ ......

could that be causing?

Thanks In Advance.

It might be helpful to put the following in init.ora and restart the database:

EVENT="1652 TRACE NAME ERRORSTACK LEVEL 3"

This will generate a trace file each time ORA-01652 occurs. The trace file should contain the offending SQL.

Quote:

---

Originally posted by hacketta1
It might be helpful to put the following in init.ora and restart the database:

EVENT="1652 TRACE NAME ERRORSTACK LEVEL 3"

This will generate a trace file each time ORA-01652 occurs. The trace file should contain the offending SQL.

---

I can not restart the database 24/7. I have used
alter system set events '1652 trace name errorstack level 3'. But no trace files have been generated. What could be the problem. Do I need to use dbms_system.set_ev?

Thanks In advance.

select * from dba_segments where next_extent > 38400 * db_block_size

and see what's the segment causing the problem

Any datafile more than 4gb size..
Once I think i had the same problem when one of hte
datafile tried to go above the above limit

Quote:

---

Originally posted by pando
select * from dba_segments where next_extent > 38400 * db_block_size

and see what's the segment causing the problem

---

Thanks pando.
select segment_name,segment_type from dba_segments where next_extent >= (38400 * 8192)

SEGMENT_NAME SEGMENT_TYPE
-------------------------------------------

SYS_IL0000011225C00004$$ LOBINDEX
SYS_IL0000010344C00005$$ LOBINDEX
SYS_LOB0000011225C00004$$ LOBSEGMENT
SYS_LOB0000010344C00005$$ LOBSEGMENT

Thanks In Advance.

better run this

select segment_name,segment_type, tablespace_name, next_extent, bytes
from dba_segments where next_extent >= (38400 * 8192)

Quote:

---

Originally posted by pando
better run this

select segment_name,segment_type, tablespace_name, next_extent, bytes
from dba_segments where next_extent >= (38400 * 8192)

---

Thanks pando. Here is the result.

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE TABLESPACE_NAME NEXT_EXTENT BYTES
------------------ ------------------------------ ----------- ----------
SYS_IL0000011225C00004$$
LOBINDEX CASHIN_MST 314572800 314572800

SYS_IL0000010344C00005$$
LOBINDEX CASHIN_MST 314572800 314572800

SYS_LOB0000011225C00004$$
LOBSEGMENT CASHIN_MST 314572800 314572800

SYS_LOB0000010344C00005$$
LOBSEGMENT CASHIN_MST 314572800 314572800

Thanks In advance

Hi,
Thought just explain that there is nothing much to fear about this error. This error usuaully occurs when some process tries to create a temp segment in a tablespace be it temporary or permanent. WE have to understand that temp segment does not always mean that there is a sort operation involved. During DDL commands also such temp segments are required and hence if such an operation is big, for a particular tablesapce such error occurs. This keeps happening when new objects are created like indexes, table, constraints etc.
Hence see if any user was trying to create a object. The best solution is to coalesce the tablespace and most of the cases the problem will get solved. Else if the operation which failed is essential then add space to the tablespace.
This also happens during index rebuild, table move etc.
This is not a very severe error.
Thanks
Regards