NZ Health

SELECT SUM(extent_count)*3/1024/1024 total_disk_space_tbytes,
SUM(extents_used)*3/1024/1024 allocated_disk_space_tbytes,
(SUM(extent_count) – SUM(extents_used))*3/1024/1024 free_disk_space_tbytes,
SUM(extents_used)/SUM(extent_count)*100 allocated_disk_space_prcnt,
100 – SUM(extents_used)/SUM(extent_count)*100 free_disk_space_prcnt,
MIN(extents_used*1.0/extent_count)*100 spu_storage_utilization_min,
AVG(extents_used*1.0/extent_count)*100 spu_storage_utilization_avg,
MAX(extents_used*1.0/extent_count)*100 spu_storage_utilization_max
FROM _vt_disk_partition
WHERE isprimary;

SELECT dsid,
SUM(extent_count)*3/1024 total_disk_space_gbytes,
SUM(extents_used)*3/1024 allocated_disk_space_gbytes,
(SUM(extent_count) – SUM(extents_used))*3/1024 free_disk_space_gbytes,
SUM(extents_used)/SUM(extent_count)*100 allocated_disk_space_prcnt,
100 – SUM(extents_used)/SUM(extent_count)*100 free_disk_space_prcnt
FROM _vt_disk_partition
WHERE isprimary
GROUP BY dsid
ORDER BY 5 DESC;
SELECT SUM(used_bytes)/1024/1024/1024/1024 used_bytes_tbytes,
SUM(allocated_bytes)/1024/1024/1024/1024 allocated_tbytes,
(SUM(allocated_bytes) – SUM(used_bytes))/1024/1024/1024/1024 deleted_tbytes,
100 – SUM(used_bytes*1.0)/SUM(allocated_bytes)*100 deleted_data_percent
FROM _v_sys_object_storage_size;
SELECT sod.dbname,
SUM(soss.used_bytes)/1024/1024/1024/1024 used_bytes_tbytes,
SUM(soss.allocated_bytes)/1024/1024/1024/1024 allocated_tbytes,
(SUM(soss.allocated_bytes) – SUM(soss.used_bytes))/1024/1024/1024 deleted_gbytes,
SUM(SUM(soss.allocated_bytes) – SUM(soss.used_bytes))OVER()/1024/1024/1024/1024 total_deleted_tbytes,
NVL(100 – SUM(soss.used_bytes*1.0)/NULLIF(SUM(soss.allocated_bytes), 0)*100, 0) deleted_data_percent
FROM _v_sys_database d
LEFT JOIN _v_sys_object_data sod ON d.objname = sod.dbname
LEFT JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
WHERE sod.objtype IN (‘TABLE’, ‘MATERIALIZED VIEW’, ‘MVIEW_STORE’)
GROUP BY sod.dbname
ORDER BY SUM(soss.allocated_bytes) – SUM(soss.used_bytes) DESC;
SELECT sod.dbname,
sod.owner objownerusername,
sod.objname,
sod.createdate,
sod.objtype,
soss.used_bytes/1024/1024/1024 used_gbytes,
soss.skew
FROM _v_sys_database d
LEFT JOIN _v_sys_object_data sod ON d.objname = sod.dbname
LEFT JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
WHERE sod.objtype IN (‘TABLE’, ‘MATERIALIZED VIEW’, ‘MVIEW_STORE’)
AND CAST(CAST(sod.createdate AS TIMESTAMP) AS DATE) >= CURRENT_DATE – 7
ORDER BY sod.createdate DESC;
SELECT sod.dbname,
sod.owner objownerusername,
sod.objname,
sod.createdate,
sod.objtype,
soss.used_bytes/1024/1024/1024 used_gbytes,
soss.skew
FROM _v_sys_database d
LEFT JOIN _v_sys_object_data sod ON d.objname = sod.dbname
LEFT JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
WHERE sod.objtype IN (‘TABLE’, ‘MATERIALIZED VIEW’, ‘MVIEW_STORE’)
AND soss.skew > 10
ORDER BY soss.skew DESC;