ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oralce数据库中比较两张表的不同点

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

原创 Linux操作系统 作者:xiaohou2006 时间:2012-05-08 23:56:44 0 删除 编辑
 我们经常会遇到这样的问题:比较两张表的结构是否相同。比如: 

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

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

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


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

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

1、表的数据结构不同:

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

  
  1. SELECT DECODE(SUM(flag),-1,'TEST3','TEST2') Table_name,col_ind_name,  
  2.        col_ind_type,null_pressed,cols  
  3. FROM (  
  4. SELECT atc.COLUMN_NAME AS col_ind_name,  
  5.     CASE WHEN data_type = 'DATE' THEN 'DATE'  
  6.       WHEN data_type = 'NUMBER' AND DATA_precision IS NULL AND data_scale IS NULL THEN 'NUMBER'  
  7.       WHEN data_type = 'NUMBER' AND DATA_precision IS NULL AND data_scale = 0 THEN 'INTEGER'  
  8.       WHEN data_type = 'NUMBER' AND DATA_precision IS NOT NULL  
  9.           AND data_scale IS NOT NULL THEN 'NUMBER('||DATA_precision||','||data_scale||')'            
  10.       WHEN data_type = 'NUMBER' AND DATA_precision IS NOT NULL   
  11.           AND data_scale IS NULL THEN 'NUMBER('||DATA_precision||')'  
  12.       WHEN data_type = 'VARCHAR2' AND char_used = 'B' THEN 'VARCHAR2('||char_length||')'     
  13.       WHEN data_type = 'VARCHAR2' AND char_used = 'C' THEN 'VARCHAR2('||char_length||' CHAR)'     
  14.       WHEN data_type = 'CHAR' AND char_used = 'B' THEN 'CHAR('||char_length||')'    
  15.       WHEN data_type = 'CHAR' AND char_used = 'C' THEN 'CHAR('||char_length||' CHAR)'  
  16.       END col_ind_type,  
  17.       decode(atc.NULLABLE,'N','No','Y','Yes',atc.NULLABLE) AS null_pressed,  
  18.       acc.COMMENTS AS cols   
  19.       ,  -1 AS flag  
  20.  FROM All_Tab_Columns atc,all_col_comments acc  
  21.  WHERE atc.TABLE_NAME = acc.TABLE_NAME  
  22.    AND atc.COLUMN_NAME = acc.COLUMN_NAME  
  23.    AND atc.TABLE_NAME = 'TEST2'  
  24.  UNION ALL   
  25. SELECT atc.COLUMN_NAME AS col_ind_name,  
  26.     CASE WHEN data_type = 'DATE' THEN 'DATE'  
  27.       WHEN data_type = 'NUMBER' AND DATA_precision IS NULL AND data_scale IS NULL THEN 'NUMBER'  
  28.       WHEN data_type = 'NUMBER' AND DATA_precision IS NULL AND data_scale = 0 THEN 'INTEGER'  
  29.       WHEN data_type = 'NUMBER' AND DATA_precision IS NOT NULL  
  30.           AND data_scale IS NOT NULL THEN 'NUMBER('||DATA_precision||','||data_scale||')'            
  31.       WHEN data_type = 'NUMBER' AND DATA_precision IS NOT NULL   
  32.           AND data_scale IS NULL THEN 'NUMBER('||DATA_precision||')'  
  33.       WHEN data_type = 'VARCHAR2' AND char_used = 'B' THEN 'VARCHAR2('||char_length||')'     
  34.       WHEN data_type = 'VARCHAR2' AND char_used = 'C' THEN 'VARCHAR2('||char_length||' CHAR)'     
  35.       WHEN data_type = 'CHAR' AND char_used = 'B' THEN 'CHAR('||char_length||')'    
  36.       WHEN data_type = 'CHAR' AND char_used = 'C' THEN 'CHAR('||char_length||' CHAR)'  
  37.       END col_ind_type,  
  38.       decode(atc.NULLABLE,'N','No','Y','Yes',atc.NULLABLE) AS null_pressed,  
  39.       acc.COMMENTS AS cols  
  40.       ,  -1 AS flag  
  41.  FROM All_Tab_Columns atc,all_col_comments acc  
  42.  WHERE atc.TABLE_NAME = acc.TABLE_NAME  
  43.    AND atc.COLUMN_NAME = acc.COLUMN_NAME  
  44.    AND atc.TABLE_NAME = 'TEST3' )  
  45.  GROUP BY col_ind_name,col_ind_type,null_pressed,cols  
  46.  HAVING SUM(flag) <> 0  


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

  1. ----------- 查找比较两张表的索引  
  2. SELECT DECODE(SUM(flag),-1,'TEST3','TEST2') Table_name,   
  3.        col_ind_name,col_ind_type,null_pressed,owner,cols,ind_Tablespace  
  4. FROM (  
  5. SELECT ai.index_name AS col_ind_name,  
  6.        CASE WHEN ai.UNIQUENESS = 'NONUNIQUE' AND ai.index_type = 'NORMAL' THEN 'Normal'  
  7.             WHEN ai.UNIQUENESS = 'UNIQUE' AND ai.index_type = 'NORMAL' THEN 'Unique'  
  8.             WHEN ai.UNIQUENESS = 'NONUNIQUE' AND ai.index_type = 'BITMAP' THEN 'Bitmap'  
  9.        ELSE ai.UNIQUENESS||'-'||ai.index_type END AS col_ind_type ,  
  10.        decode(ai.COMPRESSION,'DISABLED','No','ABLED','Yes',ai.COMPRESSION) AS null_pressed,  
  11.        ai.owner ,  
  12.        T_INDEX_COLUMNS.INDEX_COLUMNS AS cols,  
  13.        ai.TABLESPACE_NAME AS ind_Tablespace  
  14.        ,1 AS flag  
  15.   FROM all_indexes ai ,  
  16.        (SELECT INDEX_NAME, MAX(COLS) INDEX_COLUMNS  
  17.            FROM (SELECT AIC.INDEX_NAME,  
  18.             WMSYS.WM_CONCAT(AIC.COLUMN_NAME) OVER(PARTITION BY AIC.INDEX_NAME ORDER BY AIC.COLUMN_POSITION) COLS  
  19.              FROM ALL_IND_COLUMNS AIC  
  20.             WHERE TABLE_NAME = 'TEST2') AAA  
  21.        GROUP BY INDEX_NAME  
  22.        ) T_INDEX_COLUMNS  
  23. WHERE ai.INDEX_NAME = T_INDEX_COLUMNS.INDEX_NAME    
  24. UNION ALL  
  25. SELECT ai.index_name AS col_ind_name,  
  26.        CASE WHEN ai.UNIQUENESS = 'NONUNIQUE' AND ai.index_type = 'NORMAL' THEN 'Normal'  
  27.             WHEN ai.UNIQUENESS = 'UNIQUE' AND ai.index_type = 'NORMAL' THEN 'Unique'  
  28.             WHEN ai.UNIQUENESS = 'NONUNIQUE' AND ai.index_type = 'BITMAP' THEN 'Bitmap'  
  29.        ELSE ai.UNIQUENESS||'-'||ai.index_type  END AS col_ind_type ,  
  30.        decode(ai.COMPRESSION,'DISABLED','No','ABLED','Yes',ai.COMPRESSION) AS null_pressed,   
  31.        ai.owner ,  
  32.        T_INDEX_COLUMNS.INDEX_COLUMNS AS cols,  
  33.        ai.TABLESPACE_NAME AS ind_Tablespace  
  34.        ,-1 AS flag  
  35.   FROM all_indexes ai ,  
  36.        (SELECT INDEX_NAME, MAX(COLS) INDEX_COLUMNS  
  37.            FROM (SELECT AIC.INDEX_NAME,  
  38.             WMSYS.WM_CONCAT(AIC.COLUMN_NAME) OVER(PARTITION BY AIC.INDEX_NAME ORDER BY AIC.COLUMN_POSITION) COLS  
  39.              FROM ALL_IND_COLUMNS AIC  
  40.             WHERE TABLE_NAME = 'TEST3') AAA  
  41.        GROUP BY INDEX_NAME  
  42.        ) T_INDEX_COLUMNS  
  43. WHERE ai.INDEX_NAME = T_INDEX_COLUMNS.INDEX_NAME  )  
  44. GROUP BY col_ind_name,col_ind_type,null_pressed,owner,cols,ind_Tablespace   
  45. HAVING SUM(flag) <> 0 


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

转载请标明来源



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24556933/viewspace-723092/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 十一本书
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-10-26

  • 博文量
    3
  • 访问量
    3106