Oracle 收集业务用户以及迁移后的数据校验

说明:
      大多数迁移时，我们需要收集业务用户信息,并在迁移后,对这些业务用户进行数据一致性校验,但是在Oracle中存在许多的系统用户,一般我们通过dba_users视图去查询用户可以查询出数据库所有的用户,但是我们无法用肉眼区分哪些是系统用户以及哪些是非系统用户.

      我们可以通过dba_users的create time以及default tablespace字段来区分是否为系统用户.但是这种方式也不是完全准确.对于creaet time字段出现过业务用户查询create time比系统用户时间更早的情况.对于defalut tablespace字段,也存在系统用户的默认表空间为users表空间.

      在Oracle中，对于数据库中的系统用户,我们可以通过SYS.REGISTRY $以及SYS.REGISTRY $SCHEMAS去查询每个组件对应的系统用户.在通过dba_users视图将查询出来的系统用户全部过滤掉.通过这种方式定位业务用户是最为准确的.

查看业务用户以及迁移后的数据校验语句如下：
1.查看系统中每个组件对应的系统用户

SELECT CID, CNAME, NAME
FROM
(SELECT CID, CNAME, SCHEMA# FROM SYS.REGISTRY$
UNION ALL
SELECT A.CID, CNAME, B.SCHEMA# FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B
WHERE A.CID = B.CID) A,
SYS.USER$ B
WHERE A.SCHEMA# = B.USER#;

CID        CNAME                               NAME
---------- ----------------------------------- ------------------------------
XOQ        Oracle OLAP API                     SYS
APS        OLAP Analytic Workspace             SYS
CATJAVA    Oracle Database Java Packages       SYS
XML        Oracle XDK                          SYS
JAVAVM     JServer JAVA Virtual Machine        SYS
CATPROC    Oracle Database Packages and Types  SYS
CATALOG    Oracle Database Catalog Views       SYS
CATPROC    Oracle Database Packages and Types  SYSTEM
CATPROC    Oracle Database Packages and Types  OUTLN
CATPROC    Oracle Database Packages and Types  DIP
CATPROC    Oracle Database Packages and Types  ORACLE_OCM

CID        CNAME                               NAME
---------- ----------------------------------- ------------------------------
CATPROC    Oracle Database Packages and Types  DBSNMP
CATPROC    Oracle Database Packages and Types  APPQOSSYS
OWM        Oracle Workspace Manager            WMSYS
XDB        Oracle XML Database                 XS$NULL
RUL        Oracle Rules Manager                EXFSYS
EXF        Oracle Expression Filter            EXFSYS
CONTEXT    Oracle Text                         CTXSYS
XDB        Oracle XML Database                 XDB
XDB        Oracle XML Database                 ANONYMOUS
ORDIM      Oracle Multimedia                   ORDSYS
ORDIM      Oracle Multimedia                   ORDDATA

CID        CNAME                               NAME
---------- ----------------------------------- ------------------------------
ORDIM      Oracle Multimedia                   ORDPLUGINS
ORDIM      Oracle Multimedia                   SI_INFORMTN_SCHEMA
ORDIM      Oracle Multimedia                   MDSYS
SDO        Spatial                             MDSYS
AMD        OLAP Catalog                        OLAPSYS
EM         Oracle Enterprise Manager           SYSMAN
APEX       Oracle Application Express          FLOWS_FILES
APEX       Oracle Application Express          APEX_030200
OWB        OWB                                 OWBSYS

2.获取非系统用户:

