What This Article Covers

• How to analyze an oversized TEMP tablespace
• How to check TEMP usage across Oracle RAC instances
• How to identify active TEMP consumers
• How to create a smaller temporary tablespace on ASM
• How to change the default temporary tablespace
• How to validate user TEMP assignments
• How to safely remove the old TEMP tablespace
• How to optionally rename the new TEMP tablespace
• Important considerations for CDB/PDB, RAC, ASM and TDE environments

Example Scenario

In this example, the DBA wants to replace the existing 500 GB TEMP tablespace with a smaller 120 GB TEMP tablespace while retaining the ability to automatically grow it up to 200 GB if workload requirements increase.

1. Why Not Simply Resize TEMP?

Oracle provides TEMP-specific shrink operations, which can be appropriate when you only need to reduce the size of an existing tempfile.

However, when the objective is to completely replace an oversized TEMP configuration and deliberately recreate it with a known initial size, creating a new temporary tablespace can provide a cleaner operational procedure.

The replacement approach is:

OLD TEMP
   |
   | 500 GB
   |
   v
CREATE TEMP_NEW
   |
   | 120 GB
   |
   v
MAKE TEMP_NEW DEFAULT
   |
   v
VALIDATE RAC + USERS
   |
   v
DROP OLD TEMP
   |
   v
RECLAIM ASM STORAGE
   |
   v
OPTIONALLY RENAME TEMP_NEW TO TEMP

2. Step 1 – Verify Oracle RAC Instances

Connect to the database as a privileged administrator:

sqlplus / as sysdba

Check all RAC instances:

SELECT
    INST_ID,
    INSTANCE_NAME,
    HOST_NAME,
    STATUS
FROM GV$INSTANCE
ORDER BY INST_ID;

Example:

INST_ID  INSTANCE_NAME  HOST_NAME   STATUS
-------  -------------  ----------- -------
1        DB1_1          RACNODE01   OPEN
2        DB1_2          RACNODE02   OPEN

Always consider all RAC instances when analyzing TEMP usage.

3. Step 2 – Check Existing TEMP Files

SELECT
    TABLESPACE_NAME,
    FILE_ID,
    FILE_NAME,
    ROUND(BYTES/1024/1024/1024,2) AS SIZE_GB,
    AUTOEXTENSIBLE,
    ROUND(MAXBYTES/1024/1024/1024,2) AS MAX_GB
FROM DBA_TEMP_FILES
ORDER BY TABLESPACE_NAME, FILE_ID;

Example:

TABLESPACE_NAME  FILE_ID  FILE_NAME
---------------  -------  -----------------------------
TEMP             1        +DATA/DB1/TEMPFILE/temp01.dbf

SIZE_GB  AUTOEXTENSIBLE  MAX_GB
-------  --------------  -------
500      YES             500

This query tells you the physical TEMP allocation and whether the tempfile can automatically grow.

4. Step 3 – Check Current TEMP Utilization

SELECT
    TABLESPACE_NAME,
    ROUND(TABLESPACE_SIZE/1024/1024/1024,2) AS TOTAL_GB,
    ROUND(FREE_SPACE/1024/1024/1024,2) AS FREE_GB,
    ROUND(
        (TABLESPACE_SIZE-FREE_SPACE)/1024/1024/1024,
        2
    ) AS USED_GB,
    ROUND(
        (TABLESPACE_SIZE-FREE_SPACE)*100/TABLESPACE_SIZE,
        2
    ) AS USED_PCT
FROM DBA_TEMP_FREE_SPACE;

Example:

TABLESPACE_NAME  TOTAL_GB  FREE_GB  USED_GB  USED_PCT
---------------  --------  -------  -------  --------
TEMP             500       470      30       6

5. Step 4 – Check TEMP Consumers Across RAC

Before replacing TEMP, identify sessions currently consuming temporary space.

SELECT
    INST_ID,
    TABLESPACE,
    COUNT(*) AS ACTIVE_SESSIONS,
    ROUND(
        SUM(BLOCKS) *
        (SELECT VALUE
         FROM V$PARAMETER
         WHERE NAME = 'db_block_size') /
        1024/1024/1024,
        2
    ) AS TEMP_GB
FROM GV$SORT_USAGE
GROUP BY INST_ID, TABLESPACE
ORDER BY INST_ID, TEMP_GB DESC;

For detailed session-level information:

SELECT
    u.INST_ID,
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.SQL_ID,
    s.PROGRAM,
    u.TABLESPACE,
    ROUND(
        u.BLOCKS * p.VALUE / 1024/1024/1024,
        2
    ) AS TEMP_GB
