Skip to content

Query to find the size of each database in Netezza environment

Please run below query to find the size of each database in Netezza environment. The output of this query will be the database name and total space allocated to the database.
This data is stored in system views under system database.

SELECT ORX.database::nvarchar(64) AS "DatabaseName",
case when sum(SOD.allocated_bytes) is null then 0 else SUM(SOD.allocated_bytes)/1073741824 end AS "AllocatedSpace_GB"
FROM _V_SYS_OBJECT_DSLICE_INFO SOD INNER JOIN _V_OBJ_RELATION_XDB ORX ON ORX.objid = SOD.tblid
GROUP BY "DatabaseName"
ORDER BY "DatabaseName"

Now if you want to know the free space available to the database as well then try below query:

SELECT ORX.database::nvarchar(64) AS "DatabaseName" ,
case when sum(SOD.used_bytes) is null then 0 else SUM(SOD.used_bytes)/1073741824 end AS "UseSpace_GB",
case when sum(SOD.allocated_bytes) is null then 0 else SUM(SOD.allocated_bytes)/1073741824 end AS "AllocatedSpace_GB",
current_timestamp as "CurrentTime"
FROM _V_SYS_OBJECT_DSLICE_INFO SOD INNER JOIN _V_OBJ_RELATION_XDB ORX ON ORX.objid = SOD.tblid
GROUP BY "CurrentTime", "DatabaseName"
ORDER BY "DatabaseName"

Comments

  1. Yordan .Georgiev says:

    SELECT ‘ALL’ AS DatabaseName
    , SUM ( UsedSpace_GB ) AS DatabaseName
    , SUM ( AllocatedSpace_GB ) AS UsedSpace_GB
    , current_timestamp as CurrentTime
    FROM (
    SELECT ORX.database::nvarchar(64) AS DatabaseName ,
    case when sum(SOD.used_bytes) is null then 0 else SUM(SOD.used_bytes)/1073741824 end AS UsedSpace_GB,
    case when sum(SOD.allocated_bytes) is null then 0 else SUM(SOD.allocated_bytes)/1073741824 end AS AllocatedSpace_GB,
    current_timestamp as CurrentTime
    FROM _V_SYS_OBJECT_DSLICE_INFO SOD INNER JOIN _V_OBJ_RELATION_XDB ORX ON ORX.objid = SOD.tblid
    GROUP BY CurrentTime, DatabaseName
    ) AS ALL_DB_SIZES
    UNION ALL
    SELECT ORX.database::nvarchar(64) AS DatabaseName ,
    case when sum(SOD.used_bytes) is null then 0 else SUM(SOD.used_bytes)/1073741824 end AS UsedSpace_GB,
    case when sum(SOD.allocated_bytes) is null then 0 else SUM(SOD.allocated_bytes)/1073741824 end AS AllocatedSpace_GB,
    current_timestamp as CurrentTime
    FROM _V_SYS_OBJECT_DSLICE_INFO SOD INNER JOIN _V_OBJ_RELATION_XDB ORX ON ORX.objid = SOD.tblid
    GROUP BY CurrentTime, DatabaseName
    ORDER BY UsedSpace_GB DESC
    ;