Re: datafiles and tempfiles

"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

******************************************