Oracle’s Default TEMPORARY Tablespaces

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

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

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