Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Create Tablespace Temporary vs. Create Temporary Tablespace

• CREATE TABLESPACE temp DATAFILE ‘d:\oradata\orcl\tempdata.dbf.’ The statement above creates a tablespace that, although described as "temporary" in dba_tablespaces, has one or more permanent datafiles associated with it. The tablespace is designed to hold sort data and manages segments and extents differently (see above) than its permanent tablespace counterparts.

• CREATE TEMPORARY TABLESPACE temp TEMPFILE ‘d:\oradata\tempdata.dbf.’ This statement creates a true temporary tablespace. This eliminates the need to back up the temporary tablespace, which results in faster backups and a reduction in disk space. Information describing true temporary tablespaces is found in the v$tempfile  and dba_temp_file  views. No information is stored in the dba_data_files and v$datafile views, which describe permanent datafiles.

The database will start even if this file is missing.  The only way the administrator will know that the file is gone is when the first sort tries to overflow to disk.  A very explicit error message will be generated stating that the sort could not complete due to a missing or invalid sort file.  The administrator will be forced to drop and recreate the temporary tablespace before sorting to disk can take place.
 

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.