1 服务器迁移后数据量对（表、视图、索引、存储过程、函数、包体)比较
-- (1) 当前用户表总数量新旧差异
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'TABLE' PROJECT_NAME FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE='TABLE' AND T.OWNER = 'xxx'
MINUS
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'TABLE' PROJECT_NAME FROM DBA_OBJECTS@DB_LINK T WHERE T.OBJECT_TYPE='TABLE' AND T.OWNER = 'xxx'
-- (2) 当前视图总数量新旧差异
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'VIEW' PROJECT_NAME FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE='VIEW' AND T.OWNER = 'xxx'
MINUS
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'VIEW' PROJECT_NAME FROM DBA_OBJECTS@DB_LINK T WHERE T.OBJECT_TYPE='VIEW' AND T.OWNER = 'xxx'
-- (3) 当前存储过程总数量新旧差异
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'PROCEDURE' PROJECT_NAME FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE='PROCEDURE' AND T.OWNER = 'xxx'
MINUS
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'PROCEDURE' PROJECT_NAME FROM DBA_OBJECTS@DB_LINK T WHERE T.OBJECT_TYPE='PROCEDURE' AND T.OWNER = 'xxx'
-- (4) 当前包体总数量新旧差异
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'PACKAGE BODY' PROJECT_NAME FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE='PACKAGE BODY' AND T.OWNER = 'xxx'
MINUS
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'PACKAGE BODY' PROJECT_NAME FROM DBA_OBJECTS@DB_LINK T WHERE T.OBJECT_TYPE='PACKAGE BODY' AND T.OWNER = 'xxx'
-- (5) 当前索引总数量新旧差异
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'INDEX' PROJECT_NAME FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE='INDEX' AND T.OWNER = 'xxx'
MINUS
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'INDEX' PROJECT_NAME FROM DBA_OBJECTS@DB_LINK T WHERE T.OBJECT_TYPE='INDEX' AND T.OWNER = 'xxx'
-- (6) 当前函数总数量新旧差异
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'FUNCTION' PROJECT_NAME FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE='FUNCTION' AND T.OWNER = 'xxx'
MINUS
SELECT 'xxx' NAME, COUNT(1) C_COUNT, 'FUNCTION' PROJECT_NAME FROM DBA_OBJECTS@DB_LINK T WHERE T.OBJECT_TYPE='FUNCTION' AND T.OWNER = 'xxx'
2 查询程序体新旧服务器差异
-- 获取当前用户下包体数据量差异
-- SELECT * FROM ALL_SOURCE where TYPE='PACKAGE BODY' AND OWNER='xxx' and name='xxx';
-- (1) 创建辅助表
-- 辅助表1
/*-- Create table
create table T_MINUS_ERROR_INFO
(
user_name VARCHAR2(100),
type_name VARCHAR2(100),
name1 VARCHAR2(100)
)
-- Add comments to the columns
comment on column T_MINUS_ERROR_INFO.user_name
is '用户';
comment on column T_MINUS_ERROR_INFO.type_name
is '类型：PROCEDURE 、FUNCTION 、PACKAGE BODY';
comment on column T_MINUS_ERROR_INFO.name1
is '名称：PROCEDURE-xxx、FUNCTION-xxx 、PACKAGE BODY-xxx';*/
-- 辅助表2
/*create table t_minus_tmp(
text varchar2(4000)
)*/
-- (2)对单个用户下的PROCEDURE 、FUNCTION 、PACKAGE BODY 、PACKAGE 内容进行比较
-- 参数说明:
-- V_USER := 'xxx' -- 用户
-- V_DBLINK := 'DB_LINK' -- DBLINK
DECLARE
V_USER VARCHAR2(20); -- 定义用户
V_SQL_STR VARCHAR2(4000);
CURSOR C_TYPE IS SELECT DISTINCT TYPE FROM ALL_SOURCE;
V_TYPE ALL_SOURCE.TYPE%TYPE; -- 定义类型
V_COUNT VARCHAR2(10);
V_DBLINK VARCHAR2(10);
BEGIN
V_USER := 'xxx';
V_DBLINK := 'DB_LINK'
-- 获取大类：PROCEDURE 、FUNCTION 、PACKAGE BODY 、PACKAGE
EXECUTE IMMEDIATE 'TRUNCATE TABLE T_MINUS_ERROR_INFO';-- 清除辅助表T_MINUS_TMP
OPEN C_TYPE;
LOOP
FETCH C_TYPE INTO V_TYPE;
EXIT WHEN C_TYPE%NOTFOUND;
IF V_TYPE = 'PROCEDURE' OR V_TYPE = 'FUNCTION' OR V_TYPE = 'PACKAGE' OR V_TYPE='PACKAGE BODY' THEN
-- SELECT DISTINCT NAME FROM ALL_SOURCE where TYPE='PACKAGE BODY' AND OWNER='xxx' ;
-- SELECT * FROM ALL_SOURCE where TYPE='PACKAGE BODY' AND OWNER='xxx' AND NAME='xxx';
-- 获取包体的名称D
DECLARE
CURSOR C_PACKAGE_BODY_NAME IS SELECT DISTINCT NAME FROM ALL_SOURCE WHERE TYPE=V_TYPE AND OWNER=V_USER;
V_BODY_NAME ALL_SOURCE.NAME%TYPE;
BEGIN
OPEN C_PACKAGE_BODY_NAME;
LOOP
FETCH C_PACKAGE_BODY_NAME INTO V_BODY_NAME;
EXIT WHEN C_PACKAGE_BODY_NAME%NOTFOUND;
EXECUTE IMMEDIATE 'TRUNCATE TABLE T_MINUS_TMP';-- 清除辅助表T_MINUS_TMP
V_SQL_STR := 'INSERT INTO T_MINUS_TMP SELECT TEXT FROM ALL_SOURCE WHERE TYPE='||''''||V_TYPE||''''||' AND OWNER='||''''||V_USER||''''||' AND NAME='||''''||V_BODY_NAME||''''||' MINUS '||
' SELECT TEXT FROM ALL_SOURCE'||'@'||V_DBLINK||' WHERE TYPE='||''''||V_TYPE||''''||' AND OWNER='||''''||V_USER||''''||' AND NAME='||''''||V_BODY_NAME||'''';
DBMS_OUTPUT.PUT_LINE(V_SQL_STR);
EXECUTE IMMEDIATE V_SQL_STR; -- 执行动态sql,并将结果写入结果表
COMMIT;
SELECT COUNT(1) INTO V_COUNT FROM T_MINUS_TMP;
-- 对数据进行统计，判断差异
IF V_COUNT <> 0 THEN
DBMS_OUTPUT.PUT_LINE(V_USER||'.'||V_TYPE||'.'||V_BODY_NAME||' 存在差异');
INSERT INTO T_MINUS_ERROR_INFO(
USER_NAME,
TYPE_NAME,
NAME1
)
SELECT V_USER,V_TYPE,V_BODY_NAME FROM DUAL;
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE(V_USER||'.'||V_TYPE||'.'||V_BODY_NAME||' 不存在差异');
END IF;
END LOOP;
CLOSE C_PACKAGE_BODY_NAME;
END;
END IF;
END LOOP;
CLOSE C_TYPE;
END;
-- (3) 查询错误记录，信息包含了 用户，类型，类型体的名字
select * from T_MINUS_ERROR_INFO
3 查询用户下各表中新旧服务器总数据差异
-- 按顺序使用
-- (1) 创建辅助表
-- Create table
/*create table T_TMP_CY
(
table_name VARCHAR2(100),
c_count NUMBER
)
*/
--（2）获取该用户下各表，新旧服务器中数据量差异
DECLARE
V_SQL_STR VARCHAR2(5000);
V_USER_NAME VARCHAR2(100);
V_DBLINK VARCHAR2(100);
V_COUNT VARCHAR2(20);
BEGIN
V_USER_NAME := 'xxx';--确定用户名
V_DBLINK := 'DB_LINK';--确定DB＿LINK
DECLARE
CURSOR C_CURSOR IS SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER=V_USER_NAME;
V_TABLE_NAME DBA_TABLES.TABLE_NAME%TYPE;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE T_TMP_CY';
OPEN C_CURSOR;
LOOP
FETCH C_CURSOR INTO V_TABLE_NAME;
EXIT WHEN C_CURSOR%NOTFOUND;
V_SQL_STR := 'INSERT INTO T_TMP_CY'||' SELECT '||''''||V_USER_NAME||'.'||V_TABLE_NAME||''''||' TABLE_NAME, COUNT(1) C_COUNT FROM '||V_USER_NAME||'.'||V_TABLE_NAME||' MINUS '||
' SELECT '||''''||V_USER_NAME||'.'||V_TABLE_NAME||''''||' TABLE_NAME, COUNT(1) C_COUNT FROM '||V_USER_NAME||'.'||V_TABLE_NAME||'@'||V_DBLINK;
-- DBMS_OUTPUT.PUT_LINE(V_SQL_STR);
EXECUTE IMMEDIATE V_SQL_STR;
COMMIT;
SELECT C_COUNT INTO V_COUNT FROM T_TMP_CY WHERE TABLE_NAME=V_USER_NAME||'.'||V_TABLE_NAME;
IF V_COUNT<>0 THEN
DBMS_OUTPUT.PUT_LINE(V_USER_NAME||'.'||V_TABLE_NAME||'的差异量：'||V_COUNT||' 需要修正');
END IF;
END LOOP;
CLOSE C_CURSOR;
END;
END;
-- （2）获取差异数据
SELECT * FROM T_TMP_CY;
