我们经常会遇到这样的问题：比较两张表的结构是否相同。比如：

在做数据仓库时，经常会创建生产系统的表结构作为ODS的临时表，而生存系统数据库中源表如果发生变更则ODS相应的这些临时表也要发生变更。

在数据迁移时，直接拷贝数据库则两个库中表结构一致。但是，在两个库中做相应的开发难免会出现不一致的情况。

而对每张表的每个字段检查是不现实的。故通过查找数据字典比较两个库的两张表或同一个库的两张表的差异是非常有用的。

常见的表不同分为：schema不同、数据表空间不同、表注释不同、表数据结构不同、表索引不同。

前三种不同可以通过all_tables , all_tab_comments 很容易比较出来。在此不做详述。下面着重分析两张表的数据结构不同，表索引不同。

1、表的数据结构不同：

表的数据结构常见的有列名，列数据类型，是否为空。现在，我们将与表中该列相关的所有属性都关注一下，比如字段注释，字段默认值。

SELECT DECODE(SUM(flag),-1,'TEST3','TEST2') Table_name,col_ind_name,
col_ind_type,null_pressed,cols
FROM (
SELECT atc.COLUMN_NAME AS col_ind_name,
CASE WHEN data_type = 'DATE' THEN 'DATE'
WHEN data_type = 'NUMBER' AND DATA_precision IS NULL AND data_scale IS NULL THEN 'NUMBER'
WHEN data_type = 'NUMBER' AND DATA_precision IS NULL AND data_scale = 0 THEN 'INTEGER'
WHEN data_type = 'NUMBER' AND DATA_precision IS NOT NULL
AND data_scale IS NOT NULL THEN 'NUMBER('||DATA_precision||','||data_scale||')'
WHEN data_type = 'NUMBER' AND DATA_precision IS NOT NULL
AND data_scale IS NULL THEN 'NUMBER('||DATA_precision||')'
WHEN data_type = 'VARCHAR2' AND char_used = 'B' THEN 'VARCHAR2('||char_length||')'
WHEN data_type = 'VARCHAR2' AND char_used = 'C' THEN 'VARCHAR2('||char_length||' CHAR)'
WHEN data_type = 'CHAR' AND char_used = 'B' THEN 'CHAR('||char_length||')'
WHEN data_type = 'CHAR' AND char_used = 'C' THEN 'CHAR('||char_length||' CHAR)'
END col_ind_type,
decode(atc.NULLABLE,'N','No','Y','Yes',atc.NULLABLE) AS null_pressed,
acc.COMMENTS AS cols
, -1 AS flag
FROM All_Tab_Columns atc,all_col_comments acc
WHERE atc.TABLE_NAME = acc.TABLE_NAME
AND atc.COLUMN_NAME = acc.COLUMN_NAME
AND atc.TABLE_NAME = 'TEST2'
UNION ALL
SELECT atc.COLUMN_NAME AS col_ind_name,
CASE WHEN data_type = 'DATE' THEN 'DATE'
WHEN data_type = 'NUMBER' AND DATA_precision IS NULL AND data_scale IS NULL THEN 'NUMBER'
WHEN data_type = 'NUMBER' AND DATA_precision IS NULL AND data_scale = 0 THEN 'INTEGER'
WHEN data_type = 'NUMBER' AND DATA_precision IS NOT NULL
AND data_scale IS NOT NULL THEN 'NUMBER('||DATA_precision||','||data_scale||')'
WHEN data_type = 'NUMBER' AND DATA_precision IS NOT NULL
AND data_scale IS NULL THEN 'NUMBER('||DATA_precision||')'
WHEN data_type = 'VARCHAR2' AND char_used = 'B' THEN 'VARCHAR2('||char_length||')'
WHEN data_type = 'VARCHAR2' AND char_used = 'C' THEN 'VARCHAR2('||char_length||' CHAR)'
WHEN data_type = 'CHAR' AND char_used = 'B' THEN 'CHAR('||char_length||')'
WHEN data_type = 'CHAR' AND char_used = 'C' THEN 'CHAR('||char_length||' CHAR)'
END col_ind_type,
decode(atc.NULLABLE,'N','No','Y','Yes',atc.NULLABLE) AS null_pressed,
acc.COMMENTS AS cols
, -1 AS flag
FROM All_Tab_Columns atc,all_col_comments acc
WHERE atc.TABLE_NAME = acc.TABLE_NAME
AND atc.COLUMN_NAME = acc.COLUMN_NAME
AND atc.TABLE_NAME = 'TEST3' )
GROUP BY col_ind_name,col_ind_type,null_pressed,cols
HAVING SUM(flag) <> 0

