Check temporary tablespace of CDB or PDB databases

CDB or PDB temporary tablespace files details:

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

db_name    FILE_NAME                 TABLESPACE AUT     Max_MB    MB_SIZE
---------- ------------------------- ---------- --- ---------- ----------
CDB$ROOT   C:\ORACLE\ORADATA\XE\TEMP TEMP       YES      32767        129
           01.DBF

PDB1       C:\ORACLE\ORADATA\XE\PDB1 TEMP       NO           0         62
           \TEMP012019-09-18_15-38-0
           3-475-PM.DBF

PDB2       C:\ORACLE\ORADATA\XE\PDB2 TEMP       NO           0         62
           \TEMP012019-09-18_15-38-0
           3-475-PM.DBF

Check the aggregated size of temporary tablespace for CDB or PDB databases

Col name for a10
col tablespace_name for a15
SELECT  vc2.name, tf.tablespace_name, sum(decode(autoextensible,'NO',bytes,'YES',maxbytes))/1024/1024 "Max Bytes", SUM(tf.bytes)/1024/1024
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;

NAME       TABLESPACE_NAME  Max Bytes SUM(TF.BYTES)/1024/1024
---------- --------------- ---------- -----------------------
CDB$ROOT   TEMP                 32767                     129
PDB1       TEMP                    62                      62
PDB2       TEMP                    62                      62

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.