最近公司项目繁忙,实在没有时间写博,内心充满不安。
好在本次遇到一个需要“对比两个相似schema中table字段结构,并生成Scripts” 的需求,特整理于此,以平民愤 -_-,
背景如下:
1. schemaA是我们的新版本数据库框架
2. schemaB是我们的旧系统,包含了若干数据
3. 甲方要求升级必须保留原有交易数据
4. 显然schemaA、schemaB 从索引结构、序列配置、字段配置 是有多处不同的
我该怎么处理呢?
我的思路:
1. 直接使用 schemaA 数据库结构,这样可以忽略 索引结构、序列配置 的过程
2. 需要将数据从 schemaB insert 至 schemaA 中
3. 但是相对于schemaB ,schemaA已经做了太多字段变更与改进。直接insert 会有一大堆ora- 出来
4. 我需要在insert之前,将schemaB字段调整与schemaA一致,如果一个个去找,再拼接脚本,我会疯掉的。。。
5. 那么我们需要关注 all_tab_cols 这个数据字典
我的脚本:
脚本的设计思路很简单:
1. 先使用对比的办法,针对一个table分别生成 add 与 drop SQL,再union。 为过程A
2. 使用游标遍历两个schema的table得到table_list , 并循环调用过程A
3. 将得到的SQL保存至table以便读取
先查看一下tablename的情况吧
select * from
(select table_name from user_tables) a
full outer join
(select table_name from all_tables where owner='JS_TEMP') b
on a.table_name=b.table_name
where a.table_name is null or b.table_name is null
sysdba:
grant select any table to jsec;
jsec:
/*保存结果SQL的表,先建立好,以免后面编译出错*/
CREATE TABLE T_COMPARE_COLS_SCRIPTS (SCRIPTS VARCHAR2(500))
/
/*该过程针对一个table得出字段整改SQL*/
CREATE OR REPLACE PROCEDURE P_COMPARE_COLS_SCRIPTS
(V_TABLE_NAME IN VARCHAR2,
V_SCHEMA_A IN VARCHAR2,
V_SCHEMA_B IN VARCHAR2)
AS
BEGIN
INSERT INTO T_COMPARE_COLS_SCRIPTS
SELECT 'ALTER TABLE '||V_SCHEMA_A||'.'||V_TABLE_NAME||' ADD '||B.COLUMN_NAME||' '||B.DATA_TYPE
||CASE WHEN B.DATA_TYPE IN ('DATE','CLOB','BLOB') THEN ''
ELSE CASE WHEN B.DATA_LENGTH>0 THEN '(' ELSE '' END||B.DATA_LENGTH||CASE WHEN B.DATA_LENGTH>0 THEN ')'
ELSE '' END
END
--||CASE WHEN B.NULLABLE='N' THEN ' NOT NULL' ELSE '' END --我暂时忽略了是否为null的定义,如果需要可以解注
--||CASE WHEN B.DATA_DEFAULT IS NOT NULL THEN '' ELSE '' END
--我这里暂时还不能处理好默认值的问题,因为数据字典中是LONG类型,不能直接转换为字符串,但是可以在create table中to_lob ,再to_char
||';'
AS SCRIPTS
FROM
(SELECT COLUMN_NAME,DATA_TYPE,TO_CHAR(DATA_LENGTH) DATA_LENGTH,NULLABLE,DATA_DEFAULT
FROM ALL_TAB_COLS
WHERE OWNER = V_SCHEMA_A
AND TABLE_NAME = V_TABLE_NAME
AND VIRTUAL_COLUMN!='YES') A
FULL OUTER JOIN
(SELECT COLUMN_NAME,DATA_TYPE,TO_CHAR(DATA_LENGTH) DATA_LENGTH,NULLABLE,DATA_DEFAULT
FROM ALL_TAB_COLS
WHERE OWNER = V_SCHEMA_B
AND TABLE_NAME = V_TABLE_NAME
AND VIRTUAL_COLUMN!='YES') B
ON A.COLUMN_NAME = B.COLUMN_NAME
WHERE A.COLUMN_NAME IS NULL
UNION ALL
SELECT 'ALTER TABLE '||V_SCHEMA_A||'.'||V_TABLE_NAME||' DROP COLUMN '||A.COLUMN_NAME||';'
AS SCRIPTS
FROM
(SELECT COLUMN_NAME,DATA_TYPE,TO_CHAR(DATA_LENGTH) DATA_LENGTH
FROM ALL_TAB_COLS
WHERE OWNER = V_SCHEMA_A
AND TABLE_NAME = V_TABLE_NAME
AND VIRTUAL_COLUMN!='YES') A
FULL OUTER JOIN
(SELECT COLUMN_NAME,DATA_TYPE,TO_CHAR(DATA_LENGTH) DATA_LENGTH
FROM ALL_TAB_COLS
WHERE OWNER = V_SCHEMA_B
AND TABLE_NAME = V_TABLE_NAME
AND VIRTUAL_COLUMN!='YES') B
ON A.COLUMN_NAME = B.COLUMN_NAME
WHERE B.COLUMN_NAME IS NULL;
END;
/
/*该过程先遍历出tablelist,然后fatch出tablename,循环调用上面的过程*/
CREATE OR REPLACE PROCEDURE P_COMPARE_COL_DRIVER
(V_SCHEMA_DRIVER_A VARCHAR2,
V_SCHEMA_DRIVER_B VARCHAR2)
AS
V_TABLE_DRIVER_NAME VARCHAR2(50);
BEGIN
DELETE FROM T_COMPARE_COLS_SCRIPTS;
DECLARE
CURSOR C_TABLE_LIST IS
SELECT A.TABLE_NAME AS TABLE_NAME_A
FROM (SELECT TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = V_SCHEMA_DRIVER_A) A
INNER JOIN (SELECT TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = V_SCHEMA_DRIVER_B) B
ON A.TABLE_NAME = B.TABLE_NAME
ORDER BY TABLE_NAME_A;
BEGIN
OPEN C_TABLE_LIST;
FETCH C_TABLE_LIST
INTO V_TABLE_DRIVER_NAME;
WHILE C_TABLE_LIST%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(V_TABLE_DRIVER_NAME);
BEGIN
P_COMPARE_COLS_SCRIPTS(V_TABLE_NAME => V_TABLE_DRIVER_NAME,
V_SCHEMA_A => V_SCHEMA_DRIVER_A,
V_SCHEMA_B => V_SCHEMA_DRIVER_B);
END;
FETCH C_TABLE_LIST
INTO V_TABLE_DRIVER_NAME;
END LOOP;
COMMIT;
END;
END;
/
/*先测试一下*/
begin
p_compare_cols_scripts (v_table_name => 'BARGAINOR',v_schema_A => 'JSEC',v_schema_B => 'JS_TEMP');
end;
/*正式运行*/
begin
P_COMPARE_COL_DRIVER(v_schema_driver_A => 'JS_TEMP',v_schema_driver_B => 'JSEC');
end;
/*查看SQL结果,并copy出来在schemaB端执行*/
SELECT * FROM T_COMPARE_COLS_SCRIPTS ORDER BY SCRIPTS
/*两个schema结构已经一致,就可以insert了,当然是用动态SQL,不过为了加快效率,我用了 append与nologning*/
select
'insert /*+append*/ into jsec.'||table_name||' select * from js_temp.'||table_name||' nologging;'
from user_tables
where table_name in (select table_name from all_tables where owner='JSEC')
order by table_name