SELECT USERNAME, to_char(CREATED,'yyyy-mm-dd'),DEFAULT_TABLESPACE
FROM DBA_USERS
WHERE USERNAME NOT IN
(SELECT NAME
FROM
(SELECT SCHEMA#
FROM SYS.REGISTRY$
UNION ALL
SELECT B.SCHEMA#
FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B
WHERE A.CID = B.CID) A,
SYS.USER$ B
WHERE A.SCHEMA# = B.USER#)
ORDER BY 3, 2;

USERNAME                       TO_CHAR(CR DEFAULT_TABLESPACE
------------------------------ ---------- ------------------------------
BATJ                           2020-04-23 BATJ
BAJK                           2020-04-23 BATJ
LIS                            2020-04-23 LIS
OWBSYS_AUDIT                   2013-08-24 SYSAUX
MGMT_VIEW                      2013-08-24 SYSTEM
EMR                            2020-04-23 TS_EMR
EMR3                           2020-04-23 TS_EMR
EMRJK                          2020-04-23 TS_EMR_JK
WEIXIN                         2020-04-23 TS_JYK
JYK                            2020-04-23 TS_JYK
EHOSPITAL                      2020-04-23 TS_YYGL

USERNAME                       TO_CHAR(CR DEFAULT_TABLESPACE
------------------------------ ---------- ------------------------------
HISBAK                         2020-04-23 TS_ZHUYUAN_BAK
APEX_PUBLIC_USER               2013-08-24 USERS
SPATIAL_WFS_ADMIN_USR          2013-08-24 USERS
SCOTT                          2013-08-24 USERS
MDDATA                         2013-08-24 USERS
SPATIAL_CSW_ADMIN_USR          2013-08-24 USERS
MCBAK                          2020-04-23 USERS
MCBAK1                         2020-04-23 USERS
ONEKEEPER                      2020-04-23 USERS
JCST                           2020-04-23 USERS
YUNSF                          2020-04-23 USERS

USERNAME                       TO_CHAR(CR DEFAULT_TABLESPACE
------------------------------ ---------- ------------------------------
SIIMHIS                        2020-04-23 ZJHIS
MZJH                           2020-04-23 ZJHIS
SIIM                           2020-04-23 ZJHIS
PDJH                           2020-04-23 ZJHIS
ZJHIS                          2020-04-23 ZJHIS

//可以认为这些用户为业务用户
//将这些业务用户复制出来进行列转行，加入到如下SQL语句中

3.迁移后数据校验方法一

(每个业务用户下的总对象数量校验)
SELECT D.OWNER,COUNT(1)
  FROM dba_objects d
 WHERE d.OWNER   in ('BATJ','BAJK','LIS','OWBSYS_AUDIT','MGMT_VIEW','EMR','EMR3','EMRJK','WEIXIN','JYK','EHOSPITAL','HISBAK','APEX_PUBLIC_USER','SPATIAL_WFS_ADMIN_USR','SCOTT','MDDATA','SPATIAL_CSW_ADMIN_USR','MCBAK','MCBAK1','ONEKEEPER','JCST','YUNSF','SIIMHIS','MZJH','SIIM','PDJH','ZJHIS')
 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner) 
 GROUP BY D.OWNER
 ORDER BY D.OWNER ;
 
 OWNER                            COUNT(1)
------------------------------ ----------
BAJK                                   74
BATJ                                  545
EHOSPITAL                            1090
EMR                                   792
EMR3                                 1300
EMRJK                                  27
HISBAK                                 94
JCST                                   14
JYK                                   946
LIS                                    11
MZJH                                  289
OWBSYS_AUDIT                           12
PDJH                                  298
SCOTT                                  20
SIIM                                  330
SIIMHIS                                21
WEIXIN                                 90
YUNSF                                  28
ZJHIS                                3316 

4.迁移后数据校验方法二

(每个业务用户下的各个对象类别的数量校验)
select OWNER,OBJECT_TYPE,count(*) from dba_objects where owner in ('BATJ','BAJK','LIS','OWBSYS_AUDIT','MGMT_VIEW','EMR','EMR3','EMRJK','WEIXIN','JYK','EHOSPITAL','HISBAK','APEX_PUBLIC_USER','SPATIAL_WFS_ADMIN_USR','SCOTT','MDDATA','SPATIAL_CSW_ADMIN_USR','MCBAK','MCBAK1','ONEKEEPER','JCST','YUNSF','SIIMHIS','MZJH','SIIM','PDJH','ZJHIS') 
group by OWNER,OBJECT_TYPE order by owner;
 
 
 OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
BAJK                           INDEX                       27
BAJK                           SYNONYM                     12
BAJK                           TABLE                       30
BAJK                           TRIGGER                      3
BAJK                           VIEW                         2
BATJ                           DATABASE LINK                1
BATJ                           FUNCTION                    20
BATJ                           INDEX                      238
BATJ                           SYNONYM                     19
BATJ                           TABLE                      219
BATJ                           VIEW                        48

OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
EHOSPITAL                      DATABASE LINK                2
EHOSPITAL                      FUNCTION                    22
EHOSPITAL                      INDEX                      473
EHOSPITAL                      PACKAGE                     10
EHOSPITAL                      PACKAGE BODY                 8
EHOSPITAL                      PROCEDURE                   18
EHOSPITAL                      SEQUENCE                    15
EHOSPITAL                      TABLE                      490
EHOSPITAL                      VIEW                        52
......
......

5.无效对象校验:

迁移目标端执行编译无效对象
@?/rdbms/admin/utlrp.sql

使用在源端迁移导出前创建object_bak做比较(create table object_20201127 as select * from dba_objects )

select a.owner,a.object_name,a.OBJECT_TYPE from dba_objects a,object_20201127 b where a.OBJECT_NAME=b.OBJECT_NAME and a.STATUS='INVALID' and b.STATUS='VALID'

参考：http://blog.itpub.net/4227/viewspace-708250/