How to Resize Temporary Tablespace in Oracle 11g Database

by

To resize temporary tablespace in an Oracle 11g database, you should follow the below given document approach, which includes prerequisites. Checking the current configuration, and then performing an operation to resize the temporary tablespace in an Oracle 11g database.

resize temporary tablespace

Below are the detailed steps for resizing the temporary tablespace:

Prerequisites:
You just ensure that you have administrative privileges, such as SYSDBA or equivalent privileges, to perform resize temporary tablespace and file management tasks.

Database in Open State:
Your database should be in an open state to perform the resize temporary tablespace operations. You can check the database status:
SQL> SELECT status FROM v$instance;

Sufficient Disk Space:
Make sure that there is enough free disk space on the filesystem where the temporary tablespace files are stored, especially if you are increasing the size.

Backup:
While resizing temporary tablespaces generally doesn’t affect real data, it’s good practice to have a recent backup of your database.

To find which sessions are using temporary tablespace:

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

To check which temporary tablespaces using the database:

select property_name, property_value from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’;

To find the objects in the temporary tablespace:

select sr.tablespace, sr.segfile#, sr.segblk#, sr.blocks, a.sid, a.serial#, a.username, a.osuser, a.status
from v$session a, v$sort_usage sr
where a.saddr = sr.session_addr
order by sr.tablespace, sr.segfile#, sr.segblk#, sr.blocks;

SELECT S.sid || ‘,’ || S.serial# sid_serial,S.username, T.blocks * 8192 / 1024 / 1024 /1024 gb_used,T.tablespace, Q.sql_fulltext, q.sql_id FROM v$sort_usage T join v$session S on T.session_addr = S.saddr left join v$sqlarea Q on T.sqladdr = Q.address ORDER BY mb_used desc, sid_serial;

Step 1: Check the Current Temporary Tablespace Configuration

To identify the Current Temporary Tablespace
SQL> select name from v$tempfile;
SQL> SELECT tablespace_name FROM dba_temp_files;

Now check the Temporary tablespace data file location and name

select FILE_NAME,TABLESPACE_NAME,sum(bytes)/1024/1024 Total_MB from dba_temp_files group by FILE_NAME,TABLESPACE_NAME;

First check the temporary tablespace total size, used space, and free space:

SELECT TABLESPACE_NAME,SUM(TABLESPACE_SIZE)/1024/1024 TOTAL_SIZE,
SUM(ALLOCATED_SPACE )/1024/1024 USED_SPACE,
SUM(FREE_SPACE)/1024/1024 SPACE_FREE FROM DBA_TEMP_FREE_SPACE
GROUP BY TABLESPACE_NAME,TABLESPACE_SIZE,ALLOCATED_SPACE,FREE_SPACE;

OR

SQL> SELECT tablespace_name, file_name, bytes/1024/1024/1024 AS size_in_gb FROM dba_temp_files;

Step 2: Now to resize Temporary Tablespace:
There are three methods to resize temporary tablespace:

  1. Resizing existing files
  2. Adding new files
  3. Recreating the tablespace.

Method 1: Resize an Existing Temporary File and check the File Path and Size:
SQL> SELECT file_name, bytes/1024/1024/1024 AS size_in_gb
FROM dba_temp_files
WHERE tablespace_name = ‘TEMP’;

To resize temporary tablespace:
Use the ALTER DATABASE command to resize temporary tablespace:
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ RESIZE 200M;
Replace ‘/u01/app/oracle/oradata/temp01.dbf’ with your actual path.
200M is the new size of the file. You can adjust this value as per your requirements.

Method 2: Add a New Temporary File:
To add a new temporary datafile to the tablespace.
If you add a new file instead of resizing the existing one.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 200M AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;;

Replace ‘/u01/app/oracle/oradata/temp01.dbf’ with the desired path for the new file.
200M specifies the size of the new file. You can adjust this value as per your requirements.

Method 3: Recreate the Temporary Tablespace:

If you need to change the temporary tablespace than consider to drop and recreate it.
Create a New Temporary Tablespace:
SQL> CREATE TEMPORARY TABLESPACE TEMPO TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 200M;

Assign the New Tablespace as Default:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPO;

Now drop the old temporary tablespace (temp):
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

If you want to rename the New Tablespace:
SQL> ALTER TABLESPACE temp_new RENAME TO temp;

Step 3: Verification
Check the New Configuration:

After resizing, verify and ensure that the changes took effect.
SQL> SELECT tablespace_name, file_name, bytes/1024/1024/1024 AS size_in_gb FROM dba_temp_files;

Monitor Usage:
SQL> SELECT tablespace_name, SUM(bytes_used)/1024/1024 AS used_mb, SUM(bytes_free)/1024/1024 AS free_mb
FROM v$temp_space_header
GROUP BY tablespace_name;

To resize temporary tablespace in an Oracle 11g database, completed all the steps and if you copy the sql queries, please execute it on test server.

Leave a Comment