Database size

–Including size of the temporary tables
SELECT sod.dbname,
SUM(oss.used_bytes)/1024/1024/1024/1024 used_tbytes,
SUM(oss.used_bytes)/1024/1024/1024 used_gbytes,
SUM(oss.used_bytes)/1024/1024 used_mbytes,
SUM(oss.used_bytes)/1024 used_kbytes
FROM _v_sys_object_data sod,
_v_sys_object_storage_size oss
WHERE sod.objid = oss.tblid
GROUP BY sod.dbname
ORDER BY SUM(oss.used_bytes) DESC;

–Excluding the size of the temporary tables
SELECT dbname,
allocated_tbytes,
allocated_gbytes,
allocated_mbytes,
used_tbytes,
used_gbytes,
used_mbytes,
SUM(allocated_tbytes) OVER() total_allocated_tbytes,
SUM(allocated_gbytes) OVER() total_allocated_gbytes,
SUM(allocated_mbytes) OVER() total_allocated_mbytes,
SUM(used_tbytes) OVER() total_used_tbytes,
SUM(used_gbytes) OVER() total_used_gbytes,
SUM(used_mbytes) OVER() total_used_mbytes
FROM (SELECT sod.dbname,
SUM(soss.allocated_bytes)/1024/1024/1024/1024 allocated_tbytes,
SUM(soss.allocated_bytes)/1024/1024/1024 allocated_gbytes,
SUM(soss.allocated_bytes)/1024/1024 allocated_mbytes,
SUM(soss.used_bytes)/1024/1024/1024/1024 used_tbytes,
SUM(soss.used_bytes)/1024/1024/1024 used_gbytes,
SUM(soss.used_bytes)/1024/1024 used_mbytes
FROM _v_sys_object_data sod
INNER JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
AND sod.objtype <> UPPER(‘TEMP TABLE’)
INNER JOIN _v_sys_database d ON sod.dbname = d.objname
GROUP BY sod.dbname
) t;

–Only size of the temporary tables
SELECT dbname,
allocated_tbytes,
allocated_gbytes,
allocated_mbytes,
used_tbytes,
used_gbytes,
used_mbytes,
SUM(allocated_tbytes) OVER() total_allocated_tbytes,
SUM(allocated_gbytes) OVER() total_allocated_gbytes,
SUM(allocated_mbytes) OVER() total_allocated_mbytes,
SUM(used_tbytes) OVER() total_used_tbytes,
SUM(used_gbytes) OVER() total_used_gbytes,
SUM(used_mbytes) OVER() total_used_mbytes
FROM (SELECT sod.dbname,
SUM(soss.allocated_bytes)/1024/1024/1024/1024 allocated_tbytes,
SUM(soss.allocated_bytes)/1024/1024/1024 allocated_gbytes,
SUM(soss.allocated_bytes)/1024/1024 allocated_mbytes,
SUM(soss.used_bytes)/1024/1024/1024/1024 used_tbytes,
SUM(soss.used_bytes)/1024/1024/1024 used_gbytes,
SUM(soss.used_bytes)/1024/1024 used_mbytes
FROM _v_sys_object_data sod
INNER JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
AND sod.objtype = UPPER(‘TEMP TABLE’)
INNER JOIN _v_sys_database d ON sod.dbname = d.objname
GROUP BY sod.dbname
) t;