SQL Query to list tables occupying more space in SPU and disk IBM Netezza

SQL Query to list tables occupying more space in SPU and disk IBM  Netezza

Find Table list in  your Netezza data slice  which have grown a lot and now you want to check which all tables are consuming space on those data slices/disk so that you can try to find if those tables needs to be groomed, etc.

This SQL provide tables list which are using more than 5000 MB on hwid 1009 disk only

select objname as table_name,       database as db_name,       hwid as spu_id, dsid,       (allocated_bytes/1048576)

as allocated_mbytes  from _v_sys_relation_xdb sys,       _v_sys_object_dslice_info ds

 where ds.tblid = sys.objid and allocated_mbytes > 500 and hwid=1009 order by  allocated_mbytes desc,

table_name, db_name, dsid;