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
> ?

Yes. though not apparently on NT4.

> 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.
>
> 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.
>
> an OS 'cp' to 'cp' changes it to a normal file, but its still a bit naff
and
> dangerous.

Not at all. It makes a great deal of sense from a backup and recovery point of view. don't backup the temp ts and recreate it is quick and easy.

as for filling up the filesystem putting non-oracle stuff on the same filesystem as oracle datafiles is the naff and bad bit surely.

what about autoextend datafiles (with a limit of course). what about io contention. in short don't do it. set aside a file system for yor datafiles.

--
Niall Litchfield
Oracle DBA
Audit Commission UK