IT Solutions Technology Blog

WITH unit_table AS (
SELECT * FROM (VALUES (1, ‚B‘, 0, 899),
(2, ‚kB‘, 900, 9e5 – 1),
(3, ‚MB‘, 9e5, 9e8 – 1),
(4, ‚GB‘, 9e8, 9e100)
) t1 (id, unit, lo, hi)
)
SELECT f.file_id, ROUND (f.bytes / POWER (1024, u.id – 1), 2) AS f_size, u.unit
FROM dba_data_files f
JOIN unit_table u
ON f.bytes BETWEEN u.lo AND u.hi
ORDER BY f.file_id;