Re: About TEMPFILE creation

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 9 Mar 2007 09:32:41 -0800
Message-ID: <1173461561.539385.235580@8g2000cwh.googlegroups.com>

On Mar 9, 11:24 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "steven_nospam at Yahoo! Canada" <steven_nos..._at_yahoo.ca> a écrit dans le message de news:
> 1173456735.752097.70..._at_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:
> |
> | 1) Which is the more accepted practice for creating the temp
> | tablespace?
> |
> | 2) 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)
> |
> | 3) Is there any performance hit that may result from doing this one
> | way or the other?
> |
> | Thanks in advance,
> |
> | SteveN
> |

> http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96533/und...http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96521/tsp...http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/sta...http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/sta...

> Regards
> Michel Cadot

I suggest you use a true temporary tablespace: create temporary tablespace temp
tempfile '..............'

Tempfiles are not backed up by rman nor do you need to back them up as part of a manual backup. Using tempfiles saves backup time and space as well as simplifies recovery as the tempfiles are just reallocated on startup. Tempfile usage also reduces the amount of redo generated and 'ST' lock activity.

HTH -- Mark D Powell -- Received on Fri Mar 09 2007 - 11:32:41 CST