About TEMPFILE creation

From: steven_nospam at Yahoo! Canada <steven_nospam_at_yahoo.ca>
Date: 9 Mar 2007 08:12:15 -0800
Message-ID: <1173456735.752097.70700@p10g2000cwp.googlegroups.com>

System: AIX 5L
RDBMS: Oracle 9i

Is there any difference (advantage or disadvantage) to how you set up a temporary tablespace? In Oracle 8i, we always used datafiles for our temp tablespaces, such as the following:

##### OLD VERSION #######

CREATE DATABASE databasename
...;

CREATE TABLESPACE temptbspacename

   DATAFILE '/data/file/name'
   SIZE 300M
   AUTOEXTEND OFF
   ONLINE TEMPORARY; Starting with Oracle 9i, we started to create new databases with the temptbspace listed as part of the CREATE DATABASE statement.

##### NEW VERSION ######

CREATE DATABASE databasename

DATAFILE '/data/file/name' SIZE 512M DEFAULT TEMPORARY TABLESPACE temtbdspacename

    TEMPFILE '/temp/file/name'
    SIZE 300M
    AUTOEXTEND ON
    MAXSIZE 1000M.....; The only difference we have seen is that the temp tablespace is now listed in V$TEMPFILE instead of being in V$DATAFILE along with the other data files.

So we have three questions:

Which is the more accepted practice for creating the temp tablespace?
Is anyone aware of any plans to change how temp tablespaces are created or maintained for future versions of Oracle (we have not checked this in 10g yet)
Is there any performance hit that may result from doing this one way or the other?

Thanks in advance,

SteveN Received on Fri Mar 09 2007 - 10:12:15 CST