2、表索引不同表索引不同常见关注的字段有索引名称、索引类型、是否可压缩、索引owner、索引列集、索引表空间

----------- 查找比较两张表的索引
SELECT DECODE(SUM(flag),-1,'TEST3','TEST2') Table_name,
col_ind_name,col_ind_type,null_pressed,owner,cols,ind_Tablespace
FROM (
SELECT ai.index_name AS col_ind_name,
CASE WHEN ai.UNIQUENESS = 'NONUNIQUE' AND ai.index_type = 'NORMAL' THEN 'Normal'
WHEN ai.UNIQUENESS = 'UNIQUE' AND ai.index_type = 'NORMAL' THEN 'Unique'
WHEN ai.UNIQUENESS = 'NONUNIQUE' AND ai.index_type = 'BITMAP' THEN 'Bitmap'
ELSE ai.UNIQUENESS||'-'||ai.index_type END AS col_ind_type ,
decode(ai.COMPRESSION,'DISABLED','No','ABLED','Yes',ai.COMPRESSION) AS null_pressed,
ai.owner ,
T_INDEX_COLUMNS.INDEX_COLUMNS AS cols,
ai.TABLESPACE_NAME AS ind_Tablespace
,1 AS flag
FROM all_indexes ai ,
(SELECT INDEX_NAME, MAX(COLS) INDEX_COLUMNS
FROM (SELECT AIC.INDEX_NAME,
WMSYS.WM_CONCAT(AIC.COLUMN_NAME) OVER(PARTITION BY AIC.INDEX_NAME ORDER BY AIC.COLUMN_POSITION) COLS
FROM ALL_IND_COLUMNS AIC
WHERE TABLE_NAME = 'TEST2') AAA
GROUP BY INDEX_NAME
) T_INDEX_COLUMNS
WHERE ai.INDEX_NAME = T_INDEX_COLUMNS.INDEX_NAME
UNION ALL
SELECT ai.index_name AS col_ind_name,
CASE WHEN ai.UNIQUENESS = 'NONUNIQUE' AND ai.index_type = 'NORMAL' THEN 'Normal'
WHEN ai.UNIQUENESS = 'UNIQUE' AND ai.index_type = 'NORMAL' THEN 'Unique'
WHEN ai.UNIQUENESS = 'NONUNIQUE' AND ai.index_type = 'BITMAP' THEN 'Bitmap'
ELSE ai.UNIQUENESS||'-'||ai.index_type END AS col_ind_type ,
decode(ai.COMPRESSION,'DISABLED','No','ABLED','Yes',ai.COMPRESSION) AS null_pressed,
ai.owner ,
T_INDEX_COLUMNS.INDEX_COLUMNS AS cols,
ai.TABLESPACE_NAME AS ind_Tablespace
,-1 AS flag
FROM all_indexes ai ,
(SELECT INDEX_NAME, MAX(COLS) INDEX_COLUMNS
FROM (SELECT AIC.INDEX_NAME,
WMSYS.WM_CONCAT(AIC.COLUMN_NAME) OVER(PARTITION BY AIC.INDEX_NAME ORDER BY AIC.COLUMN_POSITION) COLS
FROM ALL_IND_COLUMNS AIC
WHERE TABLE_NAME = 'TEST3') AAA
GROUP BY INDEX_NAME
) T_INDEX_COLUMNS
WHERE ai.INDEX_NAME = T_INDEX_COLUMNS.INDEX_NAME )
GROUP BY col_ind_name,col_ind_type,null_pressed,owner,cols,ind_Tablespace
HAVING SUM(flag) <> 0

---来源于：http://blog.csdn.net/xiaohou_2007/article/details/7548113

转载请标明来源

ITPub博客

Oralce数据库中比较两张表的不同点

最新文章