Usage of space on HDDs

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 […]

Database objects size

SELECT sod.objid, sod.objclass, sod.objowner objownerusesysid, sod.owner objownerusername, sod.objname, sod.createdate, sod.objtype, d.objid dbobjid, sod.dbname, d.dbowner dbownerusesysid, d.owner dbownerusername, d.createdate dbcreatedate, soss.used_bytes, soss.block_size, soss.allocated_blocks, soss.allocated_bytes, soss.allocated_avg, soss.used_blocks, soss.used_bytes, soss.used_min, soss.used_max, soss.used_avg, soss.used_spread, […]

Temporary tables size

SELECT dbname, allocated_tbytes, allocated_gbytes, used_tbytes, used_gbytes, SUM(allocated_tbytes)OVER() total_allocated_tbytes, SUM(allocated_gbytes)OVER() total_allocated_gbytes, SUM(allocated_tbytes)OVER() total_used_tbytes, SUM(allocated_gbytes)OVER() total_used_gbytes 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.used_bytes)/1024/1024/1024/1024 used_tbytes, SUM(soss.used_bytes)/1024/1024/1024 used_gbytes FROM _v_sys_object_data sod INNER JOIN […]

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 […]

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 […]

Scewed tables

/* Query #1 for getting skewed table in all databases, order by skew desc Field SQL_FOR_ADDITIONAL_INFO can be used for getting object structure */ SELECT sod.objid, sod.createdate, sod.dbname, sod.owner objownerusername, […]

Select info about tables on skewed data slices

SELECT t.dsid, t.ds_prnct_used, t.ideal_ds_prnct_used, t.ds_prnct_used_delta, soss.allocated_bytes/1024/1024/1024 tot_tbl_allocated_gbytes, soss.used_bytes/1024/1024/1024 tot_tbl_used_gbytes, t.used_gbytes, t.ideal_used_gbytes, t.used_gbytes_delta, t.dis_prcnt, t.ideal_dis_prcnt, t.dis_prcnt_delta, sod.dbname, sod.owner objownerusername, sod.objname, sod.objtype, soss.used_min, soss.used_max, soss.used_avg, soss.used_spread, soss.skew, sod.createdate, t.tblid FROM ( SELECT […]

Select skewed data slices

SELECT dsid, ds_prnct_used, ideal_ds_prnct_used, (ds_prnct_used – ideal_ds_prnct_used) ds_prnct_used_delta, MIN(ds_prnct_used – ideal_ds_prnct_used)OVER(PARTITION BY NULL) ds_prnct_used_delta_min, MAX(ds_prnct_used – ideal_ds_prnct_used)OVER(PARTITION BY NULL) ds_prnct_used_delta_max, MAX(ds_prnct_used – ideal_ds_prnct_used)OVER(PARTITION BY NULL) – MIN(ds_prnct_used – ideal_ds_prnct_used)OVER(PARTITION BY […]

Select distribution method of the tables

–Current DB select current_db db_id, current_catalog db_name, t.oid, t.relname, t.reldistmethod reldistmethodnamecode, case t.reldistmethod when 6003 then ‘CENTRAL_HOST’ when 6005 then ‘RANDOM’ when 6004 then rtrim(nvl(d1.attname||’,’, ”)|| nvl(d2.attname||’,’, ”)|| nvl(d3.attname||’,’, ”)|| […]