Not exactly correct. It is 1 single segment, with multiple extents. The extents
are reused. The segment is never dropped-and-recreated unless you
(a) Stop and start the database instance [it is then created on the first request
for a temporary segment]
(b) If it is NOT a TEMPORARY Tablespace but a Tablespace .. TEMPORARY,
you issue an ALTER TABLESPACE ... DEFAULT STORAGE ... command
Now why should we worry about the Temporary Segment ?
Of the 60 odd databases I have there are 2 databases [on in 8.1.7 where I am
not using a TEMPORARY TABLESPACE but a TABLESPACE .. TEMPORARY,
and therefore, I can use the ALTER TABLESPACE .. DEFAULT STORAGE ... command
and the other in 9.2 where I am using a TEMPORARY TABLESPACE with TEMPFILEs]
where at least once a month the Tablespace which is usually 2GB [and 400MB used] goes to 8GB
[or, as I found in a third 9.2 database this week 24GB], the file system is full
and users may start getting errors. Now, the Unix Administrator does have a CRON job
checking all the file systems of his 200+ servers, once-an-hour, I have a CRON job checking all
the tablespaces of the 60 databases every hour but it takes much less than an
hour for some user writing a "wonderful SQL query" to take Temporary Tablespace
out from 2GB to 8GB [or 24GB !].
It is in those cases, that I need to know how to bring my Temporary Tablespace usage
down.
{Of course, I do know that if that "wonderful SQL query" user has exited, then
he has released all the 8GB or 24GB of extents but my Unix administrator
asks me why I am using so much space and what he should do about the alerts
that the filesystem is 90+% full. If I keep the MAXSIZE of the files too low,
it is quite likely that more than one user gets errors trying to allocate extents while the
"wonderful SQL query" user is still around in the database}.
I also issue the ALTER TABLESPACE ... DEFAULT STORAGE .. command in the
daily backup job to drop the Temporary segment when it is not in use.
Yes, there is a cost associated with reallocating extents but this is only once a day.
If the NEXT is appropriate sized, few user sessions would be allocating more than
one or two new extents the first time round.
Hemant
At 10:09 AM 14-11-03 -0800, you wrote:
[EMAIL PROTECTED] scribbled on the wall in glitter crayon:
> I got the impression that the poster was thinking segments allocated > in temp tablespace must be released, cleaned out, blown away, etc. > before something else can come along and use the space.
i was always under the impression that you didn't need to do anything to the temp tablespace. that the segments were just reused as needed. am i behind the times here?
-- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] ------------------------------------------------------------------------ Shift to the left! Shift to the right! Pop up, push down, byte, byte, byte! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
