oracle数据同步dm7,DM7对Oracle 兼容之 SQL运维实战攻略下篇

DM7对Oracle 兼容之 SQL运维实战攻略(下篇)

——杨维克上周从日常维护的角度，对日常Oracle运维所用查询，分享了部分相应的DM7的解决/查询方式。本周就这一话题继续分享。

注：篇幅所限，不附上执行结果。

Oracle环境：

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Linux 系统：2.6.32-220.el6.x86_64

DM7环境：

DM Database Server x64 V7.1.5.95-Build(2016.06.28-70305)ENT

Linux 系统：2.6.32-220.el6.x86_64

11、查看object分类数量

Oracle 11g:

select decode (t1.TYPE#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,

'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(1) quantity from

SYS.OBJ$ t1 where t1.TYPE# > 1 group by decode (t1.TYPE#,1,'INDEX' , 2,'TABLE' , 3

, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' )

union

select 'COLUMN' , count(1) from SYS.COL$;

DM7：

select SUBTYPE$ object_type, count(1) quantity from SYSOBJECTS where SUBTYPE$ <>'' group by SUBTYPE$

union

select 'column',count(1) from SYSCOLUMNS;

12、按用户查看object种类(可查每个模式下的各对象数目)

Oracle 11g：

select .html schema, sum(decode(t1.TYPE#, 1, 1, NULL)) indexes,

sum(decode(t1.TYPE#, 2, 1, NULL)) tables, sum(decode(t1.TYPE#, 3, 1, NULL))

clusters, sum(decode(t1.TYPE#, 4, 1, NULL)) views, sum(decode(t1.TYPE#, 5, 1,

NULL)) synonyms, sum(decode(t1.TYPE#, 6, 1, NULL)) sequences,

sum(decode(t1.TYPE#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))

others from SYS.OBJ$ t1, .htmlER$ t2 where t1.TYPE# >= 1 and .htmlER# =

t1.OWNER# and .html <> 'PUBLIC' group by .html;

DM7：

select .html "schema",sum(decode(t.SUBTYPE$,'VIEW',1,0)) views, sum(decode(t.SUBTYPE$,'PROC',1,0)) procs, sum(decode(t.SUBTYPE$,'STAB',1,0)) stab,sum(decode(t.SUBTYPE$,'UTAB',1,0)) utabs,

sum(decode(t.SUBTYPE$,'SYNOM',1,0)) synoms,sum(decode(t.SUBTYPE$,'CONS',1,0)) conses,

sum(decode(t.SUBTYPE$,'INDEX',1,0)) indexes

FROM SYSOBJECTS t, (select ID, NAME from SYSOBJECTS where TYPE$='SCH') syssch

WHERE syssch.ID=t.SCHID group by .html;