达梦提供几种函数来查看表或索引占用的数据页数，TABLE_USED_PAGES、TABLE_USED_SPACE、INDEX_USED_PAGES、INDEX_USED_SPACE。

查询表的碎片化率：
SELECT OBJNAME AS OBJNAME,
OBJTYPE AS OBJTYPE,
TO_CHAR(FRAGPCT) AS FRAGPCT
FROM
(SELECT * FROM
(SELECT
OWNER||’.’||TABLE_NAME AS OBJNAME,
‘TABLE/TABLE PART’ AS OBJTYPE,
ROUND(100.0*(1-TABLE_USED_PAGES(OWNER,TABLE_NAME)/1.0/TABLE_USED_SPACE(OWNER,TABLE_NAME)),2) FRAGPCT
FROM DBA_TABLES
WHERE TABLESPACE_NAME NOT IN (‘TEMP’,‘ROLL’,‘SYSTEM’)
AND OWNER NOT IN (‘SYS’,‘SYSAUDITOR’,‘SYSSSO’,‘SYSJOB’,‘SCHEDULER’)
AND TEMPORARY=‘N’
AND TABLE_USED_SPACE(OWNER,TABLE_NAME)>(SELECT SUM(TOTAL_SIZE)*0.0001 FROM V$DATAFILE)
ORDER BY TABLE_USED_SPACE(OWNER,TABLE_NAME) DESC LIMIT 10)
ORDER BY FRAGPCT DESC LIMIT 10);

查询索引的碎片化率：
SELECT OBJNAME AS OBJNAME,
OBJTYPE AS OBJTYPE,
TO_CHAR(FRAGPCT) AS FRAGPCT
FROM
(SELECT * FROM

(SELECT
OWNER||’.’||INDEX_NAME AS OBJNAME,
‘INDEX/INDEX PART’ AS OBJTYPE,
ROUND(100.0*(1-INDEX_USED_PAGES(OWNER,INDEX_NAME)/1.0/INDEX_USED_SPACE(OWNER,INDEX_NAME)),2) FRAGPCT
FROM DBA_INDEXES
WHERE TABLESPACE_NAME NOT IN (‘TEMP’,‘ROLL’,‘SYSTEM’)
AND OWNER NOT IN (‘SYS’,‘SYSAUDITOR’,‘SYSSSO’,‘SYSJOB’,‘SCHEDULER’)
AND TEMPORARY=‘N’
AND INDEX_TYPE != ‘CLUSTER’
AND INDEX_USED_SPACE(OWNER,INDEX_NAME)>(SELECT SUM(TOTAL_SIZE)* 0.0001 FROM V$DATAFILE)
ORDER BY INDEX_USED_SPACE(OWNER,INDEX_NAME) DESC)
ORDER BY FRAGPCT DESC LIMIT 10);

以上两种查询碎片化率的方法都是基于数据库查询当前数据页来计算的，但是这样有种弊端，并不能反映出重建表或索引前后的一个差异，经了解Oracle上查询碎片化率的方式是通过估算数据理论上占用的空间大小，和当前实际占用的空间进行比较，这也给达梦查询碎片化率提供了思路。
由于达梦统计信息中有列平均长度的字段col_avg_len，我们可以通过这个字段和表中数据行数来估算数据的理论占用空间，sql如下：
select *, 100 * round(1-“理论大小(KB)”/“实际大小(KB)”,4) || ‘%’ as “碎片率”
from
–实际表数据空间
(SELECT
A.OWNER AS “模式”,
A.SEGMENT_NAME AS “表名”,
A.BYTES/1024.00 AS “实际大小(KB)”,
A.TABLESPACE_NAME AS “所属表空间”,
B.COMMENTS AS “表注释”
FROM DBA_SEGMENTS A,DBA_TAB_COMMENTS B
WHERE A.OWNER=B.OWNER
AND A.SEGMENT_NAME = B.TABLE_NAME
AND A.OWNER=‘VTAX’ --替换为需要查询的模式名
–and A.SEGMENT_NAME=’’ --可以替换为需要查询的表名
and A.SEGMENT_TYPE=‘TABLE’) aa
inner join
–理论空间
(select sum(a.col_avg_len * a.t_total) / 1024.00 as “理论大小(KB)”,
b.name
from sysstats a
inner join SYSOBJECTS b
on a.id=b.id
and b.subtype='UTAB' --and b.name in ('' ) --可以替换为需要查询的表名 and b.schid in (select id from SYSOBJECTS where name='VTAX' --替换为需要查询的模式名 and type=‘SCH’)
group by b.name) bb on aa.“表名”=bb.name
order by 3 desc, 8 desc limit 20;
后经测试此方法准确率并非100%，比较依赖统计信息的收集情况，需要提前收集全列的统计信息，如果表的数据量很大，或者统计信息比较陈旧，计算出来的结果也不准确。
我们可以换一种估算理论空间大小的方式，通过lengthb函数，将需要查的表的所有字段都通过lengthb函数去计算数据所占字节，然后和表中数据条数相乘，这样计算出来的结果就会比较准确了，但是这种方式编写sql复杂且运行效率较低，对于数据量很大或者字段较多的表会比较不友好，拼接查询的sql如下：

select bb.v_sql2||’/’ || c.BYTES/1024.00 || ‘, 4)*100||’’%’’ from ‘||bb.schemaname||’.’||bb.tablename
from
(
select aa.schemaname,aa.tablename,‘select round(1- (’||LISTAGG(v_sql,’’)||‘0.00’|| ‘) / 1024.00 ’ as v_sql2
from
(select c.name as schemaname,a.name as colname,b.name as tablename,‘ifnull(sum(lengthb(’||a.name||’)),0) + ’ as v_sql
from SYSCOLUMNS a
inner join SYSOBJECTS b
on a.id=b.id
and b.subtype='UTAB' and b.name in ('CS_CUSTINFO') --可以替换为需要查询的表名 inner join SYSOBJECTS c on b.schid=c.id and c.name='VTAX' --替换为需要查询的模式名 and c.type=‘SCH’
) aa group by aa.schemaname,aa.tablename ) bb
left join DBA_SEGMENTS c
on c.OWNER=bb.schemaname
and c.SEGMENT_NAME=bb.tablename
and c.SEGMENT_TYPE=‘TABLE’;