Run this SQL to find the total space allocated to a Netezza database as well as used space.
SQL to find databases without backups
1) Databases without Successful Full Backup for 15 days
Select unique a.dbname DatabaseName from ( select dbname, status, max(starttime) starttime from _v_backup_history where starttime> (CURRENT_TIMESTAMP-15) and OPTYPE='FULL' group by dbname,status ) a, ( select dbname, status, max(starttime) starttime from _v_backup_history where starttime> (CURRENT_TIMESTAMP-15) and status = 'FAILED' and OPTYPE='FULL' group by dbname,status ) b where a.dbname = b.dbname and b.starttime>=a.starttime
2) Databases without any backup – lifetime
select datname DatabaseName from _T_database where datname not in (select dbname from _v_backup_history) and datname not like 'MASTER_DB'
SQL to find tables consuming more space in a disk
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;
SQL to list all the objects owned by an user
select o.objname, d.database, oc.classname, u.username from _t_object o, _t_user u, _t_object_classes oc, _v_database d where u.username='USERNAME' and u.usesysid=o.objowner and o.objclass=oc.objclass and o.objdb=d.objid;
In case we need to change owner, use the below bash command:
bash$ nz_change_owner -from USERNAME -to ADMIN