TEMP tablespace grows all the time!
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: TEMP tablespace grows all the time!

  1. #1
    Join Date
    Nov 2000
    Posts
    22

    Cool

    Hello All.

    RDBMS: 8.1.7.3
    O/S : Sun Solaris 8


    Localy Managed Temporary tablespace (with unlimited autoextend enabled
    on datafiles) is 40G already - and keeps growing.
    Tablespace is locally managed, and the DB is 24x7 environment,
    so shutdown is not an option to reduce the tablespace size.

    I know such a problem exists since early 8i versions (at least 8.1.6 and up),
    but nobody seems to know the answer ...

    Please Advice.
    Regards.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    It is unclear from your message if you created it with the TEMPFILE keyword. However, what makes you bother about how big it is?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Nov 2000
    Posts
    22
    Thanks for you reply.

    I used tempfile keyword of course , and the uniform extent
    (SORT_AREA_SIZE + DB_BLOCK_SIZE) allocation exists.

    I am concerned of the size because I dont want it to be too large, not more than 10G. The way it grows today it will get to
    100G in a couple of weeks.

    I am pretty sure there is a way to clear it without bouncing the DB...

    Regards.

  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    I think there is no other way but to restart the db. Or drop and recreate the temp tablespace making sure that no active connections are using the temp tablespace during that time.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  5. #5
    Create a new temp tablespace, switch it (create temporary tablespace "new_temp_tablespace" tempfile '.../...' size XXXXM extent management local uniform size 100M bla-bla, then: alter database default temporary tablespace "the_new_created_tablespace"), wait untill it becomes inactive (all thw transactions using it are finished), then drop it.

    (Then delete the file and repeat the procedure "reversing" names if you want it where it initially was.)

    THIS WORKS FINE WITH 9i. BECAUSE I DO NOT KNOW IF IT WORKS WITH 8i, MAKE A TEST BEFORE...


    [Edited by ovidius on 04-01-2002 at 04:39 AM]
    ovidius over!

  6. #6
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking


    i assume it is fragmented if it is a temp tablespace.
    check to see if it's fragmented and then coalesce it. see if that lowers the space utilization.

    certainly will not solve you problem per se ...

    - Magnus

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by SGA
    Thanks for you reply.

    I used tempfile keyword of course , and the uniform extent
    (SORT_AREA_SIZE + DB_BLOCK_SIZE) allocation exists.

    I am concerned of the size because I dont want it to be too large, not more than 10G. The way it grows today it will get to
    100G in a couple of weeks.

    I am pretty sure there is a way to clear it without bouncing the DB...

    Regards.
    I have had a similar problem before (with 8.1.7). What jgmagnus suggested will not work. You cannot run
    Code:
    ALTER TABLESPACE TEMP COALESCE;
    Well, you can of course but you will get ORA-3217.

    Bounce the base!

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Analyze table requires more space in the temp tablespace.

    Do not analyze a big table ( > 100 million rows) with 100 %.

    Analyze only 5 to 10 % on big tables.


    When there is low level of transactions, try to coalesce the temp tablespace.



  9. #9
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378
    Originally posted by tamilselvan
    When there is low level of transactions, try to coalesce the temp tablespace.
    So why did Julian say coalesce will not work?
    Am I missing something here?

    I don't get errors coalescing with an 8.1.7 db ...

    - magnus

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by jgmagnus
    So why did Julian say coalesce will not work?
    Am I missing something here?

    I don't get errors coalescing with an 8.1.7 db ...
    It depends on what kind of "temporary" your temporary tablespace realy is. If you are using tempfiles (as ovidius suggested) then you can't coalesce such temp tablespace (as julian pointed out).

    But nevertheless, is simply doesn't make any sence at all to coalesce a temporary tablespace, no matter of the Oracle release or the flavor of your temp tablespace (if it realy is created as a temporary tablespace, which became available sometime arround 7.2 or 7.3, if I'm not mistaken). You are absolutely not gaining anything by coalescing temp tablespace, you are only slowing down your future temp space allocations.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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