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

Thread: autoexend on temporary tablespace

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    autoexend on temporary tablespace

    We are using Oracle 9i (9.2.0.1.0) and has anyone noticed that if you have autoextend on the temporary tablespace (locally managed), the tablespace continually extends and doesn't appear to be re-using the available space within the tablespace?

    We have only a 5GB database, yet the temporary tablespace extended to 14GB. We eventually took autoexend off, and reverted the temporary tablespace back to 500mb. The application on this database is OLTP and does very small 'sorts', 'order by' and 'group by', and very rarely uses the temporary tablespace (sort_area_size is 2m).

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: autoexend on temporary tablespace

    Originally posted by ssmith
    We are using Oracle 9i (9.2.0.1.0) and has anyone noticed that if you have autoextend on the temporary tablespace (locally managed), the tablespace continually extends and doesn't appear to be re-using the available space within the tablespace?
    This will be true with any versions & any type of TS, what is strange in it.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    Please note the word TEMPORARY for TEMPORARY TABLESPACE.

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Yes, i know that u mentioned TEMPORARY !
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Never use AUTOEXTEND feature.

    Tamil

  6. #6
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    Why should you never use the AUTOEXTEND feature?

    Does Oracle prefer to extend the datafile rather than re-use the available space. We have reverted the temporary tablespace back to 500mb and switched AUTOEXEND off and everything is working fine i.e no 'temp out of space' errors.

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by ssmith
    Why should you never use the AUTOEXTEND feature?

    Does Oracle prefer to extend the datafile rather than re-use the available space. We have reverted the temporary tablespace back to 500mb and switched AUTOEXEND off and everything is working fine i.e no 'temp out of space' errors.
    especially on temp else mr dough-head developer will write a cartesian join on a big freaking table and merrily autoextend your temp df so that it eats up your entire file system
    I'm stmontgo and I approve of this message

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by ssmith
    Does Oracle prefer to extend the datafile rather than re-use the available space.
    Yes
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Maybe I can shed some more light on the subject as to why you don't want to choose auto extend especially on a temp table.

    1. For those of you who are using OEM you will notice that 9 times out of 10 your temp space is mostly full. That's ok, Oracle will free up that space as needed. He's just very unclean and doesn't like to clean up after himself after he's done a sort.

    2. Say that mr dough head (thanks stmontgo) does go out and produces a carteisan join that snatches up the database into temp. Would you rather have errors in your alert log or your server lock up because there's no room left on the mount point (or drive for you NT'ers).

    3. Case in point. This just happened to me two days ago.
    NOTE* I am new to this client site. I didn't build this database so be nice.

    I'm perusing through the server looking for space to make a new tablespace. I initally did a df -k I see that the /sw mount point is at 81% full. I go and do some other commands and forget which mount point I want so I issue df -k again Booom! /sw 97% full. Come to find out a dough head (thanks agian Stmontgo) developer had a package looping out of control. The stupid temp tablespace was on autoextend and dictionary managed to boot.

    4. Correct me if I'm wrong... but having a temp space over 4 gigs is pointless because you're just promoting bad code from developers. Also, if it blows out at 4 gigs that should scream "COMMIT" to you.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  10. #10
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    Interesting....I never noticed this behaviour in Oracle 8i. We had autoextend on the TEMP tablespace and it NEVER extended beyond 500mb, and we were running with this version for over 4 years. As soon as we upgraded to Oracle 9i, the TEMP tablespace extended to 14GB!!

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