Master Note: Overview of Oracle Temporary Tablespaces (Doc ID 1498442.1)

In this Document Purpose   Details   Temporary Tablespaces   Creating a Temporary Tablespace   Altering a Temporary Tablespace   Shrinking Temporary Tablespaces   Default Temporary Tablespace   Temporary Tablespace Groups   Benefits of Temporary Tablespace Groups   Creating a Temporary Tablespace Group   Adding a Tablespace to a Temporary Tablespace Group   Setting a Group as the Default Temporary Tablespace for the Database   Assigning Temporary Tablespace Groups When Creating and Altering Users   Viewing Temporary Tablespace Group Information   Viewing Space Usage for Temporary Tablespaces   Temporary Segment Handling in Temporary Tablespace   12c database with Multitenant- Things to make a note of:   Known Issues   References   References APPLIES TO: Oracle Database - Enterprise Edition - Version 9.0.1.0 and later Information in this document applies to any platform. PURPOSE Temporary tablespaces contain data that persists only for the duration of a user’s session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users. DETAILS Temporary Tablespaces A temporary tablespace does exist on a permanent basis as do other tablespaces, such as the System and Sysaux tablespaces. However, the data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users. The performance of temporary tablespaces is extremely critical when your application uses sort- and hash-intensive queries, which need to store transient data in the temporary tablespace. After starting up an instance, the first statement that uses the temporary tablespace creates a sort segment, which is shared by all sort operations in the instance. When you shut down the database, the database releases this sort segment. You can query the V$SORT_SEGMENT view to review the allocation and deallocation of space to this sort segment. You can see who is currently using the sort segment by querying the V$SORT_USAGE view. Use the V$TEMPFILE and DBA_TEMP_FILES views to find out details about the tempfiles currently allocated to a temporary tablespace Creating a Temporary Tablespace You create a temporary tablespace the same way as you do a permanent tablespace, with the difference being that you specify the TEMPORARY clause in the CREATE TABLESPACE statement and substitute the TEMPFILE clause for the DATAFILE clause. Here’s an example: Example 1: SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'temp01.dbf' SIZE 50M AUTOEXTEND ON; Tablespace created. Example 2: SQL> CREATE TEMPORARY TABLESPACE temp3 TEMPFILE 'temp02.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; Tablespace created. In example 1 the SIZE clause in the second line specifies the size of the datafile and hence the size of the temporary tablespace, as 50MB. In the statement, the AUTOEXTEND ON clause will automatically extend the size of the temporary file, and thus the size of the temporary tablespace. By default, all temporary tablespaces are created with uniformly sized extents, with each extent sized at 1MB. In the example 2, the EXTENT MANAGEMENT clause is optional. The UNIFORM SIZE clause specifies a custom extent size of 16MB instead of the default extent size of 1MB. NOTE: You use the TEMPFILE clause, not the DATAFILE clause, when you allocate space to a temporary tablespace. Oracle recommends that you use a locally managed temporary tablespace with a 1MB uniform extent size as your default temporary tablespace. It is common to create a single temporary tablespace (usually named Temp) for each database, but you can have multiple temporary tablespaces, which are part of a temporary tablespace group, if your database needs them to support heavy sorting operations. In order to drop a default temporary tablespace, you must first use the ALTER TABLESPACE command to create a new default tablespace for the database. You can then drop the previous default temporary tablespace like any other tablespace. Note 409183.1 - Resizing (or Recreating) the Temporary Tablespace Altering a Temporary Tablespace You can issue the ALTER TEMPORARY TABLESPACE statement to perform various temporary tablespace management tasks, including adding a tempfile to grow a temporary tablespace. Below is an example showing how you can make the temporary tablespace larger: SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/tempfile/tempo3.dbf' size 1000M reuse; -- You can similarly use the following ALTER TABLESPACE command to resize a tempfile: SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/tempfile/temp03.dbf RESIZE 200M; -- And you can use the following statement to drop a tempfile and remove the operating system file: SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/tempfile/temp03.dbf' DROP INCLUDING DATAFILES; -- When you drop a tempfile belonging to a temporary tablespace, the tablespace itself will remain in use. Shrinking Temporary Tablespaces You may have to increase the size of a temporary tablespace to accommodate an unusually large job that makes use of the temporary tablespace. After the completion of the job, you can shrink the temporary tablespace using the SHRINK SPACE clause in an ALTER TABLESPACE statement. Here is an example: SQL> ALTER TABLESPACE temp SHRINK SPACE; Tablespace altered. SQL> -- The SHRINK SPACE clause will shrink all tempfiles to a minimum size, which is about 1MB. -- You can employ the KEEP clause to specify a minimum size for the tempfiles, as shown here: SQL> ALTER tablespace temp SHRINK SPACE KEEP 250m; -- Oracle uses a peculiar logic when shrinking tempfiles in a temporary tablespace. -- Let’s say you have a temporary tablespace that contains two 1GB tempfiles. -- You issue a command to shrink the tablespace to 1GB, as shown here: SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 1000M; Tablespace altered. SQL> -- If you query the V$TEMPFILE view, you will see this: SQL> SELECT file#, name, bytes/1024/1024 mb FROM v$tempfile; FILE# NAME                                        MB ----- ------------------------------------ --------- 1     /u01/app/oracle/tempfile/temp01.dbf   999.9375 2     /u01/app/oracle/tempfile/temp02.dbf'    1.0625 The database shrinks one of the two tempfiles all the way down to 1MB and the other only by 1MB, leaving 999MB of space intact in that tempfile.  If your goal is to shrink a particular tempfile down to a certain minimum, you can do so by specifying the name of the particular tempfile you want to shrink, as shown here: SQL> ALTER TABLESPACE temp SHRINK TEMPFILE tempfile '/u01/app/oracle/oradata/prod1/temp02.dbf' KEEP 100m; Tablespace altered. SQL> The ALTER TABLESPACE statement shown here shrinks just the tempfile you list by the amount you specify with the KEEP clause. It leaves the other tempfiles in the TEMP tablespace alone. The KEEP clause in the previous statement ensures that the specified tempfile retains 100MB of space.  The following example shows how to shrink a single tempfile without any specific retained space: SQL> ALTER TABLESPACE temp SHRINK tempfile '/u01/app/oracle/tempfile/temp03.dbf'; Since no KEEP clause is specified in the previous statement, the database shrinks the tempfile to the minimum possible size, which is about 1MB. For more information please review the notes below Note 452697.1 - How To Shrink A Temporary Tablespace in 11G ? Note 273276.1 - How to Shrink the Datafile of Temporary Tablespace Default Temporary Tablespace When you create database users, you must assign a default temporary tablespace in which they can perform their temporary work, such as sorting. If you neglect to explicitly assign a temporary tablespace, the users will use the critical System tablespace as their temporary tablespace, which could lead to fragmentation of that tablespace, besides filling it up and freezing database activity. You can avoid these undesirable situations by creating a default temporary tablespace for the database when creating a database by using the DEFAULT TEMPORARY TABLESPACE clause. Oracle will then use this as the temporary tablespace for all users for whom you do not explicitly assign a temporary tablespace. Please refer to the 'create database' command in the standard documentation at http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5004.htm#SQLRF01204 Note that if you did not create a default temporary tablespace while creating your database, it is not too late to do so later. You can just create a temporary tablespace, as shown in the preceding examples, and make it the default temporary tablespace for the database, with a statement like below. However, please note this default temporary tablespace is only used for new users. For existing users, you have to alter the user to change the their temporary tablespace. SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temptbs02; -- You can find out the name of the current default temporary tablespace for your database by executing the following query: SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME           PROPERTY_VALUE ----------------------- ----------------- DEFAULT_TEMP_TABLESPACE TEMP For more information please review: Note 138212.1 - 9i: Database DEFAULT TEMPORARY TABLESPACE and Restrictions Temporary Tablespace Groups Large transactions can sometimes run out of temporary space. Large sort jobs, especially those involving tables with many partitions, lead to heavy use of the temporary tablespaces, thus potentially leading to a performance issue. Oracle Database 10g introduced the concept of a temporary tablespace group, which allows a user to utilize multiple temporary tablespaces simultaneously in different sessions. Here are some of the main characteristics of a temporary tablespace group: A temporary tablespace group must consist of at least one tablespace. There is no explicit maximum number of tablespaces. If you delete all members from a temporary tablespace group, the group is automatically deleted as well. A temporary tablespace group has the same namespace as the temporary tablespaces that are part of the group. The name of a temporary tablespace cannot be the same as the name of any tablespace group. When you assign a temporary tablespace to a user, you can use the temporary tablespace group name instead of the actual temporary tablespace name.  You can also use the temporary tablespace group name when you assign the default temporary tablespace for the database. Benefits of Temporary Tablespace Groups Using a temporary tablespace group, rather than the usual single temporary tablespace, provides several benefits: SQL queries are less likely to run out of sort space because the query can now simultaneously use several temporary tablespaces for sorting. You can specify multiple default temporary tablespaces at the database level. Parallel execution servers in a parallel operation will efficiently utilize multiple temporary tablespaces. A single user can simultaneously use multiple temporary tablespaces in different sessions. Creating a Temporary Tablespace Group When you assign the first temporary tablespace to a tablespace group, you automatically create the temporary tablespace group. To create a tablespace group, simply specify the TABLESPACE GROUP clause in the CREATE TABLESPACE statement, as shown here: SQL> CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u01/oracle/oradata/temp01_01.dbf' SIZE 500M TABLESPACE GROUP tmpgrp1; The preceding SQL statement will create a new temporary tablespace, temp01, along with the new tablespace group named tmpgrp1. Oracle creates the new tablespace group because the key clause TABLESPACE GROUP was used while creating the new temporary tablespace. You can also create a temporary tablespace group by specifying the same TABLESPACE GROUP clause in an ALTER TABLESPACE command, as shown here: SQL> ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp1 The preceding statement will cause Oracle to create a new group named tmpgrp1, since there was no prior temporary tablespace group with that name. If you specify a pair of quotes ('') for the tablespace group name, you are implicitly telling Oracle not to allocate that temporary tablespace to a tablespace group. Here is an example: SQL> CREATE TEMPORARY TABLESPACE temp02 TEMPFILE '/u01/oracle/oradata/temp02_01.dbf' SIZE 500M TABLESPACE GROUP ''; The preceding statement creates a temporary tablespace called temp02, which is a regular temporary tablespace and does not belong to a temporary tablespace group. If you completely omit the TABLESPACE GROUP clause, you will also create a regular temporary tablespace, which is not part of any temporary tablespace group: SQL> CREATE TEMPORARY TABLESPACE temp03 TEMPFILE '/u01/oracle/oradata/temp03_01.dbf' SIZE 500M; Adding a Tablespace to a Temporary Tablespace Group As shown in the preceding section, you can add a temporary tablespace to a group by using the ALTER TABLESPACE command. You can also change which group a temporary tablespace belongs to by using the ALTER TABLESPACE command. For example, you can specify that the tablespace temp02 belongs to the tmpgrp2 group by issuing the following statement: SQL> ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp2; The database will create a new group with the name tmpgrp2 if there is no such group already. Setting a Group as the Default Temporary Tablespace for the Database You can use a temporary tablespace group as your default temporary tablespace for the database. If you issue the following statement, all users without a default tablespace can use any temporary tablespace in the tmpgrp1 group as their default temporary tablespaces: SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tmpgrp1; The preceding ALTER DATABASE statement assigns all the tablespaces in tmpgrp1 as the default temporary tablespaces for the database. Assigning Temporary Tablespace Groups When Creating and Altering Users When you create new users, you can assign them to a temporary tablespace group instead of to the single temporary tablespace. Here is an example: SQL> CREATE USER reda IDENTIFIED BY reda DEFAULT TABLESPACE users TEMPORARY TABLESPACE tmpgrp1; User created. SQL> Once you create a user, you can also use the ALTER USER statement to change the temporary tablespace group of the user. Here is a SQL statement that does this: SQL> ALTER USER reda TEMPORARY TABLESPACE tmpgrp2; Viewing Temporary Tablespace Group Information You can use the new DBA_TABLESPACE_GROUPS data dictionary view to query the temporary tablespace groups in your database. Here is a simple query on the view that shows the names of all tablespace groups: SQL> SELECT group_name, tablespace_name FROM dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ---------- --------------- TMPGRP1    TEMP01 SQL> You can also use the DBA_USERS view to find out which temporary tablespaces or temporary tablespace groups are assigned to each user. Here is an example: SQL> SELECT username, temporary_tablespace FROM dba_users; USERNAME TEMPORARY_TABLESPACE -------- --------------------- SYS      TEMP SYSTEM   TEMP SAM      TMPGRP1 SCOTT    TEMP For more information about temporary tablespace group please review Note 245645.1 - 10g: Temporary Tablespaces Group http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces002.htm#i1013552 Viewing Space Usage for Temporary Tablespaces The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command. SQL> SELECT * from DBA_TEMP_FREE_SPACE;   TABLESPACE_NAME                     TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ----------------------------------- --------------- --------------- ---------- TEMP                                      250609664       250609664  249561088 Temporary Segment Handling in Temporary Tablespace In RDBMS release 7.3, Oracle introduced the concept of a temporary tablespace.  This tablespace would be used to hold temporary objects, like sort segments.  For performance reasons, management of a tablespace that is defined as temporary was changed so that the allocated segments and extents would NOT be released and placed on the freelist, but would only be MARKED as free. For example, when a sort occurs, Oracle allocates as many extents as are required to do the sort. At the end of the sort, the extents are marked as free, but they are not deallocated. The single sort segment approach avoids unnecessary space management: A single sort segment is allocated and shared by multiple sort operations for a given instance. Allocations and deallocations of space from this new sort segment will not require standard ST enqueue. The first sort operation on the instance creates the sort segment. Subsequent sort operation may grow the sort segment by adding new extents or allocate the existing extents out of this segment. The sort segment eventually grows to a stable state where no extents are allocated No storage space deallocation after a sort operation, but only changing the status of the extents in the sort segments. As part of this, a new data structure was created. It is called the Sort Extent Pool and is allocated out of the Shared Pool in the SGA. This structure contains a description of all of the active sort segments and their extents in the sort segment. Processes that require access to the sort segment are synchronized by a local latch called Sort Extent Pool latch. The Sort Extent Pool latch can be found in the V$LATCH and V$LATCHNAME views. As mentioned, there is a new tablespace type called TEMPORARY. By default, all tablespaces are created as PERMANENT. Permanent tablespaces can hold objects of all types, including temporary segments. Temporary segments created in a PERMANENT tablespace still follow the old algorithm and are cleaned up/removed by SMON after usage. Temporary tablespaces can ONLY hold sort segments, no other objects are allowed to be created in the tablespace. There are two ways a tablespace can be identified as TEMPORARY: With the CREATE TABLESPACE...TEMPORARY command Using ALTER TABLESPACE...TEMPORARY For more information please review the following note Note 73439.1 - Temporary Segment Handling in Temporary Tablespace 12c database with Multitenant- Things to make a note of: There is one default temporary tablespace at the entire CDB level and you can create multiple temporary tablespaces at the CDB level and like traditional database, only one can be default temp tablespace at the CDB level. Users can be explicitly assigned some temp tablespaces at PDB level. At the PDB level also, we can have the same structure like traditional one with multiple temp tablespaces and one default temp tablespaces. Users can be explicitly assigned some temp tablespaces at PDB level. If a user has temp tablespace assigned explicitly at the CDB and PDB level, then the temp tablespace assigned to it depends on the container(PDB or CDB) in which it is present currently. If a user is present in PDB but is not assigned any temp tablespace explicitly and the PDB also doesn't have any default temp tablespace, then the default temp tablespace at the CDB level is assigned to that user(where as in traditional one we have system tablespace assigned). Troubleshooting Note 1524594.1 Master Note: Troubleshooting Oracle Temporary Tablespaces  Note 1267351.1 TROUBLESHOOTING GUIDE (TSG) : ORA-1652: unable to extend temp segment Note 364417.1 How Can Temporary Segment Usage Be Monitored Over Time? Note 1069041.6 How to Find Creator of a SORT or TEMPORARY SEGMENT or Users Performing Sorts Note 317441.1 How Do You Find Who And What SQL Is Using Temp Segments Known Issues Note 1271120.1 Temporary segments in permanent tablespaces aren't cleaned for a long time  Note 188610.1 DBA_FREE_SPACE Does not Show Information about Temporary Tablespaces  Note 422723.1 ORA-25153 Temporary Tablespace Is Empty  Note 132663.1 ORA-03296 Resizing Temporary Locally Managed Tablespace Bug 2784791 TEMPORARY LOBS NOT FREED AUTOMATICALLY AFTER PL/SQL BLOCK EXECUTION, Closed as not a bug BUG 5723140 TEMP SPACE NOT RELEASED AFTER COMMIT ON TABLE WITH XMLTYPE AND XSD References Note 160426.1  TEMPORARY Tablespaces : Tempfiles or Datafiles ? Note 1039341.6 Temporary Segments Are Not Being De-Allocated After a Sort Note 35513.1 Removing `stray` TEMPORARY Segments Note 50592.1 Extent Sizes for Sort, Direct Load and Parallel Operations Note 65973.1 Temporary Tablespaces and the Sort Extent Pool Note 68836.1 How to efficiently drop a table with many extents Note 47400.1 EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments Note 181132.1 Different Kinds of Temporary Segments Note 1384829.1  How to Release Temporary LOB Segments without Closing the JDBC Connection Note 228479.1  TEMPORARY LOBS are not freed up automatically after PL/SQL block execution Note 1384829.1  How to Release Temporary LOB Segments without Closing the JDBC Connection Note 5723140.8  Bug 5723140 - Temp LOB space not released after commit Note 1099324.1 DB 11.1: Temporary Tablespaces [Video]    REFERENCES NOTE:1524594.1 - Master Note: Troubleshooting Oracle Temporary Tablespaces

Related

Products

• Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Enterprise Edition > RDBMS

Keywords

TABLESPACE; TABLESPACES; TEMPORARY TABLESPACE