Re: Temp Tablespace

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 9 Jun 1999 21:41:26 +0100
Message-ID: <928961628.26673.4.nnrp-07.9e984b29@news.demon.co.uk>

Is the tablespace declared to be of type TEMPORARY ? If so, then the single instance-based temporary segment is not released until instance shutdown.

Check the contents v$sort_segment (or possibly v$sort_segments).

I suspect there may be a bug somewhere in the 7.3.4 code for handling this segment, or for sorting in general, as I have recently ported a system from Pyramid with 7.3.3 to Sun with 7.3.4, and find that it is possible to use 16 Gb of temporary tablespace whilst sorting a 1 Gb table ! However, I haven't had time to look into this in detail.

If you want a highly detailed (but potentially expensive) mechanism for finding out what is happening with sorting, set event 10032 in the init.ora file, and you will get a trace file dump of stats for every single sort that takes place. You might want to try this on a development system first though.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Leo wrote in message <375B7635.994DAB13_at_best.com>...
>Hi all,
>
>Any input on what to look into if the temporary tablespace (of good
>size) keeps getting filled up? I'm running Oracle 7.3.4 on Solaris 2.6.
>
>Thanks,
>Leo
>
>
Received on Wed Jun 09 1999 - 15:41:26 CDT