Understanding Temporary Tablespace Files in Oracle CDB and PDB Databases

In an Oracle Multitenant environment, managing temporary tablespaces across Container Databases (CDBs) and Pluggable Databases (PDBs) is essential for optimal performance. This article provides insights into retrieving detailed information about temporary tablespace files for CDBs and PDBs.

Query 1: Fetching Temporary Tablespace File Details

The following query retrieves details about the temporary tablespace files, including the database name, file name, tablespace name, auto-extend status, maximum file size, and current size in megabytes:


COL db_name FOR a10
COL tablespace_name FOR a10
COL file_name FOR a25

SELECT 
    vc2.name AS "db_name",
    tf.file_name,
    tf.tablespace_name,
    autoextensible,
    maxbytes / 1024 / 1024 AS "Max_MB",
    SUM(tf.bytes) / 1024 / 1024 AS "MB_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;

Sample Output

db_name	FILE_NAME	TABLESPACE	AUT	Max_MB	MB_SIZE
CDB$ROOT	C:\ORACLE\ORADATA\XE\TEMP01.DBF	TEMP	YES	32767	129
PDB1	C:\ORACLE\ORADATA\XE\PDB1\TEMP01	TEMP	NO	0	62
PDB2	C:\ORACLE\ORADATA\XE\PDB2\TEMP01	TEMP	NO	0	62

Explanation:

db_name: Database container name (CDB$ROOT or PDBs).
FILE_NAME: Path of the temporary tablespace file.
TABLESPACE: Tablespace name (e.g., TEMP).
AUT: Indicates if the file is auto-extensible.
Max_MB: Maximum file size allowed in megabytes.
MB_SIZE: Current size of the file in megabytes.

Query 2: Aggregated Temporary Tablespace Size

To get a consolidated view of the maximum and current sizes of temporary tablespaces across all containers, use the following query:


COL name FOR a10
COL tablespace_name FOR a15

SELECT  
    vc2.name, 
    tf.tablespace_name, 
    SUM(DECODE(autoextensible, 'NO', bytes, 'YES', maxbytes))
/1024/1024 AS "Max Bytes", 
    SUM(tf.bytes) / 1024 / 1024 AS "Current Size"
FROM 
    v$containers vc2, cdb_temp_files tf
WHERE 
    vc2.con_id = tf.con_id
GROUP BY 
    vc2.name, tf.tablespace_name
ORDER BY 
    1, 2;

Output

NAME	TABLESPACE_NAME	Max Bytes	Current Size
CDB$ROOT	TEMP	32767	129
PDB1	TEMP	62	62
PDB2	TEMP	62	62

Explanation:

NAME: Name of the container (CDB$ROOT or PDBs).
TABLESPACE_NAME: Temporary tablespace name.
Max Bytes: Aggregated maximum size in megabytes (includes auto-extend settings).
Current Size: Current size of the temporary tablespace files.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Understanding Temporary Tablespace Files in Oracle CDB and PDB Databases