Temporary tablespace filled up - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 31

Thread: Temporary tablespace filled up

  1. #21
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You need to create indexes on the column cpolicyno of HOMESUPP table.

    Tamil

  2. #22
    Join Date
    Nov 2003
    Posts
    89
    You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed


    select shows 5075 rows wihtout group by. It took 30 minutes to run the select statement.

  3. #23
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you have an index on the column cpolicyno of HOMESUPP table?

    Tamil

  4. #24
    Join Date
    Nov 2003
    Posts
    89
    I am creating index now.
    Thanks

  5. #25
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed.
    I don't think so -- the space requirement is related more to the size of the output from the grouping than it is to the number of ungrouped rows.

    For example,

    Code:
    Select Sum(my_num)
    From   table_with_million_rows
    ... is going to require very little sort space, right?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #26
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Slimdave,
    It is not the group by clause causing the problem b/c the result set after joining 2 big tables is small. MERGE JOIN operation is causing real problem of filling out the temp space. I am trying to eliminate MERGE JOIN by adding an INDEX.

    Tamil

  7. #27
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    Slimdave,
    It is not the group by clause causing the problem b/c the result set after joining 2 big tables is small. MERGE JOIN operation is causing real problem of filling out the temp space. I am trying to eliminate MERGE JOIN by adding an INDEX.

    Tamil
    OK I see ... well I'd think that a hash join would be worth trying also.

    srt, do you have hash joins enabled in your db?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #28
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    ok, why dont you run the sql statement alone and see how it performs.
    For starters, you seem to be using
    Code:
    from homesupp a, homemain b where cpolicyno
    but you dont seem to be using this reference a or b for the column names in question.

  9. #29
    Join Date
    Nov 2003
    Posts
    89
    Worked fine with creating an index on the column.

  10. #30
    Join Date
    Feb 2005
    Posts
    1

    cleaning up temp...

    Assuming that none of the extents are currently used, you can issue the "alter tablespace" command below to clean up temp.

    alter tablespace temp
    default storage (pctincrease 0);

    This will tell smon to clean up the segments in the tablespace that are not used.

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