FROM GV$SORT_USAGE u
JOIN GV$SESSION s
  ON u.INST_ID = s.INST_ID
 AND u.SESSION_ADDR = s.SADDR
 AND u.SESSION_NUM = s.SERIAL#
CROSS JOIN (
    SELECT VALUE
    FROM V$PARAMETER
    WHERE NAME = 'db_block_size'
) p
ORDER BY TEMP_GB DESC;

If a large SQL statement is actively consuming TEMP, do not proceed with the TEMP replacement until the workload has been evaluated.

6. Step 5 – Check the Default Temporary Tablespace

SELECT
    PROPERTY_NAME,
    PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

Typical result:

PROPERTY_NAME              PROPERTY_VALUE
-------------------------  -------------
DEFAULT_TEMP_TABLESPACE    TEMP

7. Step 6 – Check User TEMP Assignments

Users can have temporary tablespaces explicitly assigned to them. Therefore, changing the database default does not necessarily mean every user will automatically use the new TEMP.

SELECT
    USERNAME,
    TEMPORARY_TABLESPACE
FROM DBA_USERS
ORDER BY TEMPORARY_TABLESPACE, USERNAME;

For a specific user:

ALTER USER APPUSER TEMPORARY TABLESPACE TEMP_NEW;

Do not blindly change every user. Some applications may intentionally use dedicated temporary tablespaces.

8. Step 7 – Check Whether the Database Is a CDB

Oracle Database 19c environments commonly use multitenant architecture.

SELECT
    NAME,
    CDB
FROM V$DATABASE;

If the result is:

NAME   CDB
-----  ---
DB1    YES

Check the containers:

SHOW CON_NAME;

SELECT
    CON_ID,
    NAME,
    OPEN_MODE
FROM V$CONTAINERS
ORDER BY CON_ID;

9. Step 8 – Check ASM Free Space

Before creating the replacement TEMP, confirm that ASM has enough free space.

SELECT
    NAME,
    TOTAL_MB,
    FREE_MB,
    ROUND(FREE_MB*100/TOTAL_MB,2) AS FREE_PCT
FROM V$ASM_DISKGROUP
ORDER BY NAME;

For example, if the new TEMP requires 120 GB, ASM must have sufficient free capacity to accommodate the new tempfile before the old TEMP is removed.

10. Step 9 – Check TDE / Encryption Requirements

If Transparent Data Encryption is used, verify the existing TEMP configuration and ensure that the replacement TEMP follows your organization’s encryption requirements.

SELECT
    TABLESPACE_NAME,
    ENCRYPT_IN_BACKUP
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'TEMP';

11. Step 10 – Create the New TEMP Tablespace

Assume the target configuration is:

• Initial size: 120 GB
• Autoextend: YES
• Growth increment: 1 GB
• Maximum size: 200 GB

For ASM:

CREATE TEMPORARY TABLESPACE TEMP_NEW
TEMPFILE '+DATA'
SIZE 120G
AUTOEXTEND ON
NEXT 1G
MAXSIZE 200G;

Using a defined MAXSIZE gives the DBA a useful upper boundary instead of allowing TEMP to potentially consume uncontrolled ASM capacity.

12. Step 11 – Validate TEMP_NEW

SELECT
    TABLESPACE_NAME,
    FILE_ID,
    FILE_NAME,
    ROUND(BYTES/1024/1024/1024,2) AS SIZE_GB,
    AUTOEXTENSIBLE,
    ROUND(MAXBYTES/1024/1024/1024,2) AS MAX_GB
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = 'TEMP_NEW';

Expected:

TABLESPACE_NAME  SIZE_GB  AUTOEXTENSIBLE  MAX_GB
---------------  -------  --------------  -------
TEMP_NEW         120      YES             200

13. Step 12 – Change the Default Temporary Tablespace

Once TEMP_NEW has been successfully created:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Verify:

SELECT
    PROPERTY_NAME,
    PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

The result should now be:

DEFAULT_TEMP_TABLESPACE    TEMP_NEW

14. Step 13 – Validate User Assignments

SELECT
    USERNAME,
    TEMPORARY_TABLESPACE
FROM DBA_USERS
ORDER BY TEMPORARY_TABLESPACE, USERNAME;

If specific users are still assigned to the old TEMP and should use TEMP_NEW:

ALTER USER APPUSER TEMPORARY TABLESPACE TEMP_NEW;

15. Step 14 – Validate All RAC Instances

SELECT
    INST_ID,
    INSTANCE_NAME,
    HOST_NAME,
    STATUS
FROM GV$INSTANCE
ORDER BY INST_ID;

Then check temporary-space consumers:

SELECT
    INST_ID,
    TABLESPACE,
    COUNT(*) AS ACTIVE_SESSIONS
