Re: datafiles and tempfiles

I'm intrigued, Niall.

I was in Hobart a month or two back. Being a "branch" office (a bit like Canberra, only worse, if you can imagine such a thing... sorry Richard!), they have no access to our central Solaris server. So the course was run on NT workstations.

I have a standard demo for why the 'tempfile' syntax was created: create an old-fashioned datafile temporary tablespace of, say, 75M, and start counting (go and make a cup of tea, have three smokes and a light snack whilst waiting). Then I create a tempfile temporary tablespace of size 750M, and Lo! It gets created in a second or so. This demo, of course, was perfected when using the Solaris machine.

So here I go, on an NT box, spouting the same spiel as ever.... only to realise too late that NT wouldn't know a sparse file if it came up and hit it with a mackerel. So the demo goes "see how long this takes... now see how long *this* takes.... er, er.... er... bugger".

So prompted by your post, I've just done the following:

SQL> create temporary tablespace temptest tempfile 'd:\oracle\ora92\db9\temptest01.dbf' size 400m; Tablespace created.
Elapsed: 00:00:00.06

SQL> create tablespace usual datafile 'd:\oracle\ora92\db9\usual01.dbf' size 400m;
Tablespace created.
Elapsed: 00:00:14.01

So, stuff me if Windows XP doesn't know about tempfiles after all (it makes sense, I guess... it's just that Oracle isn't formatting the blocks. And that's an Oracle issue, not an O/S one).

Yet I know for sure that the Hobart NT demo was about as successful as me doing my "juggling three swords and a firestick whilst doing trampoline demonstrations" party trick. In other words, not very.

Did things change between NT and W2K/XP?

Thanks for correcting my poor understanding, anyway.

Regards
HJR "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d216ded$0$8506$ed9e5944_at_reading.news.pipex.net...
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:afqhff$jkh$1_at_lust.ihug.co.nz...
> > Actually, Chuck, temporary tablespaces that are made from tempfiles
CANNOT
> > be put into hot backup mode. So no, you are not supposed to back them
up,
> > and yes, you are supposed to re-create them from scratch.
> >
> > Being sparse files, re-creating a huge tempfile on a Unix box takes a
> matter
> > of a second or so at most.
> >
> > Regards
> > HJR
>
> and creating a decent sized tempfile on NT doesn't take forever either.
>
> SQL> set timing on
> SQL> create temporary tablespace nltest
> 2 tempfile 'c:\nltest.dbf' size 1500m;
>
> Tablespace created.
>
> Elapsed: 00:00:01.08
> SQL> drop tablespace nltest;
>
> Tablespace dropped.
>
> Elapsed: 00:00:00.07
> SQL> create tablespace nltest
> 2 datafile 'c:\nltest.dbf' size 1500m;
> create tablespace nltest
> *
> ERROR at line 1:
> ORA-01119: error in creating database file 'c:\nltest.dbf'
> ORA-27038: skgfrcre: file exists
> OSD-04010: <create> option specified, file already exists
>
>
> Elapsed: 00:00:00.00
> SQL> create tablespace nltest
> 2 datafile 'c:\nltest.dbf' size 1500m;
>
> Tablespace created.
>
> Elapsed: 00:03:03.06
> SQL> drop tablespace nltest;
>
> Tablespace dropped.
>
> Elapsed: 00:00:00.09
> SQL>
>
> 1 second isn't going to hurt anyone in a recovery.
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
>
>
Received on Tue Jul 02 2002 - 04:29:32 CDT