Re: tempfile on solaris 2.7

On Tue, 27 Nov 2001 21:51:57 +1100, "G.Ong" <onggs_at_acslink.aone.net.au> wrote:

>Hello,
>
>I like to ask some questions about tempfile.
>
>We are using solaris 2.7 + rdbms 8.6.3.
>
>When a temporary tablespace with tempfile was created eg 1000M, the file is
>created + is visible at unix level ie. "ls -l" shows the file + size.
>However, "df -k" (or "du -ks" ) of that filesystem/directory does not show
>space being used (ie showed about 100K being used only).
>
>1)I know Oracle doesn't initialise tempfile, but what is the difference
>between ls -l and df -k ?
>2) However can I force Oracle to initialise the file (apart from running a
>query with a big sort) ?
>
>TIA,
>Ghee
>
>

Hi,

first I assume you are talking of 8.1.6.3?

The tempfile when created is only a descriptor in the filesystem. So ls -l lists only the directory entries. df -k shows the real used/free space in the filesystems. Since the tempfiles allocating there space from filesystem when it is needed you will see a decrease in free space when sorts occur, which donīt fit in the sort_area.

You can avoid this sometimes critical behaviour as follows: drop the temporary tablespace
create a permanent tablespace with a datafile named as you want to name your tempfile.
drop the tablespace
create your temporary tablespace with tempfile and REUSE the file of the dropped tablespace.
All the space is already allocated.

The critical behaviour mentioned above arises if due to continued usage of filesystem space there is not enough room to allocate the rest of the tempfile. Oracle alone (hopefully) knows what will happen.

I encountered another intersting problem which led me to the solution above in creating temporary tablespaces with tempfiles. I created my fist temp ts with tempfile and after short usage without sorting I shut the db down. After the restart the tempfile has vanished. Oracle logs this in the alert.log but an error occurs only when first accessing the temp ts in a large sort. Nice behaviour isnīt it?

Regards
Andy Received on Tue Nov 27 2001 - 08:39:47 CST