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

Thread: ORA-1652

  1. #1
    Join Date
    Feb 2001
    Posts
    290
    ORA-1652: unable to extend temp segment by 512 in tablespace TEMP01

    And tablespace TEMP01 is a locally managed one ...

    Its coming very often;

    I would like to figure out whats happeening , would simebody suggets any Vdollors , to look at ???

    ANyway I cant coalesce , since its a locally managed tablespace...

    Suggestions plz
    Madhu

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Datafile for ur TEMP01 tablespace has fixed size and hasn't AUTOEXTEND option.
    All disk space in datafile done and oracle can't allocate extent # 512 in this file.
    U should:

    1) set autoextent option for datafile
    or
    2) add new datafile to TEMP01 tablespace.

  3. #3
    Join Date
    Feb 2001
    Posts
    290
    Size of the tablespace is already 2Gb and i believe its sufficient .. And one more thing I absorved is 99% of my sorts are taking place in memory and not in disk.

    So what i am thinking is when ever oracle needs the disk space for sorting and the sort is so huge ( not necessarily bigger than the tablespace ), oracle is throwing ORA-1652 error.

    Usually i check v$sort_usage and v$sort_segment tables , but couldn't trace anything from it. coz i always see used_blocks are ZERO.

    Is there anyway to check the BIGGEST sort of the day or something like that ????

    Just by increasing the size of the tablespace may solve the problem for the time being , but i suspect something else..

    Any other idea ???
    PS: I am talking about a locally managed temporary tablespace

    --Madhu

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Something is using up all that space. I'd bet that it is being used by sorts or index creation.

    Maybe you are using Global Temporary Tables that have a lot of activity?

    Look at v$sort_usage for the piggies of TEMP usage.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    All users' temporary tablespace must be assigned to temporary tablespace (TEMP). The default value is SYSTEM tablespace.

    Change the type of the TEMP temporary to "TEMPORARY" and NOLOGGING.

    INITIAL and NEXT extent size should be equal to ((N*SORT_AREA_SIZE)+BLOCK_SIZE)


  6. #6
    Join Date
    Feb 2001
    Posts
    290
    Jeff,
    I did check the v$sort_usage , but I couldnt see any user except SYSTEM user doing sorts now.

    And I also queried v$sort_segment, here the values i found

    Total extents : 499
    Total blocks :255488
    Used extents : 58
    used blocks : 29696
    extent hits : 8689
    max sort blocks : 46592

    And to remember you again , 99% of my sorts are on memeory ... May be u may have some picture now with the above details.

    I think the TEMP tablespace is having enough space left over , but still i am getting the error , not on every day but atleast once in a week.

    I would like to arrest this problem.. Is there anyway to check to see the biggest sort in the past ???

    Still I am not clear , how to solve this problem???

    PS: I am not having any Global Temporary tables and just to rememeber this is a locally managed tablespace with nologging option and unform , 4MB

    thanks for your help,
    Madhu


  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    v$sort_usage will only show large segments in TEMP when the process that does alot of sorts is running.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Feb 2001
    Posts
    290
    Is there any way to dig the past sorts ???
    Pl let me know how to diagnose this problem ???

    thanks in advance,
    Madhu

  9. #9
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    May be this query little bit help u solve sort activity problems:

    COLUMN txt FORMAT A50 word
    heading 'SQL text' justify left
    COLUMN username FORMAT A20 heading 'User name' justify left
    COLUMN sid FORMAT 9999 heading 'SID' justify right
    COLUMN s# FORMAT 99999 heading 'Serial' justify right
    COLUMN cur_cnt FORMAT 99999 heading '#|curs' justify right
    COLUMN sort FORMAT 99999 heading 'Disk|sorts' justify right
    COLUMN mem FORMAT 999G999G999 heading 'Total|memory' justify right
    COLUMN exe FORMAT 999G999G999 heading 'Total|exec' justify right
    COLUMN gets FORMAT 99999999 heading 'Blocks|get' justify right
    COLUMN reads FORMAT 99999999 heading 'Blocks|read' justify right
    COLUMN prows FORMAT 99999999 heading '#|rows' justify right
    COLUMN hit FORMAT A24
    heading 'Hit ratio' justify left

    select
    s.sid sid,
    s.serial# s#,
    s.username username,
    a.sql_text txt,
    count(c.sid) cur_cnt,
    sum(sharable_mem)+sum(runtime_mem) mem,
    sum(executions) exe ,
    decode(sum(buffer_gets),0,'0',
    ltrim(to_char(100-(sum(disk_reads)/(sum(disk_reads)+sum(buffer_gets))*100),'990D99'))||'% ('||
    ltrim(to_char(sum(disk_reads),'99999999999'))||'/'||
    ltrim(to_char(sum(buffer_gets),'999999999999'))||')') hit,
    sum(sorts) sort,
    sum(rows_processed) prows
    from v$session s,
    v$open_cursor c,
    v$sqlarea a
    where s.SQL_ADDRESS=c.ADDRESS(+) and
    s.username is not null and
    s.SQL_ADDRESS=a.ADDRESS and
    s.username not in ('SYSTEM','SYS')
    group by s.sid,
    s.serial#,
    s.username,
    s.SQL_ADDRESS,
    c.ADDRESS,
    a.sql_text
    order by 3,1;

    COLUMN txt clear
    COLUMN username clear
    COLUMN sid clear
    COLUMN s# clear
    COLUMN cur_cnt clear
    COLUMN sort clear
    COLUMN mem clear
    COLUMN exe clear
    COLUMN gets clear
    COLUMN reads clear
    COLUMN prows clear
    COLUMN hit clear


  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    add event in your init.ora so there will be trace file to be diagnoticed

    event = "1652 trace name errorstack level 12"

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