Setting a Default Temporary Tablespace in Oracle Database

calendar Feb 3, 2024 / · 7 min read · Tablespace Management Temporary Tablespace database_properties dba_users dba_tablespaces dba_temp_files  ·
Share on: linkedin copy

Setting a Default Temporary Tablespace in Oracle Database

Purpose

Every Oracle database needs one answer to a plain question: where does temporary work go for a user who has no temporary tablespace of their own? The database default temporary tablespace is that answer. A new installation creates a single temporary tablespace named TEMP, and users who are not explicitly assigned a temporary tablespace use the database default.

The statement below changes that default for the whole database. It is a one-line change, but it affects every user who relies on the default, so a DBA should know who that is before running it. This post shows the statement, explains each part, and adds read-only queries to check the setting before and after the change.

Code

1alter database default temporary tablespace temp
2/

The trailing / is the SQL*Plus terminator. It is not part of the SQL statement.

Code Breakdown

  • alter database starts a change to a database-level setting. The statement needs the ALTER DATABASE system privilege.
  • default temporary tablespace is the clause that sets the database default. The Administrator's Guide gives the general form as ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;.
  • temp is the name of the tablespace to make the default. TEMP is also the name of the temporary tablespace that a new installation creates.

Checks to run around the statement

The statement above is the original script. The queries below are added read-only checks. They are not part of the script.

Find the current default before the change:

1SELECT property_name, property_value
2FROM   database_properties
3WHERE  property_name = 'DEFAULT_TEMP_TABLESPACE';

DATABASE_PROPERTIES lists permanent database properties, and the Administrator's Guide uses this query to show the current default temporary tablespace.

List which tablespaces are temporary:

1SELECT tablespace_name, contents
2FROM   dba_tablespaces
3WHERE  contents = 'TEMPORARY';

DBA_TABLESPACES describes all tablespaces in the database, and its CONTENTS column shows whether a tablespace holds PERMANENT or TEMPORARY data, among other values.

See what each user is assigned:

1SELECT username, temporary_tablespace
2FROM   dba_users
3ORDER  BY username;

DBA_USERS describes all users of the database. Its TEMPORARY_TABLESPACE column holds the name of the default tablespace for temporary tables, or the name of a tablespace group.

Check the tempfiles behind a tablespace:

1SELECT tablespace_name, file_name, bytes
2FROM   dba_temp_files
3ORDER  BY tablespace_name, file_name;

DBA_TEMP_FILES describes all temporary files (tempfiles) in the database.

Key Points

  • The default applies only to users who are not explicitly assigned a temporary tablespace. A user created or altered with a specific temporary tablespace uses that one. The statement is therefore a default for the database, not a rule for every session.
  • The default can be a single temporary tablespace or a tablespace group. The Administrator's Guide says you can give a tablespace group name wherever a tablespace name would appear when you assign the database default.
  • You cannot drop a single temporary tablespace while it is the database default. If a tablespace group is the default, you cannot drop its member tablespaces. Remove a tablespace from the group first.
  • If SYSTEM is locally managed, the database must have a default temporary tablespace defined at creation. A locally managed SYSTEM tablespace cannot be used for default temporary storage.
  • In a multitenant database, the clause DEFAULT [ LOCAL ] TEMPORARY TABLESPACE is listed among the clauses that, from the root container, modify the root and set default values for the pluggable databases. They need the commonly granted ALTER DATABASE privilege.

Insights and Best Practices

Use a dedicated temporary tablespace

Temporary segments are created and dropped often. The Concepts guide says the best practice is to create at least one special tablespace for temporary segments. The database then distributes I/O across disks and avoids fragmenting SYSTEM and other tablespaces with temporary segments.

What the default actually controls

The Concepts guide states that the database creates temporary segments in the temporary tablespace assigned to the user. It gives an example. If user1 is assigned temp1 and user2 is assigned temp2, then user1 stores temporary data in temp1 segments and user2 in temp2 segments. The database default is the value those users fall back to when no assignment was made.

Temporary tables follow the same rule. The Administrator's Guide notes that when you create a temporary table, its rows are stored in your default temporary tablespace unless you create the table in a new temporary tablespace.

Prefer locally managed tablespaces

The Administrator's Guide says that because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. It also notes that on some operating systems the database does not allocate space for a tempfile until the blocks are actually accessed. The tempfile can look small on disk and still need more disk space later, so check free space on the file system.

Think about a tablespace group

A tablespace group lets a user consume temporary space from several tablespaces. The Administrator's Guide says this can help when one tablespace is too small to hold the result of a sort, particularly on a table with many partitions. It also lets parallel execution servers in one parallel operation use multiple temporary tablespaces. You do not create a group on its own. It is created when you assign the first temporary tablespace to it, and it is removed when the last member leaves. The view DBA_TABLESPACE_GROUPS lists groups and their members.

Watch temporary space use

The Administrator's Guide describes how temporary space behaves. The first statement after startup that sorts in the temporary tablespace creates a sort segment, and that segment is released only at shutdown. When a sort finishes, its extents are marked free for reuse and are not deallocated. The DBA_TEMP_FREE_SPACE view shows total allocated and free space for each temporary tablespace. A locally managed temporary tablespace with a lot of unused space can be shrunk manually.

Local temporary tablespaces

Starting with Oracle Database 12c Release 2, local temporary tablespaces are available. The Administrator's Guide uses "temporary tablespace" to mean a shared temporary tablespace unless it says otherwise, and so does this post.

When to Use This Check / When to Run This

  • After a database is created or cloned, to make sure the default points at the temporary tablespace you intend.
  • When you add a new temporary tablespace or tablespace group and want users who rely on the default to use it.
  • Before you drop a temporary tablespace, because the default cannot be dropped. Move the default first.
  • During a review of space use, to compare DBA_USERS assignments with the database default.
  • In a multitenant database, when you set the default from the root and need to know how it carries to the pluggable databases.

Troubleshooting Common Issues

You cannot drop a temporary tablespace. The Administrator's Guide says a single temporary tablespace cannot be dropped while it is the default, and members of a default tablespace group cannot be dropped either. Run the statement in this post with a different tablespace or group name, confirm the new value with the DATABASE_PROPERTIES query, then drop the old one.

Some users did not move. Query DBA_USERS. A user whose TEMPORARY_TABLESPACE column names a specific tablespace was assigned one explicitly. Change that user with ALTER USER if it should follow the default. The Administrator's Guide says you can assign a temporary tablespace to each user with CREATE USER or ALTER USER.

The new default looks unused. Temporary space is only used when an operation cannot run in memory. The Concepts guide says the database does not create a temporary segment if an operation can be done in memory, and allocates one on disk only when memory is not enough. You expect redo from temporary work. The Administrator's Guide says redo is never generated for temporary segments or in temporary tablespaces, whatever the logging attribute. The Concepts guide adds that changes to temporary segments are not recorded in the online redo log, except for space management operations on the temporary segment.

References

Posts in this series