迁移结果统计

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

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

2. 统计数据库对象及个数。

   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;
       /

3. 统计表数据量。

   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;
       /

4. 统计约束及定义（约束类型,'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'

5. 统计索引及定义（约束类型,'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'