-
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).
-
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"
-
Please note the word TEMPORARY for TEMPORARY TABLESPACE.
-
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"
-
Never use AUTOEXTEND feature.
Tamil
-
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.
-
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
-
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"
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|