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