如何比较oracle数据库差异
来源：互联网 发布：运维常用python脚本 编辑：程序博客网 时间：2024/05/01 10:33
DB DIFF&SYNC TOOLKIT使用指南
作者：Rainny
日期：
一，本工具的限制
(1) 本工具只比较表(表名，字段名，字段数据类型，数据长度)
(2) 如果只是字段的位置不一样，本工具视为相等
(3) 索引和约束的比较暂不支持
二，本工具使用说明
(1) 开发本工具的目的是为了去除ORACLE自带DB DIFF工具的繁琐和某些限制
(2) 本工具为纯SQL语句构成,不提供UI,使用时按本文档写的步骤一步一步做下去,从做完一个比较到生成数据库同步脚本大约是5~10分钟
(3) 本工具能自动生成数据库同步SQL脚本
三，数据库比较
--左边SCHEMA：XPC90_INSTALL
CREATE TABLE T_SCHEMA_METADATA(
TABLE_NAME VARCHAR2(2000),
COLUMN_NAME VARCHAR2(2000),
DATA_TYPE VARCHAR2(200),
DATA_LENGTH VARCHAR2(200),
Constraint pk_t_schema_metadata primary key(TABLE_NAME,COLUMN_NAME)
);
--插入元数据
INSERT INTO T_SCHEMA_METADATA
select t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,
decode(t.DATA_TYPE,'NUMBER',t.DATA_PRECISION||','||t.DATA_SCALE,
'VARCHAR2',t.CHAR_LENGTH,
'NVARCHAR2',t.CHAR_LENGTH,
'NCHAR',t.CHAR_LENGTH,
'CHAR',t.CHAR_LENGTH,
'NVARCHAR',t.CHAR_LENGTH,
'VARCHAR',t.CHAR_LENGTH,
'BLOB',NULL,
'CLOB',NULL,
'DATE',NULL,
'TIMESTAMP',NULL,
NULL) from user_tab_columns t
Where t.TABLE_NAME not in(select view_name from user_views )
order by t.TABLE_NAME,t.COLUMN_NAME;
--右边SCHEMA：XPC90UPGRADE_18
CREATE TABLE T_SCHEMA_METADATA(
TABLE_NAME VARCHAR2(2000),
COLUMN_NAME VARCHAR2(2000),
DATA_TYPE VARCHAR2(200),
DATA_LENGTH VARCHAR2(200),
Constraint pk_t_schema_metadata primary key(TABLE_NAME,COLUMN_NAME)
);
--插入元数据
INSERT INTO T_SCHEMA_METADATA
select t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,
decode(t.DATA_TYPE,'NUMBER',t.DATA_PRECISION||','||t.DATA_SCALE,
'VARCHAR2',t.CHAR_LENGTH,
'NVARCHAR2',t.CHAR_LENGTH,
'NCHAR',t.CHAR_LENGTH,
'CHAR',t.CHAR_LENGTH,
'NVARCHAR',t.CHAR_LENGTH,
'VARCHAR',t.CHAR_LENGTH,
'BLOB',NULL,
'CLOB',NULL,
'DATE',NULL,
'TIMESTAMP',NULL,
NULL) from user_tab_columns t
Where t.TABLE_NAME not in(select view_name from user_views )
order by t.TABLE_NAME,t.COLUMN_NAME;
--SYS
GRANT DBA TO XPC90_INSTALL;
注：以下操作全部以左边SCHEMA：XPC90_INSTALL连接到ORACLE，并做完所有的步骤
--左边SCHEMA：XPC90_INSTALL
--创建表以存放DB比较结果集
create table t_db_diff(
l_schema_name varchar2(200),
l_TABLE_NAME VARCHAR2(2000),
l_COLUMN_NAME VARCHAR2(2000),
l_DATA_TYPE VARCHAR2(200),
l_DATA_LENGTH VARCHAR2(200),
l_table_exists VARCHAR2(200),
l_column_exists VARCHAR2(200),
r_schema_name varchar2(200),
r_TABLE_NAME VARCHAR2(2000),
r_COLUMN_NAME VARCHAR2(2000),
r_DATA_TYPE VARCHAR2(200),
r_DATA_LENGTH VARCHAR2(200),
r_table_exists VARCHAR2(200),
r_column_exists VARCHAR2(200)
);
/
--创建右边方案元数据表
create table T_SCHEMA_METADATA_RIGHT AS
SELECT * FROM XPC90UPGRADE_18.T_SCHEMA_METADATA T
ORDER BY T.TABLE_NAME,T.COLUMN_NAME;
alter table t_schema_metadata_right
add constraint pk_t_schema_metadata_right primary key(table_name,column_name);
--清空比较结果表
truncate table t_db_diff;
创建进行数据库结构表较的存储过程
CREATE OR REPLACE PROCEDURE P_DB_COMPARE(P_LeftSchema varchar2,P_RightSchema varchar2)
IS
CURSOR GETDATA IS SELECT * FROM T_SCHEMA_METADATA T
where t.table_name not in ('T_DB_DIFF','T_SCHEMA_METADATA_RIGHT','T_SCHEMA_METADATA','T_UPGRADE_DB_SQL')
ORDER BY T.TABLE_NAME,T.COLUMN_NAME;
cursor getdata2 is
SELECT * FROM t_Schema_Metadata_right
where table_name<>'T_SCHEMA_METADATA'
ORDER BY TABLE_NAME,COLUMN_NAME;
v_count number(10);
v_table_name varchar2(2000);
v_column_name varchar2(2000);
v_data_type varchar2(200);
v_data_length varchar2(200);
v_cn number(10);
v_previous_table varchar2(2000);
BEGIN
v_cn:=0;
--1.positive compare
--compare each table,column,datatype,and data length
for rec in getdata loop
if rec.table_name<>v_previous_table then
v_cn:=0;
end if;
--if exists the table
select count(*) into v_count from t_Schema_Metadata_right
where table_name=rec.table_name;
--if the table exists
if v_count>0 then
begin
select table_name,column_name,data_type,data_length
into v_table_name,v_column_name,v_data_type,v_data_length
from t_Schema_Metadata_right
where table_name=rec.table_name
and column_name=rec.column_name;
--compare datatype
if rec.data_type<>v_data_type then
insert into t_db_diff(
L_SCHEMA_NAME ,
L_TABLE_NAME ,
L_COLUMN_NAME ,
L_DATA_TYPE ,
L_DATA_LENGTH ,
L_TABLE_EXISTS ,
L_COLUMN_EXISTS ,
R_SCHEMA_NAME ,
R_TABLE_NAME ,
R_COLUMN_NAME ,
R_DATA_TYPE ,
R_DATA_LENGTH ,
R_TABLE_EXISTS ,
R_COLUMN_EXISTS )
values(
P_LeftSchema,
rec.table_name,
rec.column_name,
rec.data_type,
rec.data_length,
'table exists',
'column exists',
P_RightSchema,
v_table_name,
v_column_name,
v_data_type,
v_data_length,
'table exists',
'column exists'
);
--if datatype equal,but data length not equal
elsif rec.data_type=v_data_type and rec.data_length<>v_data_length then
insert into t_db_diff(
L_SCHEMA_NAME ,
L_TABLE_NAME ,
L_COLUMN_NAME ,
L_DATA_TYPE ,
L_DATA_LENGTH ,
L_TABLE_EXISTS ,
L_COLUMN_EXISTS ,
R_SCHEMA_NAME ,
R_TABLE_NAME ,
R_COLUMN_NAME ,
R_DATA_TYPE ,
R_DATA_LENGTH ,
R_TABLE_EXISTS ,
R_COLUMN_EXISTS )
values(
P_LeftSchema,
rec.table_name,
rec.column_name,
rec.data_type,
rec.data_length,
'table exists',
'column exists',
P_RightSchema,
v_table_name,
v_column_name,
v_data_type,
v_data_length,
'table exists',
'column exists'
);
end if;
exception
--table exists,but column does not exits
when no_data_found then
insert into t_db_diff(
L_SCHEMA_NAME ,
L_TABLE_NAME ,
L_COLUMN_NAME ,
L_DATA_TYPE ,
L_DATA_LENGTH ,
L_TABLE_EXISTS ,
L_COLUMN_EXISTS ,
R_SCHEMA_NAME ,
R_TABLE_NAME ,
R_COLUMN_NAME ,
R_DATA_TYPE ,
R_DATA_LENGTH ,
R_TABLE_EXISTS ,
R_COLUMN_EXISTS )
values(
P_LeftSchema,
rec.table_name,
rec.column_name,
rec.data_type,
rec.data_length,
'table exists',
'column exists',
P_RightSchema,
rec.table_name,
null,
null,
null,
'table exists',
'column does not exists'
);
end ;
--if the table not exists
else
if v_cn=0 then
insert into t_db_diff(
L_SCHEMA_NAME ,
L_TABLE_NAME ,
L_COLUMN_NAME ,
L_DATA_TYPE ,
L_DATA_LENGTH ,
L_TABLE_EXISTS ,
L_COLUMN_EXISTS ,
R_SCHEMA_NAME ,
R_TABLE_NAME ,
R_COLUMN_NAME ,
R_DATA_TYPE ,
R_DATA_LENGTH ,
R_TABLE_EXISTS ,
R_COLUMN_EXISTS )
values(
P_LeftSchema,
rec.table_name,
null,
null,
null,
'table exists',
null,
P_RightSchema,
null,
null,
null,
null,
'table does not exists',
null
);
v_cn:=1;
v_previous_table:=rec.table_name;
end if;
end if;
end loop;
commit;
--2.reverse compare
v_cn:=0;
--compare each table,column,datatype,and data length
for rec
if rec2.table_name<>v_previous_table then
v_cn:=0;
end if;
--if exists the table
select count(*) into v_count from t_schema_metadata
where table_name=rec2.table_name;
--if the table exists
if v_count>0 then
begin
select table_name,column_name,data_type,data_length
into v_table_name,v_column_name,v_data_type,v_data_length
from t_schema_metadata
where table_name=rec2.table_name
and column_name=rec2.column_name;
exception
--table exists,but column does not exits
when no_data_found then
insert into t_db_diff(
L_SCHEMA_NAME ,
L_TABLE_NAME ,
L_COLUMN_NAME ,
L_DATA_TYPE ,
L_DATA_LENGTH ,
L_TABLE_EXISTS ,
L_COLUMN_EXISTS ,
R_SCHEMA_NAME ,
R_TABLE_NAME ,
R_COLUMN_NAME ,
R_DATA_TYPE ,
R_DATA_LENGTH ,
R_TABLE_EXISTS ,
R_COLUMN_EXISTS )
values(
P_LeftSchema,
rec2.table_name,
null,
null,
null,
'table exists',
'column dose not exists',
P_RightSchema,
rec2.table_name,
rec2.column_name,
rec2.data_type,
rec2.data_length,
'table exists',
'column exists'
);
end ;
--if the table not exists
else
if v_cn=0 then
insert into t_db_diff(
L_SCHEMA_NAME ,
L_TABLE_NAME ,
L_COLUMN_NAME ,
L_DATA_TYPE ,
L_DATA_LENGTH ,
L_TABLE_EXISTS ,
L_COLUMN_EXISTS ,
R_SCHEMA_NAME ,
R_TABLE_NAME ,
R_COLUMN_NAME ,
R_DATA_TYPE ,
R_DATA_LENGTH ,
R_TABLE_EXISTS ,
R_COLUMN_EXISTS )
values(
P_LeftSchema,
null,
null,
null,
null,
'table does not exists',
null,
P_RightSchema,
rec2.table_name,
null,
null,
null,
'table exists',
null
);
v_cn:=1;
v_previous_table:=rec2.table_name;
end if;
end if;
end loop;
commit;
END P_DB_COMPARE;
/
执行存储过程，开始进行数据库结构的比较
exec p_db_compare('XPC90_INSTALL','XPC90UPGRADE_18');
查看数据库比较报告
1．查看左边存在，右边不存在的表，
select * from t_db_diff t
where t.l_table_exists='table exists'
and t.r_table_exists='table does not exists'
order by t.l_table_name;
2．查看左边不存在，右边存在的表
select * from t_db_diff t
where t.l_table_exists='table does not exists'
and t.r_table_exists='table exists'
order by t.l_table_name;
3．查看左边存在，右边不存在的字段
select * from t_db_diff t
where t.l_table_exists='table exists'
and t.l_column_exists='column exists'
and t.r_table_exists='table exists'
and t.r_column_exists='column does not exists'
order by t.l_table_name,t.l_column_name;
4．查看左边不存在，右边存在的字段
select * from t_db_diff t
where t.l_table_exists='table exists'
and t.l_column_exists='column dose not exists'
and t.r_table_exists='table exists'
and t.r_column_exists='column exists'
order by t.r_table_name,t.r_column_name;
5．查看数据类型不一致的字段
select * from t_db_diff t
where t.l_table_exists='table exists'
and t.l_column_exists='column exists'
and t.r_table_exists='table exists'
and t.r_column_exists='column exists'
and t.l_data_type<>t.r_data_type
order by t.l_table_name,t.l_column_name;
6.数据类型相同，但长度不一致的字段
select * from t_db_diff t
where t.l_table_exists='table exists'
and t.l_column_exists='column exists'
and t.r_table_exists='table exists'
and t.r_column_exists='column exists'
and t.l_data_type=t.r_data_type
and t.l_data_length<>t.r_data_length
order by t.l_table_name,t.l_column_name;
7.全表查询
select * from t_db_diff t order by t.l_table_name,t.l_column_name；
四，数据库同步
创建存储同步SQL语句的表
CREATE TABLE T_upgrade_db_sql(
operation_desc varchar2(2000),
operation_table varchar2(2000),
upgrade_sql clob );
创建获取表的DDL SQL语句的存储过程
create or replace procedure p_get_table_ddl(P_SCHEMA VARCHAR2 ,P_TABLE_NAME VARCHAR2)
is
--get table
CURSOR cu_tab IS
SELECT a.table_name,
a.pct_free,
a.pct_used,
a.ini_trans,
a.max_trans,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.min_extents,
a.max_extents,
a.pct_increase
FROM all_tables a
WHERE a.table_name = upper(p_table_name)
AND a.owner = Upper(P_SCHEMA);
--get table columns
CURSOR cu_col (in_table IN VARCHAR2) IS
SELECT a.column_name,
a.data_type,
decode(a.DATA_TYPE,
'VARCHAR2',a.CHAR_LENGTH,
'NVARCHAR2',a.CHAR_LENGTH,
'NCHAR',a.CHAR_LENGTH,
'CHAR',a.CHAR_LENGTH,
'NVARCHAR',a.CHAR_LENGTH,
'VARCHAR',a.CHAR_LENGTH,
'BLOB',NULL,
'CLOB',NULL,
'DATE',a.data_length,
'TIMESTAMP',NULL,NULL) data_length,
To_Char(a.data_precision) data_precision,
Decode(a.data_scale,NULL,'',0,'',',' || To_Char(a.data_scale)) data_scale,
Decode(a.nullable,'Y','NULL','N','NOT NULL') nullable
FROM all_tab_columns a
WHERE a.table_name = in_table
AND a.owner = Upper(P_SCHEMA);
V_UPGRADE_SQL CLOB;
--get table constraints
CURSOR cu_constraints is
select *
from sys.all_constraints
where table_name = upper(p_table_name)
and owner = Upper(P_SCHEMA)
and constraint_type in ('P', 'U', 'R')
order by decode(constraint_type, 'P', 0, 'U', 1, 'R', 2, 3),
constraint_name;
--get table constraits clumns
CURSOR cu_constraint_col(in_constraint_name in varchar2) is
select *
from sys.all_cons_columns
where owner = Upper(P_SCHEMA)
and table_name = upper(p_table_name)
and constraint_name = in_constraint_name
order by position;
--get foreign key reference table constraint columns
CURSOR cu_r_constraint_col(in_r_owner in varchar2,in_r_table in varchar2,in_r_constraint_name in varchar2) is
select *
from sys.all_cons_columns
where owner = in_r_owner
and table_name = in_r_table
and constraint_name = in_r_constraint_name
order by position;
v_reference_table varchar2(200);
--get table indexes
cursor cu_index is
select * from sys.all_indexes
where table_name = upper(P_TABLE_NAME)
and table_owner = upper(P_SCHEMA)
and uniqueness='NONUNIQUE'
order by index_name;
--get table index columns
cursor cu_index_col(p_indexowner varchar2,p_indexname varchar2) is
select * from sys.all_ind_columns
where index_owner = p_indexowner
and index_name = p_indexname
order by column_position;
BEGIN
V_UPGRADE_SQL:='--create table'||chr(10);
V_UPGRADE_SQL:=V_UPGRADE_SQL||'CREATE TABLE '||P_TABLE_NAME||'('||CHR(10);
FOR cur_tab IN cu_tab LOOP
FOR cur_col IN cu_col (in_table => cur_tab.table_name) LOOP
IF cur_col.data_type = 'DATE' THEN
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type,20,' ') || ' ' || cur_col.nullable||','||CHR(10);
ELSIF cur_col.data_type = 'NUMBER' THEN
if cur_col.data_precision is null and cur_col.data_scale is null then
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type ,20,' ') || ' ' || cur_col.nullable||','||CHR(10);
else
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type || '(' || cur_col.data_precision || cur_col.data_scale || ')',20,' ') || ' ' || cur_col.nullable||','||CHR(10);
end if;
ELSIF cur_col.data_type = 'BLOB' THEN
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type,20,' ') || ' ' || cur_col.nullable||','||CHR(10);
ELSIF cur_col.data_type = 'CLOB' THEN
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type,20,' ') || ' ' || cur_col.nullable||','||CHR(10);
ELSE--STRING
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type || '(' || cur_col.data_length || ')',20,' ') || ' ' || cur_col.nullable||','||CHR(10);
END IF;
END LOOP;
V_UPGRADE_SQL:=SUBSTR(V_UPGRADE_SQL,1,(INSTR(V_UPGRADE_SQL,',',-1))-1);
V_UPGRADE_SQL:=V_UPGRADE_SQL||' );'||chr(10);
END LOOP;
V_UPGRADE_SQL:=V_UPGRADE_SQL||'--add constraints'||chr(10);
--get table constraints
for REC in cu_constraints loop
--primary key
if rec.constraint_type='P' THEN
V_UPGRADE_SQL:=V_UPGRADE_SQL||'ALTER TABLE '||P_TABLE_NAME||' ADD CONSTRAINT '||REC.Constraint_Name||' PRIMARY KEY('||CHR(10);
--get pk constraint columns
for REC
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(REC2.column_name),30,' ')||','||CHR(10);
end loop;
V_UPGRADE_SQL:=SUBSTR(V_UPGRADE_SQL,1,(INSTR(V_UPGRADE_SQL,',',-1))-1);
V_UPGRADE_SQL:=V_UPGRADE_SQL||' );'||chr(10);
--Foreign key
ELSIF rec.constraint_type='R' THEN
V_UPGRADE_SQL:=V_UPGRADE_SQL||'ALTER TABLE '||P_TABLE_NAME||' ADD CONSTRAINT '||REC.Constraint_Name||' FOREIGN KEY('||CHR(10);
--find fk column
for REC
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(REC2.column_name),30,' ')||','||CHR(10);
end loop;
V_UPGRADE_SQL:=SUBSTR(V_UPGRADE_SQL,1,(INSTR(V_UPGRADE_SQL,',',-1))-1);
--find reference table
select distinct table_name into v_reference_table
from sys.all_cons_columns
where owner = rec.r_owner
and constraint_name = rec.r_constraint_name;
V_UPGRADE_SQL:=V_UPGRADE_SQL||' ) REFERENCES '||v_reference_table||'('||chr(10);
--find reference columns
for REC
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(REC3.column_name),30,' ')||','||CHR(10);
end loop;
V_UPGRADE_SQL:=SUBSTR(V_UPGRADE_SQL,1,(INSTR(V_UPGRADE_SQL,',',-1))-1);
V_UPGRADE_SQL:=V_UPGRADE_SQL||' );'||chr(10);
--Unique key
ELSIF rec.constraint_type='U' THEN
V_UPGRADE_SQL:=V_UPGRADE_SQL||'ALTER TABLE '||P_TABLE_NAME||' ADD CONSTRAINT '||REC.Constraint_Name||' UNIQUE('||CHR(10);
--get unique constraint columns
for REC
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(REC4.column_name),30,' ')||','||CHR(10);
end loop;
V_UPGRADE_SQL:=SUBSTR(V_UPGRADE_SQL,1,(INSTR(V_UPGRADE_SQL,',',-1))-1);
V_UPGRADE_SQL:=V_UPGRADE_SQL||' );'||chr(10);
END IF;
end loop;
--create indexes
V_UPGRADE_SQL:=V_UPGRADE_SQL||'--create indexes'||chr(10);
for REC
V_UPGRADE_SQL:=V_UPGRADE_SQL||'CREATE INDEX '||rec5.index_name||' ON '||P_TABLE_NAME||'('||CHR(10);
--get index columns
for REC
V_UPGRADE_SQL:=V_UPGRADE_SQL||RPad(Lower(rec6.column_name),30,' ')||','||CHR(10);
end loop;
V_UPGRADE_SQL:=SUBSTR(V_UPGRADE_SQL,1,(INSTR(V_UPGRADE_SQL,',',-1))-1);
V_UPGRADE_SQL:=V_UPGRADE_SQL||' );'||chr(10);
end loop;
INSERT INTO T_UPGRADE_DB_SQL(
OPERATION_DESC ,
OPERATION_TABLE ,
UPGRADE_SQL )
VALUES('CREATE TABLE',
p_table_name,
V_UPGRADE_SQL);
COMMIT;
END;
/
创建生成数据库同步SQL语句的存储过程
create or replace procedure p_generate_syncdb_sql
is
--get only exists in left schema tables list
cursor cu_leftexists_tab is
select * from t_db_diff t
where t.l_table_exists='table exists'
and t.r_table_exists='table does not exists'
order by t.l_table_name;
--get only exists in left schema columns list
cursor cu_leftexists_col is
select * from t_db_diff t
where t.l_table_exists='table exists'
and t.l_column_exists='column exists'
and t.r_table_exists='table exists'
and t.r_column_exists='column does not exists'
order by t.l_table_name,t.l_column_name;
--get the column need to be modify data type or data length
cursor cu_tobemodify_col is
select * from t_db_diff t
where t.l_table_exists='table exists'
and t.l_column_exists='column exists'
and t.r_table_exists='table exists'
and t.r_column_exists='column exists'
order by t.l_table_name,t.l_column_name;
V_UPGRADE_SQL clob;
v_data_length varchar2(2000);
begin
--(1)generate create table sql
for rec in cu_leftexists_tab loop
p_get_table_ddl(rec.l_schema_name,rec.l_table_name);
end loop;
--(2)generate add column sql
for rec
V_UPGRADE_SQL:='--alter table add column'||chr(10);
V_UPGRADE_SQL:=V_UPGRADE_SQL||'ALTER TABLE '||REC2.L_TABLE_NAME||' add ';
if rec2.l_data_type='NUMBER' AND substr(rec2.l_data_length,-2,2)=',0' then
v_data_length:=substr(rec2.l_data_length,1,length(rec2.l_data_length)-2);
V_UPGRADE_SQL:=V_UPGRADE_SQL||Lower(rec2.l_column_name)||' '||rec2.l_data_type||'('||v_data_length||');'||CHR(10);
elsif rec2.l_data_type in ('CLOB','BLOB','DATE') then
V_UPGRADE_SQL:=V_UPGRADE_SQL||Lower(rec2.l_column_name)||' '||rec2.l_data_type||';'||CHR(10);
else
V_UPGRADE_SQL:=V_UPGRADE_SQL||Lower(rec2.l_column_name)||' '||rec2.l_data_type||'('||rec2.l_data_length||');'||CHR(10);
end if;
INSERT INTO T_UPGRADE_DB_SQL(
OPERATION_DESC ,
OPERATION_TABLE ,
UPGRADE_SQL )
VALUES('ALTER TABLE ADD COLUMN',
REC2.L_TABLE_NAME,
V_UPGRADE_SQL);
COMMIT;
end loop;
--(3)generate modify column sql(only extend data length)
for rec
V_UPGRADE_SQL:='--alter table modify column'||chr(10);
V_UPGRADE_SQL:=V_UPGRADE_SQL||'--original datatype is:'||REC3.R_DATA_TYPE||',original data length is:'||rec3.r_data_length||chr(10);
V_UPGRADE_SQL:=V_UPGRADE_SQL||'ALTER TABLE '||REC3.R_TABLE_NAME||' MODIFY ';
if rec3.l_data_type='NUMBER' AND substr(rec3.l_data_length,-2,2)=',0' then
v_data_length:=substr(rec3.l_data_length,1,length(rec3.l_data_length)-2);
V_UPGRADE_SQL:=V_UPGRADE_SQL||Lower(rec3.l_column_name)||' '||rec3.l_data_type||'('||v_data_length||');'||CHR(10);
elsif rec3.l_data_type in ('CLOB','BLOB','DATE') then
V_UPGRADE_SQL:=V_UPGRADE_SQL||Lower(rec3.l_column_name)||' '||rec3.l_data_type||';'||CHR(10);
else
V_UPGRADE_SQL:=V_UPGRADE_SQL||Lower(rec3.l_column_name)||' '||rec3.l_data_type||'('||rec3.l_data_length||');'||CHR(10);
end if;
INSERT INTO T_UPGRADE_DB_SQL(
OPERATION_DESC ,
OPERATION_TABLE ,
UPGRADE_SQL )
VALUES('ALTER TABLE MODIFY COLUMN',
REC3.R_TABLE_NAME,
V_UPGRADE_SQL);
COMMIT;
end loop;
end p_generate_syncdb_sql;
/
执行存储过程以生成DB同步SQL语句：
exec p_generate_syncdb_sql;
查看同步SQL语句：
select to_char(upgrade_sql) from t_upgrade_db_sql order by operation_table;
将SQL语句拷到UltraEdit:
去除UltraEdit中的"双引号:
方法是：
--
保存修饰过后的文件，最终生成的SQL脚本文件如下图：
- 如何比较oracle数据库差异
- 如何快速地比较两数据库结构的差异
- 如何快速地比较两数据库结构的差异
- 如何快速地比较两数据库结构的差异
- 如何快速地比较两数据库结构的差异
- 比较两数据库的差异
- MsSQL2005新旧数据库比较差异
- mysql比较数据库结构差异
- 比较Oracle 的2个数据库的结构差异，生成更新语句
- MySQL与Oracle 差异比较之一数据类型
- MySQL与Oracle 差异比较之一数据类型
- MySQL与Oracle 差异函数比较
- MySQL与Oracle SQL语言差异比较
- MySQL与Oracle 函数差异比较
- MySQL与Oracle 差异比较之函数
- MySQL与Oracle 差异比较之一数据类型
- MySQL与Oracle 差异比较之一数据类型
- MySQL与Oracle SQL语言差异比较
- 修改MYSQL的字符集和排序规则
- 部署ASP.NET程序问题总汇
- Flex css 实现多种皮肤切换
- MSDTC不能启动的几个解决方法
- ireport学习笔记---第一个报表
- 如何比较oracle数据库差异
- C# 操作文件夹及文件
- C#钩子实现简单鼠标键盘的监控和屏蔽
- 这个问题是这样的
- JavaFX体验——弹跳的视频球
- 头文件的规则
- 【转】GDI使用经验分享
- struts中和validator相关的一些注意事项
- 2008年总结＋2009展望
