Re: Sparse TEMP files

"Liggs" <liggs_at_hotmail.com> wrote in message news:afv89t$cl3$1_at_helle.btinternet.com...
> Anybody else notice that Oracle 8i+ creates TEMP files as sparse files
> ?

Er, yes... it's a design feature of 8i, and is taught as a standard part of the 8i New Features course.

> It also adds it very quickly and oracle sees it as, eg 500mb, the 'ls -l'
> Unix command sees it as 500Mb,
> but the 'ls -s' doesn't, possibly saying 10Mb instead.
>

Correct. That's what sparse files do.

> When someone does a query that needs all the TEMP tablespace, it expands
it,
> possibly to the full 500Mb.
> Which is fine, unless you've filled the filesystem up in the meantime.
>

Why on Earth would you do that? Standard Oracle advice is that Data should be separate from Rollback and SYSTEM, and all three should be separate from TEMP. There's a potential horrible I/O contention issue if you stick TEMP on any disk that is used by another part of the database. Therefore, TEMP will be a datafile on its own, and you won't have a problem.

That said, yes, something growing into the space needed by the sparse tempfile will indeed cause 'out of space' errors. Live with it.... life is always a bargain. In this case, the deal was 'you want to be able to recreate your temporary tablespaces at lightning speed instead of having to go to the trouble of backing them up? So it shall be... but the price is that if you can't configure an Oracle database properly, you might run into trouble.'

> an OS 'cp' to 'cp' changes it to a normal file, but its still a bit naff
and
> dangerous.
>

No it's not. You might as well say that having a buffer cache is naff and dangerous, because it means changes to data are left sitting aruond in volatile RAM and are subject to loss. It's just the way it is. It's well publicised, and pretty standard configuration sense would eliminate potential problems, just as switching archiving on guarantees that no matter what happens to the buffer cache, your data is safe. Trade-offs and compromises.... the stuff of life and Oracle databases.

Regards
HJR
> Alun
>
>
Received on Wed Jul 03 2002 - 15:21:34 CDT