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;

使用者

源庫臨時表

目標庫臨時表

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;