Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Creating tempfiles does not reduce available disk space reported by "df "

Re: Creating tempfiles does not reduce available disk space reported by "df "

From: Howard J. Rogers <howardjr_at_www.com>
Date: Thu, 1 Nov 2001 06:57:40 +1100
Message-ID: <3be05789$0$15799$afc38c87@news.optusnet.com.au>


Because proper tempfile tablespaces cheat: they use sparse files, where the space is not actually carved out of the disk until its actually used. So whilst the file system knows that the space has been reserved for the tempfile, its not actually allocated, and therefore something else could grow to pinch the space.

They implemented things that way to make the instant recreation of mammoth temporary tablespaces a realistic proposition in recovery scenarios. If you've got the space on a box somewhere, try these two simple tests:

create tablespace temp1 datafile '/bing/bong/temp1.dbf' size 1000m temporary;
(and count how long it takes to create the tablespace)

then:
create temporary tablespace temp2 tempfile '/bing/bong/temp2.dbf' size 2000m;
(and I doubt you'll have much opportunity to count how long it takes, it's that fast, despite being twice the size of the other one).

The point is that backup and recovery advice has always been: if its the temporary tablespace that's stuffed, drop it and recreate it. But in practice, in earlier version, that could mean the database was effectively out of commission for minutes whilst the new create tablespace..temporary was doing its stuff. In 8i, with tempfiles, it takes fractions of a second, so dropping and recreating is now genuinely feasible.

But you never get anything for nothing with Oracle, as in life generally: what you gain in recovery flexibility, you potentially lose because of this 'out of space' problem that you've encountered.

Incidentally, for performance reasons, you really want temporary tablespaces on disks all of their own, and if that was actually the case, you wouldn't have to worry about it.

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


"Peter Hitchman" <peter.hitchman_at_derwent.co.uk> wrote in message
news:10eb1a53.0110310431.4fb6f08c_at_posting.google.com...

> Hi,
> We are running a veritas filesystem, on Solaris 8.
>
> I have created a tempfile temporary tablespace, but when looking
> at the filesystem where it is created with "df", the used/free space
> stays the same, it does not change until the tablespace is used.
> But I can confirm the file size using "ls -l". I have since managed to
> create tempfiles that do not fit in the filesystem, that then causes the
> dbw to get "out of space" messages from the OS and the file goes offline.
>
> Does anyone know why this is?
>
> Regards
>
> Pete
>
> [END]
Received on Wed Oct 31 2001 - 13:57:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US