开发需要比对生产与测试对象结构差异,将生产环境对象结构同步至测试环境,同时保留测试环境数据。包括表、视图、存储过程等约两千个,人工比对肯定不现实,搜了一下原来plsqldev就有这个功能。
一、 对比对象结构差异
左下方选择目标会话,例如源会话是A用户,目标会话是B用户,对比后会生成将B中对象改为和A中一样的sql,不要填反。
为避免对比影响生产环境,这里将生产环境对象结构dump到测试环境tmp用户,用测试环境的业务用户和tmp用户来比较。
selection中可以选择要对比的对象,如果不选默认会对比全部。
设置选项,第一项是指创建语句中还要包含表空间之类的存储配置,一般没这个必要。
点击右下角compare开始比较即可。
经观察对比约两千对象对性能基本没有什么影响,但是对比速度很慢,十分钟过去进度条才这么一丢丢。
对比结果可以在differences部分看到,右侧工具可以选择保存sql、复制sql、在目标库应用sql、查看差异。
另外如果单纯是表结构的对比,查到了一些sql语句可以快速进行比较
SELECT M.OWNER
,M.TABLE_NAME
,M.COLUMN_ID
,M.COLUMN_NAME
,M.DATA_TYPE
,M.DATA_LENGTH
,N.OWNER
,N.TABLE_NAME
,N.COLUMN_ID
,N.COLUMN_NAME
,N.DATA_TYPE
,N.DATA_LENGTH
FROM
(
SELECT OWNER
,TABLE_NAME
,COLUMN_ID
,COLUMN_NAME
,DATA_TYPE
,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER=&OWNER_A
AND TABLE_NAME=&TABLE_NAME_A
) M LEFT JOIN
(
SELECT OWNER
,TABLE_NAME
,COLUMN_ID
,COLUMN_NAME
,DATA_TYPE
,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER=&OWNER_B
AND TABLE_NAME=&TABLE_NAME_B
) N ON M.COLUMN_ID =N.COLUMN_ID
AND M.COLUMN_NAME = N.COLUMN_NAME
AND M.DATA_TYPE = N.DATA_TYPE
AND M.DATA_LENGTH= N.DATA_LENGTH
ORDER BY M.TABLE_NAME, M.COLUMN_ID;
-- 但是如果A表与B表前面的字段一致,然而B表有一些额外的字段,那么上面的SQL(左连接)就会查不出两者之间结构的不同。此时需要使用右连接才能对比出真正的表结构。所以如果需要对比两者的表结构是否一致,最好左连接查一次,右连接查一次。才能真正的确认两者的表结构的异同。
SELECT M.OWNER
,M.TABLE_NAME
,M.COLUMN_ID
,M.COLUMN_NAME
,M.DATA_TYPE
,M.DATA_LENGTH
,N.OWNER
,N.TABLE_NAME
,N.COLUMN_ID
,N.COLUMN_NAME
,N.DATA_TYPE
,N.DATA_LENGTH
FROM
(
SELECT OWNER
,TABLE_NAME
,COLUMN_ID
,COLUMN_NAME
,DATA_TYPE
,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER=&OWNER_A
AND TABLE_NAME=&TABLE_NAME_A
) M RIGHT JOIN
(
SELECT OWNER
,TABLE_NAME
,COLUMN_ID
,COLUMN_NAME
,DATA_TYPE
,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER=&OWNER_B
AND TABLE_NAME=&TABLE_NAME_B
) N ON M.COLUMN_ID =N.COLUMN_ID
AND M.COLUMN_NAME = N.COLUMN_NAME
AND M.DATA_TYPE = N.DATA_TYPE
AND M.DATA_LENGTH= N.DATA_LENGTH
ORDER BY M.TABLE_NAME, M.COLUMN_ID;
二、 对比表数据差异
跟对象结构对比基本类似,selection中可以选择要对比的对象,如果不选默认会对比全部。
左下方选择目标会话
设置选项,可以选生成脚本,也可以选直接执行
点击右下角compare开始比较即可。
对比结果可以在differences部分看到,右侧工具可以选择保存sql、复制sql、在目标库应用sql。
三、 其他方法
在oracle中,将表结构迁移其实有多种方法:
1. exp导出,使用 row=n 选项
2. expdp导出,使用 CONTENT=METADATA_ONLY 选项
3. 利用DBMS_METADATA.GET_DDL,利用该方法可以将当初的建表语句导出成文本,且建表语句包含storage。
set pagesize 0
set long 90000
set feedback off
set echo off
SELECT DBMS_METADATA.GET_DDL('TABLE', U.TABLE_NAME) FROM USER_TABLES U;
SELECT DBMS_METADATA.GET_DDL('INDEX', U.INDEX_NAME) FROM USER_INDEXES U;
SELECT DBMS_METADATA.GET_DDL('VIEW', U.VIEW_NAME) FROM User_Views U;
4. 利用user_tables and user_tab_columns导出,优点是不涉及到storage,仅仅导出建表的create语句。
SELECT decode(ta.column_id,
tb.mincol_id,
'CREATE TABLE ' || ta.table_name || chr(10) || '(' || chr(10),
'') || rpad(column_name, 40) || data_type ||
decode(data_type,
'NUMBER',
decode(sign(data_precision),
1,
'(' || data_precision || ',' || data_scale || ')',
''),
decode(sign(instr('DATE,LONG,LONG RAW,BLOB,CLOB,FLOAT,UNDEFINED,MLSLABEL,',
data_type || ',')),
1,
'',
'(' || data_length || ')')) ||
decode(ta.column_id, tb.maxcol_id, chr(10) || ');', ',')
FROM user_tab_columns ta,
(SELECT table_name,
MAX(column_id) maxcol_id,
MIN(column_id) mincol_id
FROM user_tab_columns
GROUP BY table_name) tb,
user_tables tc
WHERE ta.table_name = tb.table_name
AND ta.table_name = tc.table_name
ORDER BY ta.table_name, ta.column_id;
参考
https://blog.csdn.net/wengtf/article/details/48439189