The Eternal Temporary Tablespace Question
Haven't seen this one in a while, but it used to be all over the forums like a rash -- "Upgraded to 8i and now my Temp tablespace is always full".
Needless to say, it's documented.
posted by David Aldridge @ 7:05 AM
10 Comments:
. . . so I set it to autoextend maxsize unlimited . . .
I waited 5 days for this???
DaPi,
... and the funny thing is it never extends, but it's always full ...
================
Thanks Richard
================
Jeff,
Man, that's harsh! If you knew what I'd been through over the past few days -- RAID problems, ASM installs, ASM de-installs, raw devices, cooked devices, I've been screwed by technology at every turn. And not in a good way, either.
"it never extends" Don't you know how to spell Cartys . . , Kartesy . . . , Carthesiun . . . oh hell, you're lucky.
"never extends" . . .
Perhaps the phrase is complete refresh of a mult-million row Materialized view
;)
Dave,
I'm a litle confused. I read the section about temporary tablespaces, but didn't see anything about the tablespace always being full. Maybe I missed it, I've been known to read too fast and skim over things, especially dry material. ;)
Could you be so kind as to point it out to me? Please? We are experiencing this and I need to explain it to one of our infrastructure guys.
Thanks!
-John
Sure John ... here's the original post.
i have a problem with a oracle 8i 8.1.7
my temp tablespace grow and dont stop to grow untill i shutdown the database and start it again.
after i restart the database the temp is empty
how can i check what is the problem???
the temp dont clean even the session is closed.
Now unless I'm off the mark here, what the OP is saying is that when he looks at the temporary tablespaces on his system he sees that they are full -- he doesn't mention getting "Cannot extend" errors, just the state of "fullness".
This commonly means that a person is looking at the tablespace through a GUI tool such as OEM or Toad and seeing that the temporary tablespace is 100% allocated. In older versions of Oracle this was an indication that the entire tablespace was actually being used, but in 8i (I think) this changed. The database creates segments within the temporary tablespace but does not delete them when they are not being used (thus saving some overhead in creating and dropping them). They just get used for sorting by different sessions as indicated by the v$sort_usage system view.
So the GUI tools are giving a false impression of usage -- there's really no reason why these GUI tools couldn't display used segments instead of all segments.
Long-story-short, the presence of segments withing the temporary tablespace is not an indication of usage -- you have to look at v$sort_usage to find out how much if any of the tablespace is actually being used.
Thanks! I figured that was what was going on, but wasn't quite sure. I just wanted to confirm that. I'm going to be putting together an SQL script to plug in to my ntework/system monitoring tool so we can see what is going on and be alerted to actual running out of space problems.
Thanks for the answer!
also David you might be off the mark a little with the original problem, if you set autoextend maxsize unlimited then as dapi indicated on early 8i temp will just keep on growing rather than reusing perfectly reusable segments (maybe the autoextend guys also hadn't heard of v$sort_usage back then). I think this got fixed in a patch set.
David,
Just as a footnote to your response to me, I threw together a query to see actual temp space usage as opposed to GUI interpreted temp space usage. Simple I know but if I can make someone else's life that much more simple...
SELECT decode(sum(s.blocks),NULL, 0, (sum(s.blocks)/sum(t.blocks))*100) temp_percent_used
FROM SYS.v_$sort_usage s RIGHT OUTER JOIN SYS.dba_temp_files t ON s.TABLESPACE = t.tablespace_name
(I know, left outer join would have worked too, but it's all in the ordering of your from clause for this simple of a query. I also chose ANSI/ISO syntax over Oracle since I like the idea of possible portability.)
-John
Post a Comment
<< Home