Re: Tempspace never freed?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 21 Jun 1998 20:47:25 GMT
Message-ID: <35936ea3.16309912@192.86.155.100>

A copy of this was sent to Richard C Haven <rhaven_at_dontspamonme-santacruz.com> (if that email address didn't require changing) On Fri, 19 Jun 1998 18:13:14 -0700, you wrote:

>We are running a 7.3 NT server and the tempspace used when we open
>queries seems never to be freed. This is a recent behavior change and
>the only real difference in environment is that the tables are slightly
>larger. We have checked the initial extents and they seems fine (e.g.
>large enough to hold the entire table).
>
>Any ideas?

Are you using 'temporary' tablespaces? (real temporary, created or altered to be temporary..)

If so, this is normal. space is allocated in there once and kept around (to avoid repeated space management requests). If you really want to take it back down to zero (or as near as possible), you can alter it to be permanent and then back to temporary to get the space 'released' (but you'll only slow things down).

Are you running out of temp space? You should be because of this -- the space gets used and reused, its just not given back to the 'data dictionary' to avoid the space management requests...

An example of "freeing" the temp space:

SQL> @free

                                                             %
Tablespace Name        KBytes         Used         Free   Used      Largest
---------------- ------------ ------------ ------------ ------ ------------
...
TEMPORARY              31,744          248       31,496     .8       16,696

...
29 rows selected.

SQL> alter tablespace temporary permanent; Tablespace altered.

SQL> alter tablespace temporary temporary; Tablespace altered.

SQL> @free

                                                             %
Tablespace Name        KBytes         Used         Free   Used      Largest
---------------- ------------ ------------ ------------ ------ ------------
...
TEMPORARY              31,744            8       31,736     .0       31,736

...

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Jun 21 1998 - 15:47:25 CDT