Oracle's Default TEMPORARY Tablespaces

Friday Oct 10th 2003 by James Koopmann

Using the SYSTEM tablespace as a temporary tablespace is to be frowned upon, yet Oracle still lets us assign it as such. James Koopmann explores a better way.

While we all know that using the SYSTEM tablespace as a temporary tablespace is to be frowned upon, Oracle still lets us assign it as such. Let's take a look at how Oracle suggests handling the creation and assignment of TEMPORARY tablespaces.

Isn't it funny? We have all been told from early on in our DBA carriers that having the SYSTEM tablespace assigned as a TEMPORARY tablespace for any purpose what so ever is highly frowned upon. Yet, Oracle still allows us to implement the SYSTEM tablespace as TEMPORARY for user in our databases. While they are reluctant to make this a constraint when creating users, they have taken a step forward in the fight against DBAs assigning this precious tablespace as a TEMPORARY tablespace.

The Evolution of the New TEMPORARY Tablespace

In the old days, DBAs would create a normal tablespace and assign this to users to use as a temporary tablespace through the CREATE USER or ALTER USER commands. These tablespaces could hold temporary segments and permanent segments. Oracle's position at that time was to advise everyone not to place permanent segments into these tablespaces that were also being used as TEMPORARY tablespaces for users.

Oracle then implemented the notion of a true TEMPORARY tablespace. These TEMPORARY tablespaces were to be assigned to users at creation time and could only hold temporary segments. This was a great step in the right direction because now we could not place permanent objects into them and users could no longer be assigned permanent tablespaces as their temporary tablespaces. The problem was and still is that when you create a user, you might forget to specify the users' temporary tablespace and thus the users' temporary table space would be defined as the SYSTEM tablespace.

Now Oracle has gone yet another step further and allowed us to create a DEFAULT TEMPORARY tablespace for the entire database. The benefit this gives is if we forget to specify a temporary tablespace for the user, they will inherit the DEFAULT TEMPORARY tablespace as theirs. Let's go a bit further and look at the individual pieces that make this all possible.

The SYSTEM Tablespace

Discussion of TEMPORARY tablespaces would not be complete if we did not talk about the SYSTEM tablespace. After all, this is from where the true problem stems. If we have not setup a DEFAULT TEMPORARY tablespace properly or specify what temporary tablespace a user gets when creating him, he will be assigned the SYSTEM tablespace as his temporary tablespace.

One way to force yourself into using DEFAULT TEMPORARY tablespaces is to create a database that uses a SYSTEM tablespace that is locally managed. When you do this, by definition, the database must have a default temporary tablespace and this default temporary tablespace cannot be the SYSTEM tablespace. If you go this route and do not want to specify the DEFAULT TEMPORARY TABLESPACE clause, Oracle will take care of that for you and create one itself. Just remember if you go this route, the database cannot have any dictionary managed tablespaces in it.

If you already have a database that uses a dictionary managed SYSTEM tablespace, Oracle will allow you to migrate the dictionary managed SYSTEM tablespace to a locally managed tablespace. You can accomplish this by using the DBMS_SPACE_ADMIN PACKAGE. The statement looks like the statement in Listing 1.

Listing 1
Switch SYSTEM tablespace to locally managed


When doing the migration Listing 2 gives you a few items of which you need to be aware.

Listing 2
Items to be aware of when switching SYSTEM to locally managed tablespace

  1. The database will need to have a DEFAULT TEMPORARY tablespace defined.
  2. Rollback segments must also be in either locally managed tablespaces or must be using undo management.
  3. The system is in restricted mode when performing the switch.
  4. You might want to migrate all tablespaces to locally managed before the SYSTEM tablespace.
  5. You might want a cold backup.

The TEMPORARY Tablespace

You can create a TEMPORARY tablespace by issuing the statement in Listing 3. This is the simplest form of the CREATE TEMPORARY command. You may also issue a statement such as in Listing 4. The difference in the two statements is the specifying of the TEMPFILE location. In the first example, Oracle will use the initialization parameter DB_CREATE_FILE_DEST if specified. If this parameter is not specified you must use the statement in Listing 4 or the statement will fail. If you would like to set the DB_CREATE_FILE_DEST, just issue a statement such as that in Listing 5. If you are wondering how the files look for the tablespaces just created you can look at Listing 6.

Listing 3
Creating a TEMPORARY tablespace


Listing 4
Creating a TEMPORARY tablespace

                 TEMPFILE 'C:\ORACLE\ORADATA\HCMC\ts02.tmp' SIZE 100M;

Listing 5
Specifying the DB_CREATE_FILE_DEST parameter

alter system set db_create_file_dest = 'C:\ORACLE\ORADATA\HCMC';

Listing 6
Outcome of creating TEMPORARY tablespaces

-SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;

TABLESPACE_NAME   FILE_NAME                                         BYTES/1024/102
----------------- ------------------------------------------------  --------------
TEMP02            C:\ORACLE\ORADATA\HCMC\TS02.TMP                   100



The default temporary tablespace is nothing more than a temporary tablespace that has been designated a global temporary tablespace for all users that have not been explicitly defined a temporary tablespace at user creation time. You can specify a default temporary tablespace by issuing a statement such as in Listing 7.

Listing 7
Specify the DEFAULT TEMPORARY Tablespace


Now if we were to create a user, their temporary tablespace would be TEMP01. The best thing about using default temporary tablespaces is that if you were to switch the default temporary tablespace, all users that were assigned the initial default temporary tablespace would inherit the new default temporary tablespace. Take a look at Listing 8 to see the progression of switching a default temporary tablespace and the users' inheriting it.

Listing 8
Users inheriting the DEFAULT TEMPORARY Tablespace

-SQL> CREATE USER tempuser IDENTIFIED BY tempuser;
User created.

-SQL> select username,temporary_tablespace from dba_users where username = 'TEMPUSER';
------------------------------ ------------------------------
TEMPUSER                       TEMP01

Database altered.

-SQL> select username,temporary_tablespace from dba_users where username = 'TEMPUSER';
------------------------------ ------------------------------
TEMPUSER                       TEMP02

The DEFAULT Tablespace

Ok, too much of a good thing. Now the last remaining problem with creation of a user is the DEFAULT tablespace. When we create a user and do not specify the default tablespace, the user is assigned the SYSTEM tablespace. Look at Listing 9. I personally will look to Oracle to change this very soon. I understand the reasoning behind assigning temporary tablespaces and default tablespaces to the SYSTEM tablespace in earlier versions. Most of the systems were not that large and many installations were just running with the SYSTEM tablespace. The unfortunate thing is that today, a single tablespace cannot hold all the data and performance is much more important in the systems of today.

Listing 9
Creation of a user being assigned a default tablespace of SYSTEM

-SQL> select username,temporary_tablespace,default_tablespace 
        from dba_users where username = 'TEMPUSER';

--------------- ------------------------------ ------------------------------
TEMPUSER        TEMP02                         SYSTEM

In previous releases of Oracle, if you forgot to assign a temporary tablespace to a user, the SYSTEM tablespace was used. This could and did cause some major contention and performance issues. Oracle has always stated that it was a bad practice to assign the SYSTEM tablespace to a users' default or temporary tablespace. While getting the DEFAULT tablespace assignment still requires some intervention, the assigning of a TEMPORARY tablespaces has become a bit less worrisome.

» See All Articles by Columnist James Koopmann

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved