Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Unable to extend TEMP tablespace

    Hi ,

    This type of query has run previously in the database . But now this query is getting the error message . We have 4 CPU's . I can't increse TEMP tablespace(not authorized to do this) . Is there any other solution except to increse the TEMP tablespace ? Though I've mentioned the tablespace IM2_D for the table , still it's refering TEMP tablespace , most probably for HASH partition .

    SQL> create table ph_extract_tmp1 parallel 32 nologging
    PARTITION BY HASH (AOL_INDV_ID)
    PARTITIONS 32 STORE IN (IM2_D)
    as select /*+ PARALLEL(a,16) PARALLEL(b,16) */ a.*
    from ph_extract_tmp1_org a, dup_ph_tmp1 b
    where a.rowid = b.row_id
    2 3 4 5 6 7 ;
    create table ph_extract_tmp1 parallel 32 nologging
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P033
    ORA-01652: unable to extend temp segment by 64 in tablespace TEMP


    Thanks in advance ....
    himridul

  2. #2
    Join Date
    Jan 2004
    Posts
    99
    try this and then run your query, let me know how you get on.

    ALTER SESSION SET HASH_MULTIBLOCK_IO_COUNT = 2

  3. #3
    Join Date
    Jan 2004
    Posts
    66
    No zaki_mtk , It's not working . Still it's giving the same error message.
    himridul

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    how about autoentending your temp tablespace
    OR
    create a new LARGE temp tablespace and assign that to
    the user that is creating this table.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can also set up a larger sort_area_size to keep the sort in memory as opposed to going to the temp tablespace. If you have to go to the temp tablespace, ensure the default next_extent size for the temporary tablespace is the same as the sort_area_size parameter.
    I would guess that it is currently setup for 64kb ... Probably toooooo small ....

    HTH
    Gregg

  6. #6
    Join Date
    Jan 2004
    Location
    India
    Posts
    113
    Resizing the datafile associated with the temporary tablespace could be a good option to try.

    Alter database datafile ..... resize 500m;

    But remember resizing means it does not add up to the existing one (ie if 200m existed then it won't be 200+500=700m) u will find the size of the datafile as 500m.
    U can also add more datafiles to ur temp tablespace.
    HOPE IT HELPS

    Kaushik

Posting Permissions

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