【达梦数据库】统计达梦源库数据

背景

根据达梦官网ORACLE迁移到DM的统计数据量的案例，改写SQL用来统计达梦源库表信息

SQL

参考链接: 3.1.2 数据库调研
备注：LOB类型和物化视图未统计

--迁移对象统计
select a.username  用户,
        (select count(1) from dba_tables b where b.owner = a.username) 表数量,
        ( SELECT COUNT(1) FROM DBA_INDEXES I WHERE UNIQUENESS = 'UNIQUE' AND OWNER =A.USERNAME OR INDEX_NAME NOT LIKE 'SYS_%' AND OWNER =A.USERNAME) "索引数量",
        (select count(distinct c.table_name)
           from dba_tab_partitions c
          where c.table_owner = a.username) 分区表数量,
        (select count(1)
           from dba_tab_cols d
          where d.OWNER = a.username
            and d.DATA_TYPE like '%LOB%') 包含lob表数量,
--        (select sum(e.bytes) / 1024 / 1024 / 1024
--           from dba_extents e
--          where exists (select 1
--                   from dba_lobs f
--                  where f.owner = a.username
--                    and f.segment_name = e.segment_name)) lob占用空间,
        (select count(1) from dba_views g where g.OWNER = a.username) 视图数量,
        (select count(1) from dba_triggers h where h.owner = a.username) 触发器数量,
        (select count(DISTINCT I.NAME)
           from DBA_SOURCE I
          WHERE I.OWNER = A.username
            AND I.TYPE = 'FUNCTION') 函数数量,
        (select COUNT(1)
           FROM DBA_SEQUENCES j
          WHERE j.sequence_owner = A.username) 序列数量,
        (select count(1) from dba_synonyms where owner= A.username) 同义词,
--        (select COUNT(1) FROM DBA_MVIEWS K WHERE K.owner = A.username) 物化视图数量,
        (select count(DISTINCT l.NAME)
           from DBA_SOURCE L
          WHERE L.OWNER = A.username
            AND L.TYPE = 'PROCEDURE') 存储过程数量,
        (select COUNT(1) FROM DBA_DB_LINKS M WHERE M.owner = A.username) dblink数量,
        (select max(n.DATA_LENGTH)
           from dba_tab_cols n
          where n.OWNER = a.username) 最大单字段宽度,
        (select SUM(O.DATA_LENGTH)
           from dba_tab_cols o
          where o.OWNER = a.username
            and o.DATA_TYPE not like '%LOB%') 最大行宽度
   from dba_users a where username in ('用户');

--数据量
select distinct segment_type,sum(BYTES) / 1024 / 1024 / 1024 , COUNT(*) FROM DBA_SEGMENTS where owner = '用户' group by segment_type order by 2 desc;