oracle里对两个schema进行比较

工作中常常要对两台不同DB服务器的schema进行比较。比如测试机器和本番机器。

比较内容有各个表的字段的长度，属性，还有index等。

采取的办法是执行下面三个sql文，把DB情报生成出文件，然后对文件进行diff处理。

 

1. 抽出各个表的字段情报。

set echo off;
set linesize 30000;
set pagesize 32767;
set trimspool on;
set autocommit off;
set exitcommit off;
set null NULL;
COLUMN DATA_TYPE FORMAT A20;

spool columns.txt;

SELECT TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  DATA_LENGTH,
  CHAR_LENGTH,
  DATA_PRECISION,
  DATA_SCALE,
  NULLABLE,
  COLUMN_ID
FROM USER_TAB_COLUMNS
ORDER BY TABLE_NAME,
  COLUMN_ID;

spool off;

 

2. 抽出index相关情报。

set echo off;
set linesize 30000;
set pagesize 32767;
set trimspool on;
set autocommit off;
set exitcommit off;
set null NULL;

spool indexes.txt;

SELECT TABLE_NAME,
  INDEX_NAME,
  INDEX_TYPE,
  UNIQUENESS FROM USER_INDEXES
ORDER BY TABLE_NAME,
  INDEX_NAME;

spool off;

 

3. 抽出index各个字段的情报。

set echo off;
set linesize 30000;
set pagesize 32767;
set trimspool on;
set autocommit off;
set exitcommit off;
set null NULL;
COLUMN COLUMN_NAME FORMAT A40;

spool index_columns.txt;

SELECT TABLE_NAME,
  INDEX_NAME,
  TRIM(COLUMN_NAME) COLUMN_NAME,
  COLUMN_POSITION,
  DESCEND
FROM User_Ind_Columns
WHERE TABLE_NAME NOT LIKE 'BIN%'
ORDER BY TABLE_NAME,
  INDEX_NAME,
  COLUMN_POSITION;

spool off;