迁移结果统计

查看数据库及数据库的字符集。

SQL> SELECT DB_NAME,CHAR_SET,TIME_ZONE,ONLINE FROM DBA_DATABASES WHERE DB_NAME='{数据库名称}';

统计数据库对象及个数。

SQL>DECLARE
    CURSOR CUR IS 	  
      SELECT SCH.SCHEMA_NAME OWNER,'TABLE' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_TABLES TAB LEFT JOIN DBA_SCHEMAS SCH ON TAB.SCHEMA_ID = SCH.SCHEMA_ID GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT SCH.SCHEMA_NAME OWNER,'VIEW' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_VIEWS VW LEFT JOIN DBA_SCHEMAS SCH ON VW.SCHEMA_ID = SCH.SCHEMA_ID GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT SCH.SCHEMA_NAME OWNER,'SEQUENCES' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_SEQUENCES SEQ LEFT JOIN DBA_SCHEMAS SCH ON SEQ.SCHEMA_ID = SCH.SCHEMA_ID GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT SCH.SCHEMA_NAME OWNER,'PACKAGE HEAD' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_PACKAGES PAK LEFT JOIN DBA_SCHEMAS SCH ON PAK.SCHEMA_ID = SCH.SCHEMA_ID WHERE SPEC IS NOT NULL GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT SCH.SCHEMA_NAME OWNER,'PACKAGE BODY' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_PACKAGES PAK LEFT JOIN DBA_SCHEMAS SCH ON PAK.SCHEMA_ID = SCH.SCHEMA_ID WHERE BODY IS NOT NULL GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT SCH.SCHEMA_NAME OWNER,'PROCEDURE' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_PROCEDURES PROC LEFT JOIN DBA_SCHEMAS SCH ON PROC.SCHEMA_ID = SCH.SCHEMA_ID WHERE PROC.RET_TYPE IS NULL GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT SCH.SCHEMA_NAME OWNER,'FUNCTION' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_PROCEDURES FUNC LEFT JOIN DBA_SCHEMAS SCH ON FUNC.SCHEMA_ID = SCH.SCHEMA_ID WHERE FUNC.RET_TYPE IS NOT NULL GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT SCH.SCHEMA_NAME OWNER,'TRIGGER' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_TRIGGERS TRIG LEFT JOIN DBA_SCHEMAS SCH ON TRIG.SCHEMA_ID = SCH.SCHEMA_ID GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT SCH.SCHEMA_NAME OWNER,'SYNONYM' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_SYNONYMS SYN LEFT JOIN DBA_SCHEMAS SCH ON SYN.SCHEMA_ID = SCH.SCHEMA_ID GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT SCH.SCHEMA_NAME OWNER,'UDT TYPE' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_TYPES TYP LEFT JOIN DBA_SCHEMAS SCH ON TYP.SCHEMA_ID = SCH.SCHEMA_ID GROUP BY SCH.SCHEMA_NAME
      UNION ALL 
      SELECT DB.DB_NAME OWNER,'JOB' AS OBJ_TYPE,COUNT(*) CNT FROM DBA_JOBS JOB LEFT JOIN DBA_DATABASES DB ON JOB.DB_ID = DB.DB_ID GROUP BY DB.DB_NAME;
    VAR_COUNT NUMBER(10);
    VAR_STR VARCHAR(500);
    BEGIN
      FOR VAR_ROW IN CUR LOOP
        SEND_MSG( VAR_ROW.OWNER||'.'||VAR_ROW.OBJ_TYPE || '=' || VAR_ROW.CNT);
      END LOOP;
    END;
    /

统计表数据量。

SQL> DECLARE
       CURSOR CUR IS SELECT	S.SCHEMA_NAME || '.' || T.TABLE_NAME AS TABLE_NAME FROM DBA_TABLES T 
       			         LEFT JOIN DBA_SCHEMAS S ON S.SCHEMA_ID = T.SCHEMA_ID ORDER BY	S.SCHEMA_NAME,T.TABLE_NAME;
       VAR_COUNT NUMBER(10);
       VAR_STR VARCHAR(500);
     BEGIN
     FOR VAR_ROW IN CUR LOOP
       VAR_STR := 'SELECT COUNT(*) FROM ' || VAR_ROW.TABLE_NAME;

       EXECUTE IMMEDIATE VAR_STR INTO VAR_COUNT;
       SEND_MSG( VAR_ROW.TABLE_NAME || '=' || TO_CHAR(VAR_COUNT));
     END LOOP;
     END;
    /

统计约束及定义（约束类型,'F': 外键;'R': 引用外键;'C': 值检查;'D': 默认值;'U': 唯一值;'P': 主键;）。

SELECT
        DB.DB_NAME AS 数据库名,
        CH.SCHEMA_NAME AS 模式名,
        T.TABLE_NAME AS 表名,
        C.CONS_NAME AS 约束名,
        C.CONS_TYPE AS 约束类型,
        REPLACE(C.DEFINE,  '"',  '') AS 约束定义
      FROM
        DBA_CONSTRAINTS C
      LEFT JOIN DBA_TABLES T ON
        C.TABLE_ID = T.TABLE_ID
      LEFT JOIN DBA_DATABASES DB ON
        C.DB_ID = DB.DB_ID
      LEFT JOIN DBA_SCHEMAS CH ON
        CH.USER_ID = T.USER_ID AND CH.DB_ID=DB.DB_ID
      WHERE DB.DB_NAME='DBNAME' AND CH.SCHEMA_NAME='USER'

统计索引及定义（约束类型,'BTREE': B树索引;'RTREE': R树索引;'FULL': 全文索引;'BITMAP': 位图索引;'UNION': 联合索引;）。

SQL> SELECT
        DB.DB_NAME AS 数据库名,
        CH.SCHEMA_NAME AS 模式名,
        T.TABLE_NAME AS 表名,
        INDEX_NAME AS 索引名,
        (CASE INDEX_TYPE
              WHEN 0 THEN 'BTREE'
          WHEN 1 THEN 'RTREE'
          WHEN 2 THEN 'FULLTEXT'
          WHEN 3 THEN 'BITMAP'
          WHEN 4 THEN 'UNION'
        END) AS 索引类型,
        IS_PRIMARY AS 是否是主键索引,
        IS_UNIQUE AS 是否是唯一索引,
        FIELD_NUM AS 索引字段数,
        REPLACE(KEYS,  '"',  '') AS 索引字段
      FROM
        DBA_INDEXES AS C
      LEFT JOIN DBA_TABLES T ON
        C.TABLE_ID = T.TABLE_ID
      LEFT JOIN DBA_DATABASES DB ON
        C.DB_ID = DB.DB_ID
      LEFT JOIN DBA_SCHEMAS CH ON
        CH.USER_ID = T.USER_ID AND CH.DB_ID = DB.DB_ID
      WHERE
        DB.DB_NAME = 'DBNAME' AND CH.SCHEMA_NAME = 'USER'

迁移结果统计 ​

迁移结果统计