背景
根据达梦官网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;