DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323

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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    One of your sessions must be failing -- which one is getting the error?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Thanks.

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

    Thanks In Advance
    Nagesh

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    do you use that for temporary tablespace?

    do you rebuild index to that tablespace?

    do you move tables to that tablespace?

  5. #5
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    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.
    Nagesh

  6. #6
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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.

  7. #7
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    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.
    Nagesh

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select * from dba_segments where next_extent > 38400 * db_block_size

    and see what's the segment causing the problem

  9. #9
    Join Date
    Apr 2003
    Posts
    353
    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

  10. #10
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    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.
    Nagesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width