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;

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

1.创建dblink

1.1 旧库修改tnsname到新库，在新库上添加旧库的tns配置

1.2 旧库创建有访问权限的用户

1.3 新库创建dblink到旧库

2.实例查询

3.数据量大小对比

4.用户对比

4.1创建用户对比

4.2用户下对象总数

4.4用户对象类型

4.5单一用户对象对比

4.6单一用户表分区对比

4.7单一用户表分区数量对比

4.8表分区详细对比

4.9无效对象

4.10有效对象

4.11diff view

4.12用户索引

4.13用户对象权限，被赋予public用户的表

4.14用户权限

4.15段类型统计

4.16约束统计

5.db_link

6.public的同义词

7.自动任务统计

oracle数据对比--用户,索引,分区,dblink,同义词,视图的相关教程结束。

相关推荐

扩容C盘后盘符不见的数据如何找到

Django4全栈进阶之路11 view视图

[转]数据库物化视图刷新SQL命令和查询被delete掉的数据

mysql-高级功能(触发器、存储过程、视图、事务)

磁盘管理|df、du|分区 fdisk |格式化

微信小程序-视图容器组件

微信小程序-视图数据绑定

微信小程序-视图事件