Query to find tables consuming more space in a disk – Netezza
Some times you may find that your Netezza data slice used space 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.
In order to make your task easy, you can use below query to find tables consuming more space in a disk. The below query will list all the tables in disk id 1,2,3,and 4 which are using more than 1000 MB on any of the disk. It will also provide valuable information like database name where that table exist, disk id as well as spu id in which this disk is residing.
You can alter the script by changing disk id as per your requirement.
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 dsid in (1,2,3,4) and allocated_mbytes > 1000 order by allocated_mbytes desc, table_name,db_name, dsid;