How to Assign a Dedicated Temporary Tablespace to a User in Oracle

In Oracle databases, every user requires a temporary tablespace for sorting and other temporary operations. By default, users are assigned the default temporary tablespace (TEMP), but in some cases, you may want to provide a dedicated temporary tablespace for specific users. This guide covers how to create, assign, and manage temporary tablespaces, including both filesystem-based storage and Automatic Storage Management (ASM).

1. Creating a Dedicated Temporary Tablespace (Filesystem-Based)

If your Oracle database is using traditional filesystem-based storage, use the following command to create a new temporary tablespace:

CREATE TEMPORARY TABLESPACE temp_user1
TEMPFILE '/u01/app/oracle/oradata/ORCL/temp_user1.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Explanation of Parameters:

CREATE TEMPORARY TABLESPACE temp_user1 → Creates a new temporary tablespace named temp_user1.
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp_user1.dbf’ → Specifies the location of the tempfile.
SIZE 100M → Sets the initial size to 100MB.
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED → Enables auto-extension by 10MB increments up to an unlimited size.
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M → Uses local extent management with uniform 1MB extents.

2. Creating a Temporary Tablespace in ASM (+DATA)

If your database is using Oracle ASM (Automatic Storage Management), use the following command to create a temporary tablespace on an ASM disk group (e.g., +DATA):

CREATE TEMPORARY TABLESPACE temp_user1
TEMPFILE '+DATA'
SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Key Differences in ASM:

Instead of specifying a full file path, we simply specify the ASM disk group (+DATA).
Oracle will automatically manage the physical storage inside the ASM disk group.

3. Assigning the Temporary Tablespace to a User

Once the temporary tablespace is created, assign it to a user with the following command:

ALTER USER user1 TEMPORARY TABLESPACE temp_user1;

To verify that the user has been assigned the correct temporary tablespace, run:

SELECT username, temporary_tablespace FROM dba_users WHERE username = 'USER1';

4. Viewing All Temporary Tablespaces in the Database

To list all temporary tablespaces in the database, use:

SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';

To check details of a specific temporary tablespace:

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_MB, autoextensible FROM dba_temp_files;

5. Extracting the DDL of an Existing Temporary Tablespace

If you need to reference an existing temporary tablespace from another database, you can extract its DDL using the following query:

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'TEMP_USER1') FROM DUAL;

This will generate the DDL statement that was used to create the specified temporary tablespace, allowing you to replicate it in another database.

6. Dropping a Temporary Tablespace (If Needed)

If you need to remove a temporary tablespace, make sure no users are using it. First, change the users to another temporary tablespace:

ALTER USER user1 TEMPORARY TABLESPACE TEMP;

Then, drop the temporary tablespace:

DROP TABLESPACE temp_user1 INCLUDING CONTENTS AND DATAFILES;

Conclusion

By following these steps, you can successfully create and assign a dedicated temporary tablespace to a user in Oracle, whether using filesystem-based storage or ASM (+DATA). This approach ensures better resource management and performance tuning for specific users or workloads.

For enterprise environments, it’s a good practice to monitor temporary tablespaces and ensure they are properly allocated based on user requirements.

Let me know if you have any questions or need further clarification!