SELECT /*+ ordered */ CAST(ddf.tablespace_name AS VARCHAR2(15)) tablespace, ddf.file_id file_id, CAST(ddf.file_name AS VARCHAR2(47)) filename, ddf.bytes/1024/1024/1024 filesize_gb, NVL((ddf.bytes - qt_dfs.bytes), ddf.bytes) used, TRUNC(((NVL((ddf.bytes - qt_dfs.bytes), ddf.bytes))/ddf.bytes) * 100) pct_used FROM sys.dba_data_files ddf, v$datafile df, (select file_id, SUM(bytes) bytes from sys.dba_free_space GROUP BY file_id ) qt_dfs WHERE ddf.file_id = qt_dfs.file_id (+) AND ddf.file_name = df.name UNION SELECT CAST(dtf.tablespace_name AS VARCHAR2(15)) tablespace, dtf.file_id file_id, CAST(dtf.file_name AS VARCHAR2(47)) filename, dtf.bytes/1024/1024/1024 filesize_gb, NVL(tep.bytes_cached, 0) used, TRUNC((tep.bytes_cached/dtf.bytes) * 100) pct_used FROM sys.dba_temp_files dtf, v$temp_extent_pool tep, v$tempfile tf WHERE dtf.file_id = tep.file_id (+) AND tf.file# = dtf.file_id; => Reference (file size in MB) Query Output (11gXE)
23416