Blog

In Oracle databases, temp tablespaces are crucial for managing temporary data generated during operations like sorting, joining tables, and running complex queries. This blog post will guide you on how to add tempfile to temp tablespace in Oracle using SQL scripts and examples. Let’s open VirtualBox and get started!

1. First check the default temp tablespace:

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Here we can see that the default temp tablespace is TEMP. Now we have to check free space in the TEMP tablespace.

2. Check free space in TEMP tablespace:

col TABLESPACE_NAME format a20
SELECT TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024/1024 as TABLESPACE_SIZE_GB, ALLOCATED_SPACE/1024/1024/1024 as ALLOCATED_SPACE_GB, 
FREE_SPACE/1024/1024/1024 as FREE_SPACE_GB FROM  dba_temp_free_space;

If you need the size in MB, follow the below script:

col TABLESPACE_NAME format a20
SELECT TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024 as TABLESPACE_SIZE_MB, ALLOCATED_SPACE/1024/1024 as ALLOCATED_SPACE_MB, 
FREE_SPACE/1024/1024 as FREE_SPACE_MB FROM  dba_temp_free_space;

Here we can see 32MB of space has been allocated out of which 31MB is free. So no need to add space in the TEMP tablespace.

3. Check the existing temp files in the TEMP tablespace:

set pages 1000
set lines 1000
col TABLESPACE_NAME format a20
col file_name format a30
COLUMN status FORMAT A15
COLUMN CURRENT_SIZE_GB FORMAT 999.99
COLUMN MAX_SIZE_GB FORMAT 999.99
select tablespace_name,file_name,status,bytes/1024/1024/1024 AS CURRENT_SIZE_GB,maxbytes/1024/1024/1024 AS MAX_SIZE_GB,autoextensible from dba_temp_files order by file_name;

This query will provide a detailed breakdown of each temp file status, current size, and max size. We can also check if auto-extended is enabled or not. Please note that all the temp files can be extended till MAX_SIZE_GB.So if the tempfile can be extended then resize the tempfile otherwise add a new tempfile.

4. Resize a tempfile:

Before resizing, ensure that the filesystem or ASM disk group has sufficient space. Use the following commands to resize a tempfile:

ALTER DATABASE tempfile '<file_path>' RESIZE 1G;

Now we can see the Current temp file size is 1GB.

5. Add tempfile in the tablespace in Oracle:

First, verify if Oracle Managed Files (OMF) is enabled. If the DB_CREATE_FILE_DEST parameter is set to a directory path, It means OMF is enabled and Oracle will use this directory for creating and managing database files. You won’t need to specify a path when adding a new tempfile.

Case 1: OMF is not enabled:

If resizing is not feasible or if you prefer to add additional space, you can add a new temp file to the tablespace.

ALTER TABLESPACE <tablespace_name> ADD tempfile '<file_path>' SIZE <new_size>;

If you want the temp file to increase in size when it’s full automatically, you can use the AUTOEXTEND clause:

ALTER TABLESPACE <tablespace_name> ADD tempfile '<file_path>' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 5G;

Here SIZE 100M means the current tempfile size is 100M. AUTOEXTEND ON NEXT 10M means the tempfile will automatically extend by 10 megabytes whenever it runs out of space and MAXSIZE 5G specifies that the tempfile will not grow beyond 5 gigabytes.

Case 2: OMF is enabled:

If OMF is enabled then you can add a new temp file to the tablespace by using the below query:

ALTER TABLESPACE <tablespace_name> ADD tempfile SIZE <new_size>;

If you want to use the AUTOEXTEND clause, then use the below:

ALTER TABLESPACE <tablespace_name> ADD tempfile SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 5G;

I hope you found this article helpful. Feel free to reach out if you have any questions regarding how to add tempfile to temp tablespace in Oracle. If you want to add datafile to tablespace in Oracle then follow my other article Add datafile to tablespace in Oracle.