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

[gnagesh]

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.

[slimdave]

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

[gnagesh]

Thanks.

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

Thanks In Advance

[pando]

do you use that for temporary tablespace?

do you rebuild index to that tablespace?

do you move tables to that tablespace?

[gnagesh]

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.

[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.

[gnagesh]

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.

[pando]

select * from dba_segments where next_extent > 38400 * db_block_size

and see what's the segment causing the problem

[engiri]

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

[gnagesh]

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.