Unable to extend temp segment.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Unable to extend temp segment.

  1. #1
    Join Date
    Sep 2000
    Posts
    362

    Arrow

    Hi,
    When I do a analuze compute statistics Oracle gives me unable to extend temporary segment.

    SQL> analyze table test compute statistics;
    analyze table test compute statistics
    *
    ERROR at line 1:
    ORA-01652: unable to extend temp segment by 27309 in tablespace TEMPORARY_DATA


    But when I see the statistics from enterprise manager it shows that the size of the tablespace is 102 MB and used is 0.002 MB. If it hasnt reached its maximum limit why is it giving me unable to extend error.

    I also queried to confirm the results of enterpise manager. Below is the output of the query.

    select
    2 b.file_id "file#",
    3 b.tablespace_name "Tablespace name",
    4 b.bytes "#Bytes",
    5 (b.bytes-sum(nvl(a.bytes,0)))"#used",
    6 sum(nvl(a.bytes,0))"#free",
    7 (sum(nvl(a.bytes,0))/(b.bytes))*100"%Free"
    8 from sys.dba_free_space a,sys.dba_data_files b
    9 where a.file_id(+)=b.file_id
    10 group by b.tablespace_name ,b.file_id,b.bytes
    11 order by b.tablespace_name;

    file# Tablespace name #Bytes #used #free %Free
    --------- ------------------------------ --------- --------- --------- ---------
    7 REC_DATA 104857600 1036288 103821312 99.011719
    3 ROLLBACK_DATA 10485760 4917248 5568512 53.105469
    1 SYSTEM 62914560 60721152 2193408 3.4863281
    6 TEMP 52428800 2048 52426752 99.996094
    4 TEMPORARY_DATA 112197632 2048 112195584 99.998175
    5 USERS 377657344 377647104 10240 .00271145
    2 USER_DATA 3145728 1753088 1392640 44.270833

    Please suggest.

    Thanks
    Anurag

    [Edited by anuragmin on 05-30-2001 at 11:20 AM]

  2. #2
    Join Date
    Jul 2000
    Posts
    243
    Hi

    do you have a temporary tablespace assigned to your user? check your user temp table space

  3. #3
    Join Date
    Jul 2000
    Posts
    243
    sorry , whant i ment to say is check you user temp table space, it may not be the temp you think it is.

  4. #4
    Join Date
    Sep 2000
    Posts
    362
    I checked it.

    The default tablespace is users and the pemporary tablespace is temporary_data.

    Thanks
    Anurag

  5. #5
    Join Date
    Dec 1999
    Posts
    217
    Check the size for the next extent in the storage parameters for the temporary tblspace. This error also occurs if the next extent is too small.

    Chintz

  6. #6
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    These are the storage parameters for the temporary Tablespace.

    Initial Extent : 10KB
    Next Extent: 10KB
    pct Increase: 50 %
    Maximum Extents 121

    The table which I am trying to analyze here has more than 6 million records.

    Thanks
    Anurag


  7. #7
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Whoa!! Initial and next are too small. Increase them to atleast 1M each. (Extent allocation is CPU intensive). Increase the size of the ts.

  8. #8
    Join Date
    Sep 2000
    Posts
    362
    Hi Halo,

    Is it ok to have pctincrease set to 50% for a temorary tablepsace.

    Thanks
    Anurag

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    pctincrease shold be 0 in TEMP tablespace so you ensure that initial = next, inital and next should be multiple of sort_area_size to improve I/O

    anyway instead of check free space with OEM try this query

    select sum(bytes) from dba_free_space where tablespace_name='TEMPORARY_DATA'

  10. #10
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    I wouldn't quite do that.

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