db2 表空间统计sql

数据库表空间统计sql:

表空间信息统计：

select substr(tbsp_name,1,20) as

TABLESPACE_NAME,substr(tbsp_content_type,1,10) as TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as TOTAL_MB, sum(tbsp_used_size_kb)/1024 as

USED_MB,sum(tbsp_free_size_kb)/1024 as FREE_MB,tbsp_page_size AS PAGE_SIZE

from SYSIBMADM.TBSP_UTILIZATION group by

tbsp_name,tbsp_content_type,tbsp_page_size

order by 1

表空间中table大小统计sql:

select t.tabschema,

t.tabname,

(t.DATA_OBJECT_P_SIZE + t.INDEX_OBJECT_P_SIZE +

t.LONG_OBJECT_P_SIZE +

t.LOB_OBJECT_P_SIZE + t.XML_OBJECT_P_SIZE) AS TOTAL_P_SIZE,

t.DATA_OBJECT_L_SIZE DATA_OBJECT_L_SIZE,

t.DATA_OBJECT_P_SIZE DATA_OBJECT_P_SIZE,

t.LONG_OBJECT_P_SIZE LONG_OBJECT_P_SIZE,

t.LOB_OBJECT_P_SIZE LOB_OBJECT_P_SIZE,

t.XML_OBJECT_P_SIZE XML_OBJECT_P_SIZE,

t.INDEX_OBJECT_P_SIZE INDEX_OBJECT_P_SIZE, t1.card,

t1.npages,

t1.fpages,

t1.tbspace

from SYSIBMADM.ADMINTABINFO t, syscat.tables t1 where t.tabname=t1.tabname and

t.tabschema=t1.tabschema and t1.tbspace in

( 'DATASPACE1','DATESPACE' )

order by TOTAL_P_SIZE desc