DBA Tips Archive for Oracle

  


Oracle9i: Default Temporary Tablespaces - (Oracle 9i)

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. Create Database Example
  3. Creating Default TEMP Tablespace After Database Creation
  4. Restrictions
  5. Oracle9i Update!



Overview

Before Oracle9i, creating users without explicitly defining them to a temporary tablespace would result in their account using SYSTEM for sorting. This would often lead to both performance and space issues. A new feature in Oracle9i is the ability to specify a Default Temporary Tablespace when creating the database.

Use the DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement to specify that a temporary tablespace is to be created at the point of database creation. This tablespace will be assigned as the default temporary tablespace for any users not otherwise assigned a temporary tablespace. The DBA can still though explicitly assign users a default temporary tablespace.



Create Database Example

    CREATE DATABASE "ORA920" NOARCHIVELOG
        MAXLOGFILES             32
        MAXLOGMEMBERS           5
        MAXDATAFILES            600
        MAXINSTANCES            10
        MAXLOGHISTORY           1000
    DATAFILE
      '/u08/app/oradata/ORA920/system01.dbf' size 500M
      REUSE
      AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE temp
        TEMPFILE '/u07/app/oradata/ORA920/temp01.dbf' SIZE 500M REUSE
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500K
    UNDO TABLESPACE "UNDOTBS" DATAFILE
      '/u06/app/oradata/ORA920/undotbs01.dbf' SIZE 300M
      REUSE
      AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
    CHARACTER SET UTF8
    NATIONAL CHARACTER SET AL16UTF16
    LOGFILE
      GROUP 1
     ('/u03/app/oradata/ORA920/redo_g01a.log',
      '/u04/app/oradata/ORA920/redo_g01b.log',
      '/u05/app/oradata/ORA920/redo_g01c.log') SIZE 15M,
      GROUP 2
    ( '/u03/app/oradata/ORA920/redo_g02a.log',
      '/u04/app/oradata/ORA920/redo_g02b.log',
      '/u05/app/oradata/ORA920/redo_g02c.log') SIZE 15M,
      GROUP 3
    ( '/u03/app/oradata/ORA920/redo_g03a.log',
      '/u04/app/oradata/ORA920/redo_g03b.log',
      '/u05/app/oradata/ORA920/redo_g03c.log') SIZE 15M
    /



Creating Default TEMP Tablespace After Database Creation

If the DBA decides to change the default temporary tablespace, or to create one after the database is already created, it is very straightforward. Simply create the new temporary tablespace (CREATE TEMPORARY TABLESPACE), then assign it as the default temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. By default, users will be automatically switched to the new temporary tablespace.

After creating or ensuring that your temporary tablespace exists, run the following command:

    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
The DBA cannot drop a default temporary tablespace, but it is possible to assign a new default temporary tablespace and then drop the old one. You also cannot change a default temporary tablespace to a permanent tablespace, nor can you take a default temporary tablespace offline.

Users can utilize the view: DATABASE_PROPERTIES to obtain the name of the current default temporary tablespace. The PROPERTY_NAME column contains the value "DEFAULT_TEMP_TABLESPACE" and the PROPERTY_VALUE column contains the default temporary tablespace name.

  SQL> COLUMN property_value FORMAT a16

  SQL> SELECT property_name, property_value FROM database_properties
    2  WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

  PROPERTY_NAME                  PROPERTY_VALUE
  ------------------------------ ----------------
  DEFAULT_TEMP_TABLESPACE        NEW_TEMP



Restrictions

The following restrictions apply to default temporary tablespaces:



Oracle9i Update!

Finally, note that Oracle9i will no longer allow you to assign a permanent locally managed tablespace as a user's temporary tablespace. This was allowed in Oracle8i, but the users session would get an error when it tried to create a temporary segment in the tablespace.


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 20-Jan-2006 15:47:15 EST
Page Count: 19354