FROM GV$SORT_USAGE
GROUP BY INST_ID, TABLESPACE
ORDER BY INST_ID, TABLESPACE;

Confirm that the RAC instances are healthy and that application workloads can use TEMP_NEW.

16. Do Not Immediately Drop the Old TEMP

Switching the default temporary tablespace does not mean that every existing workload has instantly stopped using the old TEMP.

Before dropping the old TEMP:

• Check active TEMP consumers.
• Validate application sessions.
• Check scheduled jobs.
• Check ETL and reporting workloads.
• Check parallel processing workloads.
• Confirm the new TEMP has sufficient capacity.

If possible, perform the change during an approved maintenance window and validate the application’s normal workload before removing the old TEMP.

17. Step 15 – Final Pre-Drop Validation

Check all temporary files:

SELECT
    TABLESPACE_NAME,
    FILE_ID,
    FILE_NAME,
    ROUND(BYTES/1024/1024/1024,2) AS SIZE_GB
FROM DBA_TEMP_FILES
ORDER BY TABLESPACE_NAME, FILE_ID;

Check the default TEMP:

SELECT
    PROPERTY_NAME,
    PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

Check active TEMP usage:

SELECT
    INST_ID,
    TABLESPACE,
    COUNT(*) AS ACTIVE_SESSIONS
FROM GV$SORT_USAGE
GROUP BY INST_ID, TABLESPACE
ORDER BY INST_ID, TABLESPACE;

Before dropping the old TEMP, the desired state is:

DEFAULT TEMP = TEMP_NEW

TEMP_NEW = Available / In Use

TEMP = No active consumers

18. Step 16 – Drop the Old TEMP Tablespace

Once the old TEMP is confirmed to be unused and the new TEMP is fully operational:

DROP TABLESPACE TEMP
INCLUDING CONTENTS AND DATAFILES;

With ASM, the old tempfile is removed from the ASM storage as part of the operation, allowing the previously allocated space to become available again.

19. Step 17 – Optionally Rename TEMP_NEW to TEMP

If your organization’s naming convention requires the temporary tablespace to remain named TEMP, you can rename the new tablespace after removing the old one:

ALTER TABLESPACE TEMP_NEW RENAME TO TEMP;

Verify:

SELECT
    PROPERTY_NAME,
    PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

Then:

SELECT
    TABLESPACE_NAME,
    FILE_ID,
    FILE_NAME,
    ROUND(BYTES/1024/1024/1024,2) AS SIZE_GB,
    AUTOEXTENSIBLE,
    ROUND(MAXBYTES/1024/1024/1024,2) AS MAX_GB
FROM DBA_TEMP_FILES
ORDER BY TABLESPACE_NAME, FILE_ID;

20. Post-Change Validation

Check TEMP utilization

SELECT
    TABLESPACE_NAME,
    ROUND(TABLESPACE_SIZE/1024/1024/1024,2) AS TOTAL_GB,
    ROUND(FREE_SPACE/1024/1024/1024,2) AS FREE_GB,
    ROUND(
        (TABLESPACE_SIZE-FREE_SPACE)/1024/1024/1024,
        2
    ) AS USED_GB,
    ROUND(
        (TABLESPACE_SIZE-FREE_SPACE)*100/TABLESPACE_SIZE,
        2
    ) AS USED_PCT
FROM DBA_TEMP_FREE_SPACE;

Check RAC TEMP consumers

SELECT
    INST_ID,
    TABLESPACE,
    COUNT(*) AS ACTIVE_SESSIONS
FROM GV$SORT_USAGE
GROUP BY INST_ID, TABLESPACE
ORDER BY INST_ID, TABLESPACE;

Check ASM space

SELECT
    NAME,
    TOTAL_MB,
    FREE_MB,
    ROUND(FREE_MB*100/TOTAL_MB,2) AS FREE_PCT
FROM V$ASM_DISKGROUP
ORDER BY NAME;

The ASM free space should increase approximately by the amount of storage released from the old TEMP, subject to the actual storage configuration and ASM allocation behavior.

21. Complete Example

Suppose the environment currently looks like this:

Oracle RAC 19c
ASM Disk Group : +DATA

OLD TEMP
--------
Tablespace : TEMP
Size       : 500 GB

TARGET
------
Tablespace : TEMP_NEW
Initial    : 120 GB
Autoextend : YES
Increment  : 1 GB
Maximum    : 200 GB

Create the new TEMP:

CREATE TEMPORARY TABLESPACE TEMP_NEW
TEMPFILE '+DATA'
SIZE 120G
AUTOEXTEND ON
NEXT 1G
MAXSIZE 200G;

Switch the default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Validate:

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

After complete validation and confirmation that the old TEMP is no longer required:

