oracle数据对比--用户,索引,分区,dblink,同义词,视图
问题描述：需要对比用户数据一般在数据库迁移之后，需要对比一下两个库之间的差距，如果登上去一条命令的执行，去统计，就会比较麻烦，这里整理了一些脚本可用。通过创建dblink的方式快速查询，也可以整合到一个脚本中spool到一个文本中
1.创建dblink
1.1 旧库修改tnsname到新库，在新库上添加旧库的tns配置
SQL> !tnsping fzcbdb9i Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.136.7.208)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fzcbdb))) OK (0 msec)
1.2 旧库创建有访问权限的用户
create user comp identified by comp default tablespace USERS; grant connect,resource,dba to comp;
1.3 新库创建dblink到旧库
create database link t_s_link connect to comp identified by comp using 'fzcbdb1'; select count(*) from dba_tables@t_s_link;
2.实例查询
select instance_name,status from v$instance; select instance_name,status from v$instance@t_s_link;
3.数据量大小对比
dba_segments SQL> select sum(bytes)/1024/1024/1024 as size_g from dba_segments; SIZE_G ---------- 7420.62244 SQL> select sum(bytes)/1024/1024/1024 as size_g from dba_segments@t_s_link; SIZE_G ---------- 8972.52301
4.用户对比
4.1创建用户对比
select username from all_users order by created;
select username from all_users@t_s_link order by created;
4.2用户下对象总数
SELECT OWNER, COUNT(*) FROM DBA_OBJECTS WHERE OWNER NOT IN ( 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS', 'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM') GROUP BY OWNER; SELECT OWNER, COUNT(*) FROM DBA_OBJECTS@t_s_link WHERE OWNER NOT IN ( 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS', 'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM') GROUP BY OWNER;
4.4用户对象类型
set pages 12222 lines 132 select c.OWNER,c.object_type,c.cntlinux,d.cntaix,c.cntlinux-d.cntaix diff from (select a.OWNER,a.object_type,count(*) cntlinux from dba_objects a where owner in ('DBSFWUSER','DVF','RIDP','FEX','CREDIT','ETL','TST','FDM','UPRR','JGDB','ACTIONUSER','DZDA','BKMONITOR','WKXTOAUSER','SENSORS','PUBLIC','OLAPSYS','SDM','ORACLE_OCM','AUDSYS','DVSYS','BP_QUERY','GSMADMIN_INTERN','AL','OJVMSYS','USE','GDM','APPQOSSYS','ORDDATA','BAK','SMARTBI','EXPDPUSER','REMOTE_SCHEDULE','R_AGENT') group by a.OWNER,a.OBJECT_TYPE) c,( select b.OWNER,b.object_type,count(*) cntaix from dba_objects b group by b.OWNER,b.OBJECT_TYPE) d where c.owner=d.owner(+) and c.object_type=d.object_type(+) order by c.owner,c.object_type / set pages 1222 lines 132 select c.OWNER,c.object_type,c.STATUS,c.cntlinux,d.cntaix,c.cntlinux-(decode(d.cntaix,null,0,cntaix)) diff from (select a.OWNER,a.object_type,a.status,count(*) cntlinux from dba_objects a where owner ='ODSSMIS' group by a.OWNER,a.OBJECT_TYPE,a.STATUS) c,( select b.OWNER,b.object_type,b.STATUS,count(*) cntaix from dba_objects@t_s_link b group by b.OWNER,b.OBJECT_TYPE,b.STATUS) d where c.owner=d.owner(+) and c.object_type=d.object_type(+) and c.status=d.status(+) order by c.owner,c.object_type /
4.5单一用户对象对比
select c.OWNER,c.object_type, cntlinux,cntaix ,cntlinux-cntaix diff from ( select a.OWNER,a.object_type,count(*) cntlinux from dba_objects a where a.owner ='FDM' group by a.OWNER,a.OBJECT_TYPE) c ,(select b.OWNER,b.object_type,count(*) cntaix from dba_objects b where b.owner ='FDM' group by b.OWNER,b.OBJECT_TYPE) d where c.owner=d.owner and c.object_type=d.object_type order by c.owner,c.object_type / select c.OWNER,c.object_type, cntlinux,cntaix ,cntlinux-cntaix diff from ( select a.OWNER,a.object_type,count(*) cntlinux from dba_objects@t_s_link a where a.owner ='FDM' group by a.OWNER,a.OBJECT_TYPE) c ,(select b.OWNER,b.object_type,count(*) cntaix from dba_objects b where b.owner ='FDM' group by b.OWNER,b.OBJECT_TYPE) d where c.owner=d.owner and c.object_type=d.object_type order by c.owner,c.object_type /
4.6单一用户表分区对比
col object_type for a20 col object_name for a25 select object_name,object_type,CREATED,TIMESTAMP,TEMPORARY from dba_objects where owner ='JGDB' and object_type='TABLE PARTITION'; col object_type for a20 col object_name for a25 select object_name,object_type,CREATED,TIMESTAMP,TEMPORARY from dba_objects@t_s_link where owner ='JGDB' and object_type='TABLE PARTITION';
4.7单一用户表分区数量对比
select count(0) from dba_objects where owner ='JGDB' and object_type='TABLE PARTITION'; select count(0) from dba_objects@t_s_link where owner ='JGDB' and object_type='TABLE PARTITION';
4.8表分区详细对比
col object_name for a35 select c.object_name, cntlinux,cntaix ,cntlinux-cntaix diff from (select count(0) cntaix,object_name from dba_objects where owner ='JGDB' and object_type='TABLE PARTITION' group by object_name) c, (select count(0) cntlinux,object_name from dba_objects where owner ='JGDB' and object_type='TABLE PARTITION' group by object_name) d where c.object_name=d.object_name(+) order by object_name; col object_name for a35 select c.object_name, cntlinux,cntaix ,cntlinux-cntaix diff from (select count(0) cntaix,object_name from dba_objects@t_s_link where owner ='JGDB' and object_type='TABLE PARTITION' group by object_name) c, (select count(0) cntlinux,object_name from dba_objects where owner ='JGDB' and object_type='TABLE PARTITION' group by object_name) d where c.object_name=d.object_name(+) order by object_name;
4.9无效对象
select c.OWNER,'INVALID' ,linuxcnt ,aixcnt,linuxcnt-aixcnt diff from (select o.OWNER, count(*) linuxcnt from dba_objects o where o.status='INVALID' and o.OWNER in ('DBSFWUSER','DVF','RIDP','FEX','CREDIT','ETL','TST','FDM','UPRR','JGDB','ACTIONUSER','DZDA','BKMONITOR','WKXTOAUSER','SENSORS','PUBLIC','OLAPSYS','SDM','ORACLE_OCM','AUDSYS','DVSYS','BP_QUERY','GSMADMIN_INTERN','AL','OJVMSYS','USE','GDM','APPQOSSYS','ORDDATA','BAK','SMARTBI','EXPDPUSER','REMOTE_SCHEDULE','R_AGENT') group by o.OWNER order by o.OWNER ) c, (select o.OWNER, count(*) aixcnt from dba_objects o where o.status='INVALID' and o.OWNER in ('DBSFWUSER','DVF','RIDP','FEX','CREDIT','ETL','TST','FDM','UPRR','JGDB','ACTIONUSER','DZDA','BKMONITOR','WKXTOAUSER','SENSORS','PUBLIC','OLAPSYS','SDM','ORACLE_OCM','AUDSYS','DVSYS','BP_QUERY','GSMADMIN_INTERN','AL','OJVMSYS','USE','GDM','APPQOSSYS','ORDDATA','BAK','SMARTBI','EXPDPUSER','REMOTE_SCHEDULE','R_AGENT') group by o.OWNER order by o.OWNER ) d where c.owner=d.owner(+) / select c.OWNER,'INVALID' ,linuxcnt ,aixcnt,linuxcnt-aixcnt diff from (select o.OWNER, count(*) linuxcnt from dba_objects o where o.status='INVALID' and o.OWNER in ('PICCSMIS','ODSUSER','ODSSMIS','PICCSMISREAD','VSMIS','DBAMONITOR','R7','FMIS_SMIS','FMIS_WRITEBACK','SMIS2MP','ECIF_SMIS','SUGGEST_SMIS','SMISCRM','XXX') group by o.OWNER order by o.OWNER ) c, (select o.OWNER, count(*) aixcnt from dba_objects@t_s_link o where o.status='INVALID' and o.OWNER in ('PICCSMIS','ODSUSER','ODSSMIS','PICCSMISREAD','VSMIS','DBAMONITOR','R7','FMIS_SMIS','FMIS_WRITEBACK','SMIS2MP','ECIF_SMIS','SUGGEST_SMIS','SMISCRM','XXX') group by o.OWNER order by o.OWNER ) d where c.owner=d.owner(+) /
4.10有效对象
select c.OWNER,'VALID' ,linuxcnt ,aixcnt,linuxcnt-aixcnt diff from (select o.OWNER, count(*) linuxcnt from dba_objects o where o.status='VALID' and o.OWNER in ('PICCSMIS','ODSUSER','ODSSMIS','PICCSMISREAD','VSMIS','DBAMONITOR','R7','FMIS_SMIS','FMIS_WRITEBACK','SMIS2MP','ECIF_SMIS','SUGGEST_SMIS','SMISCRM','XXX') group by o.OWNER order by o.OWNER ) c, (select o.OWNER, count(*) aixcnt from dba_objects o where o.status='VALID' and o.OWNER in ('PICCSMIS','ODSUSER','ODSSMIS','PICCSMISREAD','VSMIS','DBAMONITOR','R7','FMIS_SMIS','FMIS_WRITEBACK','SMIS2MP','ECIF_SMIS','SUGGEST_SMIS','SMISCRM','XXX') group by o.OWNER order by o.OWNER ) d where c.owner=d.owner / select c.OWNER,'VALID' ,linuxcnt ,aixcnt,linuxcnt-aixcnt diff from (select o.OWNER, count(*) linuxcnt from dba_objects o where o.status='VALID' and o.OWNER in ('PICCSMIS','ODSUSER','ODSSMIS','PICCSMISREAD','VSMIS','DBAMONITOR','R7','FMIS_SMIS','FMIS_WRITEBACK','SMIS2MP','ECIF_SMIS','SUGGEST_SMIS','SMISCRM','XXX') group by o.OWNER order by o.OWNER ) c, (select o.OWNER, count(*) aixcnt from dba_objects@t_s_link o where o.status='VALID' and o.OWNER in ('PICCSMIS','ODSUSER','ODSSMIS','PICCSMISREAD','VSMIS','DBAMONITOR','R7','FMIS_SMIS','FMIS_WRITEBACK','SMIS2MP','ECIF_SMIS','SUGGEST_SMIS','SMISCRM','XXX') group by o.OWNER order by o.OWNER ) d where c.owner=d.owner /
4.11diff view
set pages 12222 lines 132 col OBJECT_TYPE for a15 col object_name for a33 select * from (select object_name,object_type from dba_objects b where b.owner ='ODSSMIS' and object_type='VIEW' minus select object_name,object_type from dba_objects a where a.owner ='ODSSMIS' and object_type='VIEW') order by object_type,object_name / set pages 12222 lines 132 col OBJECT_TYPE for a15 col object_name for a33 select * from (select object_name,object_type from dba_objects@t_s_link b where b.owner ='ODSSMIS' and object_type='VIEW' minus select object_name,object_type from dba_objects a where a.owner ='ODSSMIS' and object_type='VIEW') order by object_type,object_name /
4.12用户索引
select index_name,table_name,owner from dba_indexes where table_owner in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC'); select index_name,table_name,owner from dba_indexes@t_s_link where table_owner in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC');
4.13用户对象权限，被赋予public用户的表
select * from dba_tab_privs where owner in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC') and grantee='PUBLIC'; select * from dba_tab_privs@t_s_link where owner in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC') and grantee='PUBLIC';
4.14用户权限
col owner format a10 col privilege format a10 select * from dba_sys_privs where grantee in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC') order by grantee; select * from dba_tab_privs where grantee in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC') order by grantee; select * from dba_role_privs where grantee in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC') order by grantee; col owner format a10 col privilege format a10 select * from dba_sys_privs@t_s_link where grantee in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC') order by grantee; select * from dba_tab_privs@t_s_link where grantee in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC') order by grantee; select * from dba_role_privs@t_s_link where grantee in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC') order by grantee;
4.15段类型统计
select segment_type,count(*) from dba_segments where owner='DEVELOP' group by segment_type; select count(*) from dba_segments where owner='DEVELOP'; select count(*) from dba_constraints where owner='DEVELOP'; select segment_type,count(*) from dba_segments@t_s_link where owner='DEVELOP' group by segment_type; select count(*) from dba_segments@t_s_link where owner='DEVELOP'; select count(*) from dba_constraints@t_s_link where owner='DEVELOP';
4.16约束统计
select constraint_type,count(*) from dba_constraints where owner='DEVELOP' group by CONSTRAINT_TYPE; select STATUS,COUNT(*) from dba_constraints where owner='DEVELOP' group by status; select constraint_type,count(*) from dba_constraints@t_s_link where owner='DEVELOP' group by CONSTRAINT_TYPE; select STATUS,COUNT(*) from dba_constraints@t_s_link where owner='DEVELOP' group by status;
5.db_link
col created format a10 col db_link format a25 select * from dba_db_links order by db_link; col created format a10 col db_link format a25 select * from dba_db_links@t_s_link order by db_link;
6.public的同义词
set linesize 1000 col SYNONYM_NAME format a40 col table_name format a40 select * from dba_synonyms where owner = 'PUBLIC' and TABLE_OWNER in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC'); set linesize 1000 col SYNONYM_NAME format a40 col table_name format a40 select * from dba_synonyms@t_s_link where owner = 'PUBLIC' and TABLE_OWNER in ('DEVELOP','DBUSER','HDCQRY','FINGER','DBQUERY','MCTC');
7.自动任务统计
select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT from dba_jobs; select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT from dba_jobs@t_s_link;
