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

Thread: ORA-1652 unable to extend temp segment

  1. #1
    Join Date
    Jun 2005
    Posts
    17

    ORA-1652 unable to extend temp segment

    how can i handle this problem?

  2. #2
    Join Date
    Sep 2002
    Posts
    376
    Tune your query so that it uses less temporary space......

  3. #3
    Join Date
    Jun 2005
    Posts
    17
    Thanks bang dba... any other solutions rather than tuning my query statements? thanks!!!

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    make the temp tablespace bigger (resizing a datafile or adding one) - then tune your queries and look at memory settings

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by Harry_Potter
    any other solutions rather than tuning my query statements?
    Wave your magic wand?
    Jeff Hunter

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    set sort_area_size=200000000;

    and then try your query also tuning is very important.

    regards
    anandkl
    anandkl

  7. #7
    Join Date
    Feb 2004
    Location
    Russia
    Posts
    13
    Quote Originally Posted by Harry_Potter
    how can i handle this problem?
    Code:
    SQL*Plus: Release 9.2.0.5.0 - Production on Fri Oct 1 21:58:01 2004
    
    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
    
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    
    SQL> create tablespace ttt datafile 'd:\ttt.ora' size 512k uniform size 64k;
    
    Tablespace created.
    
    SQL> create table t tablespace usr as select * from all_objects;
    
    Table created.
    
    SQL> select sum(vsize(owner))+ sum(vsize(object_name))+ sum(vsize(object_id))+ sum(vsize(object_type)) from t;
    
    SUM(VSIZE(OWNER))+SUM(VSIZE(OBJECT_NAME))+SUM(VSIZE(OBJECT_ID))+SUM(VSIZE(OBJECT
    --------------------------------------------------------------------------------
    1049895
    
    SQL> create index t_i on t(owner,object_name,object_id,object_type) tablespace ttt;
    create index t_i on t(owner,object_name,object_id,object_type) tablespace ttt
    *
    ERROR at line 1:
    ORA-01652: unable to extend temp segment by 16 in tablespace TTT
    
    SQL> show parameter db_block_size
    
    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size integer 4096
    SQL>

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    your exmaple doesnt show him how to fix it though des it

  9. #9
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    Quote Originally Posted by davey23uk
    make the temp tablespace bigger (resizing a datafile or adding one) - then tune your queries and look at memory settings

    Temp segment is not in the 'TEMP' tablespace. It has to do with freespace available in the tablespace that is listed (not given in the original post). Extend the tablespace you are building the object in.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    true, good spot - but it might have been in the temporary tablespace, so i might be right in the end

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