oracle数据对比--用户,索引,分区,dblink,同义词,视图

概述问题描述：需要对比用户数据一般在数据库迁移之后，需要对比一下两个库之间的差距，如果登上去一条命令的执行，去统计，就会比较麻烦，这里整理了一些脚本可用。通过创建dblink的方式快速查询，也可以整合到一

问题描述：需要对比用户数据一般在数据库迁移之后，需要对比一下两个库之间的差距，如果登上去一条命令的执行，去统计，就会比较麻烦，这里整理了一些脚本可用。通过创建dblink的方式快速查询，也可以整合到一个脚本中spool到一个文本中

 

1.创建dblink1.1 旧库修改tnsname到新库，在新库上添加旧库的tns配置

sql> !tnsPing fzcbdb9iUsed TNSnameS adapter to resolve the aliasAttempting 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;from v$instance@t_s_link;

 

 

 

3.数据量大小对比

dba_segmentssql> select sum(bytes)/1024/1024 as size_g  dba_segments;    SIZE_G----------7420.62244sql>  dba_segments@t_s_link;    SIZE_G----------8972.52301

 

4.用户对比4.1创建用户对比

select username  all_users order by created;
from all_users@t_s_link order by created;

 

 

 

4.2用户下对象总数

SELECT OWNER,COUNT(*)  FROM DBA_OBJECTS WHERE OWNER NOT IN ( SYS',OUTLNSYstemCTXSYSDBSNMP,LOGSTDBY_adminISTRATORORDSYSORdplUGINSOEM_MONITORWKSYSWKPROXYWK_TESTWKUSERmdsYSLBACSYSDMSYSWMSYSEXFSYSSYSMANMDDATASI_INFORMTN_SCHEMAXDBODM) GROUP BY OWNER;SELECT OWNER,COUNT(*)  FROM DBA_OBJECTS@t_s_link WHERE OWNER NOT IN ( ) GROUP BY OWNER;

 

 

 

4.4用户对象类型

set pages 12222 lines 132select c.OWNER,c.object_type,c.cntlinux,d.cntaix,c.cntlinux-d.cntaix diff  (select a.OWNER,a.object_type,count(*) cntlinux from dba_objects a where owner in (DBSFWUSERDVFRIDPFEXCREDITETLTSTFDMUPRRJGDBACTIONUSERDZDABKMONITORWKXTOAUSERSENSORSPUBliColAPSYSSDMORACLE_OCMAUDSYSDVSYSBP_queryGSMadmin_INTERNALOJVMSYSUSEGDMAPPQOSSYSORDDATABAKSMARTBIEXPDPUSERREMOTE_SCHEDulER_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/1222 lines null,1)">0,cntaix)) diff where owner =ODSSMIS  group by a.OWNER,a.OBJECT_TYPE,a.STATUS) c,b.STATUS,1)">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单一用户对象对比

 (where a.owner =from dba_objects b where b.owner =  group by b.OWNER,b.OBJECT_TYPE) dwhere c.owner=d.owner and c.object_type=d.object_type order by c.owner,1)">from dba_objects@t_s_link a 
 
 
 
 
4.6单一用户表分区对比
col object_type for a20col object_name  a25select object_name,object_type,CREATED,TIMESTAMP,TEMPORARY from dba_objects ' and object_type=table PARTITION;col object_type from dba_objects@t_s_link ';
 
 
4.7单一用户表分区数量对比
select count(0) '  and object_type=;';
 
 
4.8表分区详细对比
col object_name  a35select c.object_name,1)">(0) cntaix,object_name  group by object_name) c,1)">0) cntlinux,1)"> group by object_name) dwhere  c.object_name=d.object_name(+)   order by object_name;col object_name where  c.object_name=d.object_name(+)   order by object_name;
 
 
4.9无效对象
INVALIDfrom  (select o.OWNER,count(*) linuxcnt   dba_objects owhere o.status=' and o.OWNER  )group by o.OWNER order by o.OWNER ) c,count(*) aixcnt  )group by o.OWNER order by o.OWNER ) dwhere c.owner=d.owner(+)/PICCSMISODSUSERPICCSMISREADVSMISDBAMONITORR7FMIS_SMISFMIS_WRITEBACKSMIS2MPECIF_SMISSUGGEST_SMISSMISCRMXXX dba_objects@t_s_link o)/
 
 
4.10有效对象
VALIDwhere c.owner=d.owner/d.owner/
 
 
 
 
 
4.11diff vIEw
 
132col OBJECT_TYPE  a15col object_name  a33select * VIEWminus) order by object_type,object_name/from dba_objects@t_s_link b select index_name,table_name,owner from dba_indexes where table_owner DEVELOPDBUSERHDCQRYFINGERDBqueryMCTC);from dba_indexes@t_s_link ');
 
4.13用户对象权限，被赋予public用户的表
from dba_tab_privs ') and grantee=from dba_tab_privs@t_s_link ';
 
4.14用户权限
col owner format a10col privilege format a10from dba_sys_privs where grantee ) order by grantee;from dba_role_privs ) order by grantee;col owner format a10col privilege format a10from dba_sys_privs@t_s_link from dba_role_privs@t_s_link ') order by grantee;
 
4.15段类型统计
select segment_type,count(*) from dba_segments where owner= group by segment_type;from dba_constraints from dba_segments@t_s_link from dba_constraints@t_s_link ';
 
4.16约束统计
select constraint_type,1)"> group by CONSTRAINT_TYPE;select STATUS,COUNT(*)  group by status;' group by status;
 
5.db_link
 
col created format a10col db_link format a25 dba_db_links order by db_link;col created format a10col db_link format a25from dba_db_links@t_s_link order by db_link;
 
 
6.public的同义词
 
set linesize 1000col SYNONYM_name format a40col table_name format a40select *   dba_synonyms where owner =    and table_OWNER );       dba_synonyms@t_s_link ');
 
 
7.自动任务统计
 
 
select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,FAILURES,broKEN,WHAT  dba_jobs;from dba_jobs@t_s_link;
 
          总结       
以上是内存溢出为你收集整理的oracle数据对比--用户,索引,分区,dblink,同义词,视图全部内容，希望文章能够帮你解决oracle数据对比--用户,索引,分区,dblink,同义词,视图所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/sjk/1153707.html