DSG ORACLE复杂软件比对步骤1.doc

DSG软件比对步骤
安装步骤
均在目标端操作
dsg用户需要的权限
---------------all privileges
-- 1 Role for DSG
GRANT DBA TO DSG;
ALTER USER DSG DEFAULT ROLE ALL;
-- 4 System Privileges for DSG
GRANT EXECUTE ANY PROCEDURE TO DSG;
GRANT SELECT ANY DICTIONARY TO DSG;
GRANT SELECT ANY SEQUENCE TO DSG;
GRANT SELECT ANY TABLE TO DSG;
创建DBLINK,连接生产库
----------------for create dblink
DROP DATABASE LINK DBVERIFY;
CREATE DATABASE LINK DBVERIFY
CONNECT TO DSG
IDENTIFIED BY <PWD>
USING 'tnsnames';
创建比对使用的临时表
-----------------for create check table;
CREATE TABLE ---不支持的表
(
OWNER VARCHAR2(10 BYTE),
TABLE_NAME VARCHAR2(50 BYTE),
NO_SUP_TYPE VARCHAR2(20 BYTE)
) ;
-- Create table
create table DSG_CHECK_TABLE ------当前比对的表
(
DS_OWNER VARCHAR2(50) not null,
DT_OWNER VARCHAR2(50),
DS_NAME VARCHAR2(50) not null,
DT_NAME VARCHAR2(50),
TABLE_PARTITION VARCHAR2(50) not null,
SYNONYM_NO NUMBER default 0,
CHECK_TYPE VARCHAR2(20),
DS_COUNT NUMBER,
DT_COUNT NUMBER,
MINUS_COUNT NUMBER,
SEQ NUMBER,
STATUS NUMBER,
START_DIFF DATE,
MINUS_DIFF DATE,
DS_COUNT_DIFF DATE,
DT_COUNT_DIFF DATE,
ERR_MSG VARCHAR2(500),
CHECK_SQL VARCHAR2(4000)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table DSG_CHECK_TABLE
add constraint PK_DSG_CHECK_TABLE primary key (DS_OWNER, DS_NAME, TABLE_PARTITION);
-- Create table for get al