Temporary tablespaces in Oraclee 19c

In Oracle 19c, temporary tablespaces are used to manage temporary data such as sorting, hashing, and temporary table storage during SQL execution. Oracle uses the temporary tablespace to handle operations that require intermediate data storage, such as:

• Sorting data (e.g., ORDER BY, GROUP BY)
• Performing joins (e.g., hash joins)
• Creating indexes
• Managing Global Temporary Tables (GTTs)

Here’s how you can use and manage temporary tablespaces for sorting data in Oracle 19c:

1. Assign a Temporary Tablespace to a User
   Each user is assigned a temporary tablespace where sorting and temporary operations are performed. To check or assign a temporary tablespace:
   • Check the Current Temporary Tablespace:

     SELECT USERNAME, TEMPORARY_TABLESPACE
     FROM DBA_USERS
     WHERE USERNAME = 'YOUR_USERNAME';
             

   • Assign a Temporary Tablespace:

     ALTER USER YOUR_USERNAME TEMPORARY TABLESPACE temp;
             

     Replace YOUR_USERNAME with the specific user and temp with the desired temporary tablespace name.
2. Create or Modify a Temporary Tablespace
   You can create or modify a temporary tablespace to handle sorting operations more efficiently.
   • Create a Temporary Tablespace:

     CREATE TEMPORARY TABLESPACE temp_tablespace_name
     TEMPFILE '/path_to_tempfile/tempfile_name.dbf'
     SIZE 1G
     AUTOEXTEND ON
     NEXT 100M
     MAXSIZE UNLIMITED;
             

   • Modify a Temporary Tablespace:
     If you need to change its size or auto-extend settings:

     ALTER DATABASE TEMPFILE '/path_to_tempfile/tempfile_name.dbf' RESIZE 2G;
             

3. Use Temporary Tablespaces for Sorting Operations
   Oracle automatically uses the assigned temporary tablespace for sorting operations. You can influence sorting behavior and monitor its usage.
   • Example Query that Triggers Sorting:

     SELECT column_name
     FROM table_name
     ORDER BY column_name;
             

     This query will use the temporary tablespace if the sort exceeds the memory allocated for sorting.
4. Monitor Temporary Tablespace Usage
   To ensure optimal performance, monitor the usage of temporary tablespaces, especially in sorting-intensive operations.
   • Check Temporary Tablespace Usage:

     SELECT TABLESPACE_NAME, FILE_NAME, BYTES_USED, BYTES_FREE
     FROM V$TEMP_SPACE_HEADER;
             

   • View Sessions Using Temporary Space:

     SELECT SESSION_NUM, SEGMENT_NAME, TABLESPACE_NAME, BYTES
     FROM V$SORT_USAGE;
             

5. Tune Sorting Operations
   Oracle allows you to control sorting through memory allocation and temporary tablespaces.
   • Increase Sort Area Size:
     For large sorts, increasing the memory allocated to sorting can reduce reliance on temporary tablespaces. Adjust the following parameter (at the session level):

     ALTER SESSION SET PGA_AGGREGATE_TARGET = 512M;
             

6. Use Query Hints to Control Sorting
   If you want more control over sort operations, you can use hints to optimize sorting:

   SELECT /*+ ORDERED */ column_name
   FROM table_name
   ORDER BY column_name;
       

Best Practices

1. Monitor Temporary Tablespace Growth: Avoid running out of space during heavy sorting operations by enabling AUTOEXTEND.
2. Optimize Queries: Rewrite queries to minimize unnecessary sorting (e.g., remove redundant ORDER BY clauses).
3. Regular Maintenance: Use SHRINK SPACE for temporary tablespaces to reclaim unused space:

ALTER TABLESPACE temp SHRINK SPACE;

By following these steps, Oracle 19c will effectively use temporary tablespaces for sorting data and other intermediate operations.

You can improve database performance by doing sorting in temporary tablespaces instead of permanent ones. You can flag a tablespace as temporary by using the TEMPORARY keyword when you create the tablespace. You can also use the ALTER TABLESPACE statement to convert a tablespace into a temporary tablespace. Here are some examples:

CREATE TABLESPACE coin_sort
   DATAFILE 'e:\oracle\oradata\coin\coin_sort01.dat' SIZE 10k
   TEMPORARY;
   
ALTER TABLESPACE coin_sort TEMPORARY;

The first statement creates a tablespace as a temporary tablespace. The second statement alters an existing tablespace, making it temporary. The keyword PERMANENT may be used with the ALTER TABLESPACE statement to reverse the process and to change a tablespace from temporary to permanent. Once you have a temporary tablespace, you can assign users to that tablespace by using the TEMPORARY TABLESPACE clause with the CREATE USER and ALTER USER statements. For example:

ALTER USER coin_admin
TEMPORARY TABLESPACE coin_sort;

Oracle uses temporary tablespaces only for temporary objects such as sort segments. For more efficient operation, Oracle allocates sort segments to an instance, rather than to individual users. Sorts are often done in response to queries containing ORDER BY, GROUP BY, and DISTINCT clauses. By assigning users to temporary tablespaces, you ensure that those sorts are done as efficiently as possible. In the next lesson, you will learn how temporary tablespaces appear in the data dictionary.