Question & Answer
Question
How to find which tables are consuming more space in a disk?
Cause
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.
Answer
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. The query 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.
** Please Note I had no large tables so I made mine 10 MB to show that it works and believe this is what your looking for, please see the output below:**
| 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 > 100 order by allocated_mbytes desc, table_name,db_name, dsid; |
TABLE_NAME | DB_NAME | SPU_ID | DSID | ALLOCATED_MBYTES
---------------------------+---------+--------+------+------------------
NZ_CHECK_DISK_SCAN_SPEEDS | SYSTEM | 1137 | 1 | 513
NZ_CHECK_DISK_SCAN_SPEEDS | SYSTEM | 1137 | 2 | 513
NZ_CHECK_DISK_SCAN_SPEEDS | SYSTEM | 1129 | 3 | 513
NZ_CHECK_DISK_SCAN_SPEEDS | SYSTEM | 1129 | 4 | 513
(4 rows)
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21969371