查询表信息，表的容量等信息
为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:
【操作系统】：
【CPU】:
【问题描述】*：
原MySQL：
SELECT
table_schema as database_name,
table_name AS table_name,
TABLE_ROWS AS table_rows,
IFNULL(index_length,0) AS indexVolume,
IFNULL(data_length,0) AS dataVolume,
IFNULL((data_length + index_length),0) AS tableVolume
FROM information_schema.TABLES
- 其中database_name、table_name、table_rows是从SYS.ALL_TABLES查询吗，但NUM_ROWS为什么会没有值?
SELECT OWNER AS "database_name", TABLE_NAME AS "table_name", NUM_ROWS AS "table_rows" FROM SYS.ALL_TABLES
- 索引长度indexVolume、数据长度dataVolume、表的总容量tableVolume这三个值如何获取，需要不通过dba权限的表获取
达梦all_tables视图中获取表记录数实际是从sysstat统计信息系统表关联得到的，统计信息如果未收集或者有延迟，数据也不会准确，建议使用达梦的系统函数获取表记录数和表大小，
例如：查看SYSDBA模式下LOG_COMMIT表的行数、表大小（已包含其所有索引使用大小）、IX_001索引大小
SELECT TABLE_ROWCOUNT('SYSDBA', 'LOG_COMMIT') as 行数,
TABLE_USED_SPACE('SYSDBA', 'LOG_COMMIT')*page/1024.0/1024 as MB,
index_USED_SPACE('SYSDBA', 'IX_001')*page/1024.0/1024 as MB;
更多系统函数用法参见sql手册附录3：
https://eco.dameng.com/document/dm/zh-cn/pm/sql-appendix
也可以通过查dba_segments表看表已分配的空间大小
SELECT
tablespace_name,
segment_name,
segment_type,
SUM(bytes) / 1024 /1024 AS used_mb
FROM
dba_segments
WHERE
owner = 'YOUR_SCHEMA_NAME'
AND segment_type = 'TABLE'
GROUP BY
tablespace_name,
segment_name,
segment_type
ORDER BY
used_mb DESC;
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
你测试一下这个SQL是否能满足需要，但需要有SOI、VTI角色授权
SELECT USR.NAME AS USER_NAME ,SCH.NAME AS SCHEMA_NAME ,TAB.NAME AS TABLE_NAME ,TABLE_ROWCOUNT(SCH.NAME,TAB.NAME) AS NUM_ROWS ,ROUND(TABLE_USED_PAGES(SCH.NAME,TAB.NAME) * PAGE / 1024.0 / 1024.0,2) AS TABLE_SIZE_MB ,ROUND(TABLE_USED_LOB_PAGES(SCH.NAME,TAB.NAME) * PAGE / 1024.0 / 1024.0,2) AS LOB_SIZE_MB ,IDX.CLUSTER_SIZE_MB AS DATA_SIZE_MB ,IDX.INDEX_SIZE_MB ,IDX.USED_TABLESPACE FROM SYSOBJECTS TAB JOIN SYSOBJECTS SCH ON SCH.TYPE$ = 'SCH' AND SCH.ID = TAB.SCHID JOIN SYSOBJECTS USR ON USR.TYPE$ = 'UR' AND USR.ID = SCH.PID JOIN (SELECT IDX.PID AS TABLE_ID ,SUM(NVL(DECODE(BITAND(IDXINFO.XTYPE,0x01),0,ROUND(INDEX_USED_PAGES(IDX.ID) * PAGE / 1024.0 / 1024.0,2)),0)) AS CLUSTER_SIZE_MB ,SUM(NVL(DECODE(BITAND(IDXINFO.XTYPE,0x01),1,ROUND(INDEX_USED_PAGES(IDX.ID) * PAGE / 1024.0 / 1024.0,2)),0)) AS INDEX_SIZE_MB ,LISTAGG(DISTINCT TS.NAME,',') WITHIN GROUP (ORDER BY TS.ID) AS USED_TABLESPACE FROM SYSOBJECTS IDX JOIN SYSINDEXES IDXINFO ON IDXINFO.ID = IDX.ID JOIN V$TABLESPACE TS ON TS.ID = IDXINFO.GROUPID WHERE IDX.TYPE$ = 'TABOBJ' AND IDX.SUBTYPE$ = 'INDEX' GROUP BY IDX.PID ) IDX ON IDX.TABLE_ID = TAB.ID WHERE TAB.TYPE$ = 'SCHOBJ' AND TAB.SUBTYPE$ IN ('STAB','UTAB') --AND SCH.NAME = '模式名' --AND TAB.NAME = '表名' ORDER BY TAB.NAME