ORACLE数据校验文档

源端表空间

源端数据文件

set   linesize 1000 col   file_name for a50 SELECT   d.FILE_ID,        d.TABLESPACE_NAME,        (SELECT (SUM(nb.BYTES/1024/1024))           FROM dba_data_files nb          WHERE nb.TABLESPACE_NAME =   d.TABLESPACE_NAME) ts_size_m,        d.FILE_NAME,        (d.BYTES/1024/1024) file_size_m,        (d.USER_BYTES/1024/1024)   file_use_size_m   FROM dba_data_files d WHERE   d.TABLESPACE_NAME  not in   ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2')  ORDER BY    file_id;

用户

创建时间

alter session set nls_date_format='yyyy-mm-dd   hh24:mi:ss';

set line 200

SELECT d.username,

         d.default_tablespace,

         d.temporary_tablespace,

         d.account_status,

       d.created

  from dba_users   d

 where   d.account_status = 'OPEN' and d.username not like '%SYS%'     order by d.CREATED desc;

表空间

源端数据文件

目标端数据文件

set linesize 1000

col file_name for   a50

SELECT d.FILE_ID,

       d.TABLESPACE_NAME,

       (SELECT (SUM(nb.BYTES/1024/1024))

          FROM dba_data_files nb

         WHERE nb.TABLESPACE_NAME =   d.TABLESPACE_NAME) ts_size_m,

       d.FILE_NAME,

       (d.BYTES/1024/1024) file_size_m,

       (d.USER_BYTES/1024/1024)   file_use_size_m

  FROM dba_data_files d

WHERE   d.TABLESPACE_NAME  not in   ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2')  ORDER BY  file_id;

用户

源端

目标端

SELECT D.OWNER,COUNT(*)

FROM dba_objects d

WHERE d.OWNER   in   ('WEB','QLYG','WLSJ','WLWX_YD','WLJX','WLSBZD','WEBUSER','JBGC','WLWX_LOG','LBHIS','WLSHK','WEBCXYB','WLWX','WLJYJK','MOVE','QMCB_NEW','WLWXSY','LB01','WL12333','COMMVAULT','QMCB','LB01_TP','WLSMKJK','WL_JB','QSSBCX','XZPT_TP','JHLIB','XZPT','LBSIJG','LBSIINT','ODC','ZJJBUNI2','ZJJBREP','ZJJBUNI2REP','ZJJBUNI','WLREP','JBSJ')

and d.OWNER not in ('PUBLIC')

AND NOT EXISTS (SELECT * FROM   DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)

GROUP BY D.OWNER

ORDER BY D.OWNER;

用户

对象类型

源端

目标端

SELECT D.OWNER, D.OBJECT_TYPE,   COUNT(1)

FROM dba_objects d

WHERE d.OWNER in   ('WEB','QLYG','WLSJ','WLWX_YD','WLJX','WLSBZD','WEBUSER','JBGC','WLWX_LOG','LBHIS','WLSHK','WEBCXYB','WLWX','WLJYJK','MOVE','QMCB_NEW','WLWXSY','LB01','WL12333','COMMVAULT','QMCB','LB01_TP','WLSMKJK','WL_JB','QSSBCX','XZPT_TP','JHLIB','XZPT','LBSIJG','LBSIINT','ODC','ZJJBUNI2','ZJJBREP','ZJJBUNI2REP','ZJJBUNI','WLREP','JBSJ')

and d.OWNER not in ('PUBLIC')

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, D.OBJECT_TYPE

ORDER BY D.OWNER;

create table check_tab(schema   varchar2(30),tab_name varchar2(30),tab_count int);

CREATE OR REPLACE PROCEDURE   TAB_CHECK

AS

    cursor tab_cur is

    SELECT OWNER,TABLE_NAME FROM DBA_TABLES   WHERE OWNER IN ('WEB','QLYG','WLSJ','WLWX_YD','WLJX','WLSBZD','WEBUSER','JBGC','WLWX_LOG','LBHIS','WLSHK','WEBCXYB','WLWX','WLJYJK','MOVE','QMCB_NEW','WLWXSY','LB01','WL12333','COMMVAULT','QMCB','LB01_TP','WLSMKJK','WL_JB','QSSBCX','XZPT_TP','JHLIB','XZPT','LBSIJG','LBSIINT','ODC','ZJJBUNI2','ZJJBREP','ZJJBUNI2REP','ZJJBUNI','WLREP','JBSJ');

    sqlddl varchar2(1000);

begin

    for tab_recoder in tab_cur loop

    sqlddl:='insert into check_tab select   '||''''||tab_recoder.owner||''''||','||''''||tab_recoder.table_name||''''||',count(1)   from '||tab_recoder.owner||'.'||tab_recoder.table_name;

    dbms_output.put_line(sqlddl);

    EXECUTE IMMEDIATE sqlddl;

    end loop;

end;

/

exec sys.TAB_CHECK;

用户

源库临时表

目标库临时表

