Oracle比较两库或用户下对象结构、表数据差异

开发需要比对生产与测试对象结构差异，将生产环境对象结构同步至测试环境，同时保留测试环境数据。包括表、视图、存储过程等约两千个，人工比对肯定不现实，搜了一下原来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

http://www.51testing.com/html/35/15073535-3718989.html

https://www.cnblogs.com/kerrycode/p/4664094.html