Oracle Temporary Tablespace Resize

 Description:-

In this article I'm going to see how to resize Temp Tablespace on PDB database level

Demo:-

Login in to PDB Database

SQL> alter session set container=ORCLPDB;

Session altered.

First Check Temporary Tablespace Usage

From PDB Level:-

SELECT A.tablespace_name tablespace, D.GB_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_used,D.GB_total , SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 / 1024 GB_total
FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size
) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.GB_total;

TABLESPACE GB_TOTAL GB_USED GB_FREE
—————————— ———- ———- ———-
TEMP               .034179688            0             .034179688

From CDB level:-

To find CDB and PDB temporary tablespace files

set lines 100 pages 100
col db_name for a20
col tablespace_name for a20
col file_name for a40
SELECT vc2.name “db_name”,tf.file_name, tf.tablespace_name,
autoextensible, maxbytes/1024/1024/1024 “Max_GB”, SUM(tf.bytes)/1024/1024/1024 “GB_SIZE”
FROM v$containers vc2, cdb_temp_files tf WHERE vc2.con_id = tf.con_id
GROUP BY vc2.name,tf.file_name, tf.tablespace_name, autoextensible, maxbytes
ORDER BY 1, 2;

Increase Temp Tablespace 2 options

Alter Database Tempfile Resize

SQL> select name from v$tempfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf

SQL> alter database tempfile ‘/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf’ resize 50m;

Database altered.

Add Tempfile to the tablespace

SQL> alter tablespace TEMP add tempfile ‘/u01/app/oracle/oradata/ORCL/orclpdb/temp02.dbf’ SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/temp02.dbf

How to find Current Size of Temporary Tablespace

SQL> select sum(bytes)/1024/1024/1024 gb from dba_temp_files where tablespace_name = ‘TEMP’;

GB
———-
1.034179688

Information about Temporary Tablespaces and Tempfiles

To find which sessions are using temp space

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 database level

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

To find the objects held in the TEMP tablespace.

select srt.tablespace, srt.segfile#,srt.segblk#, srt.blocks,a.sid, a.serial#, a.username,a.osuser, a.status
from  v$session a,v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#,srt.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;