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

Thread: side effects of ORA-1652 unable to extend temporary tablespace

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    Recently I got the following error in my alert.log file

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

    My temproray tablespace is 300M and is Localy managed with uniform extents of 1M each.

    How will this effect the database or the query/statement because of which this error occurred.

    Should I just keep on increasing the tablespace till this error goes away even if the temp tablespace has to be of 10G. I mean how much is enough. How do I find out.

    How to size the temporary tablespace.

    Also is there a way to find out which statement triggered this error?



    Please suggest.

    Thanks

    [Edited by ronnie on 06-13-2002 at 05:28 PM]
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Size requirements are purely based on your requirements.

    Keeping it simple TEMP depends on you Table Sizes.
    Then it varies for what your are doing -- unions, joins, group by, order by ...

    Apart from these temp is used when you create indexes.

    If your are doing complex queries with multiple joins and group by's on tables with millions of rows, then you really need big TEMP.

    I went upto 6 GIG for a TEMP before
    Sridhar R Patnam

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    There are some problems in Using LMTs for TEMP...So I create LMTs only for DATA and INDEX but not TEMP. Anyway LMTs do not support SYSTEM & RBS...

    Yeah! Query depends on TEMP tablespace. Say for example If your query is trying SORT and your SORT_AREA_SIZE is limited it has to use TEMP tablespace for sorting and it can't extend for the space needed you get ORA-1652 error..
    Reddy,Sam

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by sreddy
    There are some problems in Using LMTs for TEMP...So I create LMTs only for DATA and INDEX but not TEMP. Anyway LMTs do not support SYSTEM & RBS...

    Yeah! Query depends on TEMP tablespace. Say for example If your query is trying SORT and your SORT_AREA_SIZE is limited it has to use TEMP tablespace for sorting and it can't extend for the space needed you get ORA-1652 error..
    and how harmful is this error for the database and what what will happen to the statement which triggered it.

    sreddy can you please let us know about the issues with Temo LMT because I recently changed all my TEMP's to LMT's from Dictionary managed.

    Also I have a couple of databases where RBS is LMT and they alsi seem to be working fine. Any thing which I should look for...

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by: sreddy
    There are some problems in Using LMTs for TEMP...So I create LMTs only for DATA and INDEX but not TEMP. Anyway LMTs do not support SYSTEM & RBS...

    Yeah! Query depends on TEMP tablespace. Say for example If your query is trying SORT and your SORT_AREA_SIZE is limited it has to use TEMP tablespace for sorting and it can't extend for the space needed you get ORA-1652 error..
    I too never heard of TEMP having issues with LMT.
    LMT support RBS and from version 9 it supports SYSTEM as well.

    Sanjay

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I should have been little specific about the version I'm talking. Iam talking about LMTs on 8i.

    I had little weird experience on creating TEMP tablespace as LMT... I can't see that tablespace in the list when I query dba-tablespaces view...

    9i LMTs support all types of tablespaces... as SANJAY say. I'm not sure about the oracle version you are talking about ..

    This error is not going to harm your database anyway, if it can't extend TEMP tablespace. Probably your query will not come back OR if someone trying run a complex query this error might get repeated. You don't need to be panic...
    Reddy,Sam

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by sreddy
    I had little weird experience on creating TEMP tablespace as LMT... I can't see that tablespace in the list when I query dba-tablespaces view...
    Well, I see mine when I run select * from dba_tablespaces; :-)
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    LMT support RBS and from version 9 it supports SYSTEM as well.

    Sanjay
    Only from 9.2!
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    LMT RBS is supported since 8i but since it had many bugs.....

  10. #10
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Originally posted by ronnie
    Hi,

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

    [Edited by ronnie on 06-13-2002 at 05:28 PM]
    Have you set MAXSIZE for temporary tablespace?
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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