Temporary Tablespaces - Oracle DBA

Temporary Tablespaces

Temporary tablespaces can improve the concurrency of multiple sort operations, reduce their overhead and avoid oracle database space management operations. A temporary tablespace can be assigned to users with the create user or alter user statement and can be shared by multiple users.

Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist for every instance that performs sort operations within a given tablespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown.

• You can view the allocation and deallocation of space in a temporary tablespace sort segment using the v$sort_segment view. The v$tempseg_usage view identifies the current sort users in those segments.
• You cannot explicitly create objects in a temporary tablespace.

Note: The exception to the preceding statement is a temporary table. When you create a temporary table, its rows are stored in your default temporary tablespace.

Creating a locally managed temporary tablespace:-

Locally managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data. Because of this, they enable you to perform on-disk sorting operations in a read-only or standby database.

To create a locally managed temporary tablespace, you use the following statement creates a temporary tablespace in which each extent is 16m. Each 16m extent (which is the equivalent of 8000 blocks when the standard block size is 2k) is represented by a bit in the bitmap for the file.

Sql> create temporary tablespace lmtemp tempfile

‘/u02/oracle/data/lmtemp01.dbf’   size 20m reuse extent management local uniform size 16m;

Note: On some operating systems, the database does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. Please refer to your operating system documentation to determine whether the database allocates tempfile space in this way on your system.

Creating a bigfile temporary tablespace:-

Just as for regular tablespaces, you can create single-file (bigfile) temporary tablespaces. Use the create bigfile temporary tablespace statement to create a single-tempfile tablespace.

Altering a locally managed temporary tablespace:-

Checkout Oracle DBA Interview Questions

Note: You cannot use the alter tablespace statement, with the temporary keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the create temporary tablespace statement to create a locally managed temporary tablespace.

Except for adding a tempfile, taking a tempfile offline, or bringing a tempfile online, as illustrated in the following examples, you cannot use the alter tablespace statement for a locally managed temporary tablespace.

Sql>alter tablespace lmtemp add tempfile ‘/u02/oracle/data/lmtemp02.dbf’ size 18m reuse;

Sql>alter tablespace lmtemp tempfile offline;

Sql>alter tablespace lmtemp tempfile online;

Note: You cannot take a temporary tablespace offline. Instead, you take its tempfile offline. The view v$tempfile displays online status for a tempfile.

However, the alter database statement can be used to alter tempfiles.

The following statements take offline and bring online tempfiles. They behave identically to the last two altertablespace statements in the previous example.

Sql>alter database tempfile ‘/u02/oracle/data/lmtemp02.dbf’ offline;

Sql>alter database tempfile ‘/u02/oracle/data/lmtemp02.dbf’ online;

The following statement resizes a temporary file:

Sql>alter database tempfile ‘/u02/oracle/data/lmtemp02.dbf’ resize 18m;

The following statement drops a temporary file and deletes the operating system file:

Sql>alter database tempfile ‘/u02/oracle/data/lmtemp02.dbf’ drop

Including datafiles;

It is also possible to use the alter database statement to enable or disable the automatic extension of an existing tempfile, and to rename (rename file) a tempfile.

Note: To rename a tempfile, you take the tempfile offline, use operating system commands to rename or relocate the tempfile, and then use the alterdatabaserenamefile command to update the database controlfiles.