select table_name,owner,TEMPORARY from   dba_tables where owner in   ('WEB','QLYG','WLSJ','WLWX_YD','WLJX','WLSBZD','WEBUSER','JBGC','WLWX_LOG','LBHIS','WLSHK','WEBCXYB','WLWX','WLJYJK','MOVE','QMCB_NEW','WLWXSY','LB01','WL12333','COMMVAULT','QMCB','LB01_TP','WLSMKJK','WL_JB','QSSBCX','XZPT_TP','JHLIB','XZPT','LBSIJG','LBSIINT','ODC','ZJJBUNI2','ZJJBREP','ZJJBUNI2REP','ZJJBUNI','WLREP','JBSJ')  and TEMPORARY='Y';

set   heading off; set echo off; Set pages 999; set long 90000; spool get_tmptable_ddl.sql select   dbms_metadata.get_ddl('TABLE','BST_USER_PARAM','LB01') FROM dual ;

drop table t_tmp_user_lhr;                                                                                           create table t_tmp_user_lhr( id number,   username varchar2(50), exec_sql varchar2(4000),create_type varchar2(20)); DROP sequence s_t_tmp_user_lhr;                                                                                   create sequence s_t_tmp_user_lhr;                                                                                                                                                                                                        begin                                                                                                                                                                                                                                    for   cur in (SELECT d.username,                                                                                                             d.default_tablespace,                                                                                                d.account_status,                                                                                                      'create user ' ||   d.username || ' identified by ' ||                                                               d.username || ' default   tablespace ' ||                                                                              d.default_tablespace ||   '  TEMPORARY TABLESPACE  ' ||                                                              D.temporary_tablespace   || ';' CREATE_USER,                                                                             replace(to_char(DBMS_METADATA.GET_DDL('USER',                                                                                                                D.username)),                                                                        chr(10),                                                                                                            '')   create_USER1                                                                                        FROM dba_users d                                                                                       WHERE d.username   in    ('WEB','QLYG','WLSJ','WLWX_YD','WLJX','WLSBZD','WEBUSER','JBGC','WLWX_LOG','LBHIS','WLSHK','WEBCXYB','WLWX','WLJYJK','MOVE','QMCB_NEW','WLWXSY','LB01','WL12333','COMMVAULT','QMCB','LB01_TP','WLSMKJK','WL_JB','QSSBCX','XZPT_TP','JHLIB','XZPT','LBSIJG','LBSIINT','ODC','ZJJBUNI2','ZJJBREP','ZJJBUNI2REP','ZJJBUNI','WLREP','JBSJ'))   loop                                                              INSERT INTO t_tmp_user_lhr                                                                                               (id, username, exec_sql, create_type)                                                                                values                                                                                                                  (s_t_tmp_user_lhr.nextval, cur.username, cur.CREATE_USER,   'USER');                                                                                                                                                                       INSERT INTO t_tmp_user_lhr                                                                                               (id, username, exec_sql, create_type)                                                                                 SELECT s_t_tmp_user_lhr.nextval,                                                                                              cur.username,                                                                                                           CASE                                                                                                                   WHEN D.ADMIN_OPTION = 'YES'   THEN                                                                                      'GRANT ' || d.privilege || ' TO '   || d.GRANTEE ||                                                                    ' WITH GRANT OPTION ;'                                                                                              ELSE                                                                                                                 'GRANT ' || d.privilege || '   TO ' || d.GRANTEE || ';'                                                               END priv,                                                                                                               'DBA_SYS_PRIVS'                                                                                                   FROM dba_sys_privs d                                                                                                   WHERE D.GRANTEE = CUR.USERNAME;                                                                                                                                                                                                           INSERT INTO t_tmp_user_lhr                                                                                               (id, username, exec_sql, create_type)                                                                                  SELECT s_t_tmp_user_lhr.nextval,                                                                                              cur.username,                                                                                                          CASE                                                                                                                  WHEN D.ADMIN_OPTION = 'YES'   THEN                                                                                      'GRANT ' || d.GRANTED_ROLE ||   ' TO ' || d.GRANTEE ||                                                                 ' WITH GRANT OPTION;'                                                                                               ELSE                                                                                                                   'GRANT ' || d.GRANTED_ROLE ||   ' TO ' || d.GRANTEE || ';'                                                          END priv,                                                                                                               'DBA_ROLE_PRIVS'                                                                                                  FROM DBA_ROLE_PRIVS d                                                                                                 WHERE D.GRANTEE = CUR.USERNAME;                                                                                                                                                                                                          INSERT INTO t_tmp_user_lhr                                                                                              (id, username, exec_sql, create_type)                                                                                  SELECT s_t_tmp_user_lhr.nextval,                                                                                             cur.username,                                                                                                          CASE                                                                                                                    WHEN d.grantable = 'YES'   THEN                                                                                         'GRANT ' || d.privilege || '   ON ' || d.owner || '.' ||                                                              d.table_name || ' TO ' ||   d.GRANTEE ||                                                                               '  WITH GRANT OPTION ;'                                                                                             ELSE                                                                                                                  'GRANT ' || d.privilege || '   ON ' || d.owner || '.' ||                                                             d.table_name || ' TO ' ||   d.GRANTEE || ';'                                                                          END priv,                                                                                                              'DBA_TAB_PRIVS'                                                                                                  FROM DBA_TAB_PRIVS d                                                                                                  WHERE D.GRANTEE = CUR.USERNAME;                                                                                  end   loop;                                                                                                              COMMIT;                                                                                                             end;                                                                                                                 /                                                                                                                    SELECT * FROM t_tmp_user_lhr;