DROP TABLESPACE TEMP
INCLUDING CONTENTS AND DATAFILES;

Optionally rename:

ALTER TABLESPACE TEMP_NEW RENAME TO TEMP;

22. Production DBA Checklist

23. Recommended Operational Flow

              ORACLE RAC 19c
                     |
                     v
              Assess TEMP Usage
                     |
                     v
          Review Historical Peak Usage
                     |
                     v
           Check GV$SORT_USAGE
                     |
                     v
            Check User TEMP Mapping
                     |
                     v
             Check CDB/PDB
                     |
                     v
             Check TDE / ASM
                     |
                     v
          +----------------------+
          | CREATE TEMP_NEW      |
          | 120 GB               |
          | MAXSIZE 200 GB       |
          +----------+-----------+
                     |
                     v
             Validate TEMP_NEW
                     |
                     v
       ALTER DATABASE DEFAULT TEMPORARY
              TABLESPACE TEMP_NEW
                     |
                     v
          Validate RAC + Users
                     |
                     v
             Monitor Workload
                     |
                     v
             Confirm Old TEMP
                  Unused
                     |
                     v
       DROP TABLESPACE TEMP
       INCLUDING CONTENTS
       AND DATAFILES
                     |
                     v
              ASM Storage
               Reclaimed
                     |
                     v
        Optional Rename to TEMP
                     |
                     v
              Final Validation

24. Key Takeaways

• Do not size TEMP based only on current usage. Historical and peak workload requirements are more important.
• Always check TEMP usage across all RAC instances.
• Review DBA_USERS before dropping the old TEMP.
• Validate CDB/PDB configuration in multitenant environments.
• Check ASM free space before creating the replacement TEMP.
• Use controlled AUTOEXTEND/MAXSIZE settings where appropriate.
• Do not immediately drop the old TEMP after changing the default.
• Validate the application workload before physically removing the old TEMP.

Frequently Asked Questions

1. Can I reduce the size of an Oracle TEMP tablespace?

Yes. Oracle provides TEMP-specific shrink functionality, and another controlled approach is to create a new smaller temporary tablespace, switch the default temporary tablespace to it, and then remove the old TEMP after validation.

2. Is it safe to drop TEMP in an Oracle RAC database?

It can be safe when another valid temporary tablespace is available and has been made the default, but you should first verify RAC-wide TEMP usage, user assignments, application workload and container configuration.

3. Does changing the default TEMP automatically change every user’s temporary tablespace?

Not necessarily. Users can have an explicitly assigned temporary tablespace. Review DBA_USERS.TEMPORARY_TABLESPACE before removing the old TEMP.

4. How do I check TEMP usage across Oracle RAC?

Use the global dynamic performance views such as GV$SORT_USAGE rather than checking only the local instance.

5. Can I create TEMP_NEW on ASM?

Yes. For an ASM-managed Oracle RAC database, a temporary tablespace can be created with its tempfile in an ASM disk group such as +DATA, subject to the database’s storage configuration and policies.

6. Should TEMP use AUTOEXTEND?

This depends on the workload and operational standards. If AUTOEXTEND is enabled in production, using a defined increment and MAXSIZE can provide better control over storage consumption than an unrestricted configuration.

7. Should I rename TEMP_NEW back to TEMP?

It is optional. Renaming can be useful if your organization’s operational standards expect the default temporary tablespace to have the conventional name TEMP.

8. What is the biggest mistake when reducing TEMP?

The most common mistake is sizing TEMP based only on today’s utilization. A database that normally uses 30 GB may temporarily require significantly more space during month-end, reporting, ETL or parallel processing.

Conclusion

Replacing an oversized TEMP tablespace is a useful Oracle DBA storage-management technique, particularly when a large amount of unused TEMP has accumulated on ASM.

In an Oracle RAC 19c production environment, the safest approach is to treat the operation as a controlled change rather than simply deleting a large tempfile.

The recommended sequence is:

Assess
  ↓
Measure Current + Historical TEMP Usage
  ↓
Check RAC-Wide TEMP Consumers
  ↓
Review Users and CDB/PDB Configuration
  ↓
Check ASM / TDE
  ↓
Create Smaller TEMP
  ↓
Switch Default TEMP
  ↓
Validate
  ↓
Monitor
  ↓
Drop Old TEMP
  ↓
Reclaim ASM Storage
  ↓
Post-Change Validation

Always test the procedure in a non-production environment first and follow your organization’s change-management, backup, rollback and maintenance-window procedures.

Author: Jatindra Nath Borah
Focus: Oracle Database Administration, Oracle RAC, Oracle Data Guard, Multitenant, ASM and Enterprise Database Architecture