Script to find Netezza database size

Reading Time: 2 minutes

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