DB2 TABLESPACE 表空间状态查询

原创已于 2025-06-27 18:21:58 修改 · 1.8k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

于 2023-08-16 10:09:26 首次发布

本文介绍了在DB2数据库中通过命令行和SQL查询两种方法查看表空间信息，包括名称、类型、状态和大小等详细信息。

部署运行你感兴趣的模型镜像

两种方法

1.命令行方式:

su - db2inst1
db2 connect to testdb
db2 list tablespaces

结果如下

           Tablespaces for Current Database

 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 3
 Name                                 = SYSTOOLSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 4
 Name                                 = MY_TABLESPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal

2.sql查询方式可以使用sql工具连接后查询

以下SQL仅适用于v11.5社区版，其他版本是否适用未知。

SELECT
    TU.TBSP_NAME,
    TU.TBSP_TYPE,
    TU.TBSP_STATE,
  	TS.OWNER,
  	TS.DEFINER,
    CAST((TU.TBSP_TOTAL_SIZE_KB / 1024) AS VARCHAR) || 'MB' AS TOTAL_SIZE_MB,
    CAST((TU.TBSP_USED_SIZE_KB / 1024) AS VARCHAR) || 'MB' AS USED_SIZE_MB,
    CAST((TU.TBSP_FREE_SIZE_KB / 1024) AS VARCHAR) || 'MB' AS FREE_SIZE_MB,
    TU.TBSP_TOTAL_PAGES,
	TU.TBSP_USABLE_PAGES,
	TU.TBSP_USED_PAGES,
	TU.TBSP_FREE_PAGES,
	TU.TBSP_PAGE_TOP,
	TU.TBSP_PAGE_SIZE
FROM
    SYSIBMADM.TBSP_UTILIZATION TU
LEFT JOIN SYSCAT.TABLESPACES TS ON TS.TBSPACEID = TU.TBSP_ID

查询结果如下