oracle表结构比对,oracle 比较两个用户表结构的区别。

create table ESPACE_TABLE

(

TABLE_NAME VARCHAR2(100) not null

)

create table ESPACE_COLUMN

(

TABLE_NAME VARCHAR2(100) not null,

COLUMN_NAME VARCHAR2(100) not null

)

create table UC_TABLE_DIFFERENT

(

S_TABLE_NAME VARCHAR2(30),

B_TABLE_NAME VARCHAR2(30),

S_COLUMN_NAME VARCHAR2(30),

B_COLUMN_NAME VARCHAR2(30),

S_DATA_TYPE VARCHAR2(106),

B_DATA_TYPE VARCHAR2(106),

S_DATA_LENGTH NUMBER,

B_DATA_LENGTH NUMBER,

S_DATA_PRECISION NUMBER,

B_DATA_PRECISION NUMBER,

S_DATA_SCALE NUMBER,

B_DATA_SCALE NUMBER,

S_NULLABLE VARCHAR2(1),

B_NULLABLE VARCHAR2(1),

S_DATA_DEFAULT VARCHAR2(4000),

B_DATA_DEFAULT VARCHAR2(4000)

)

create table SDU_TABLES

(

TABLE_NAME VARCHAR2(30) not null,

COLUMN_NAME VARCHAR2(30) not null,

DATA_TYPE VARCHAR2(106),

DATA_LENGTH NUMBER not null,

DATA_PRECISION NUMBER,

DATA_SCALE NUMBER,

NULLABLE VARCHAR2(1),

DATA_DEFAULT VARCHAR2(4000)

)

create table UC_TABLE_DIFFERENT

(

S_TABLE_NAME VARCHAR2(30),

B_TABLE_NAME VARCHAR2(30),

S_COLUMN_NAME VARCHAR2(30),

B_COLUMN_NAME VARCHAR2(30),

S_DATA_TYPE VARCHAR2(106),

B_DATA_TYPE VARCHAR2(106),

S_DATA_LENGTH NUMBER,

B_DATA_LENGTH NUMBER,

S_DATA_PRECISION NUMBER,

B_DATA_PRECISION NUMBER,

S_DATA_SCALE NUMBER,

B_DATA_SCALE NUMBER,

S_NULLABLE VARCHAR2(1),

B_NULLABLE VARCHAR2(1),

S_DATA_DEFAULT VARCHAR2(4000),

B_DATA_DEFAULT VARCHAR2(4000)

)

CREATE OR REPLACE PROCEDURE COMPARE_UC_TABLES

AS

BEGIN

execute immediate 'delete from sdu_tables';

execute immediate 'delete from bmp_tables';

execute immediate 'delete from uc_table_different';

execute immediate 'insert into sdu_tables select

A.Table_Name,

A.column_name ,A.data_type ,A.data_length ,A.data_precision ,

A.Data_Scale ,A.nullable ,Default_value(a.OWNER,a.Table_Name,a.column_name) as Data_default

from

dba_tab_columns A

where

A.owner=''SDU'' and SUBSTR(TABLE_NAME,1,4) != ''BIN$''';

execute immediate 'insert into bmp_tables select

A.Table_Name,

A.column_name ,A.data_type ,A.data_length ,A.data_precision ,

A.Data_Scale ,A.nullable ,Default_value(a.OWNER,a.Table_Name,a.column_name) as Data_default

from

dba_tab_columns A

where

A.owner=''BMP'' and SUBSTR(TABLE_NAME,1,4) != ''BIN$''';

DELETE FROM sdu_tables WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM ESPACE_TABLE);

DELETE FROM bmp_tables WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM ESPACE_TABLE);

DELETE FROM sdu_tables S WHERE EXISTS (SELECT 1 FROM ESPACE_COLUMN E WHERE E.TABLE_NAME = S.TABLE_NAME AND E.COLUMN_NAME = S.COLUMN_NAME);

DELETE FROM bmp_tables B WHERE EXISTS (SELECT 1 FROM ESPACE_COLUMN E WHERE E.TABLE_NAME = B.TABLE_NAME AND E.COLUMN_NAME = B.COLUMN_NAME);

commit;

execute immediate 'insert into uc_table_different select *

from (select s.Table_Name s_Table_Name,

b.Table_Name b_Table_Name,

s.column_name s_column_name,

b.column_name b_column_name,

s.data_type s_data_type,

b.data_type b_data_type,

s.data_length s_data_length,

b.data_length b_data_length,

s.data_precision s_data_precision,

b.data_precision b_data_precision,

s.Data_Scale s_Data_Scale,

b.Data_Scale b_Data_Scale,

s.nullable s_nullable,

b.nullable b_nullable,

s.Data_default s_Data_default,

b.Data_default b_Data_default

from sdu_tables s

full join bmp_tables b on s.Table_Name = b.Table_Name

and s.column_name = b.column_name)

where s_column_name is null

or b_column_name is null

or s_data_type != b_data_type

or s_data_length != b_data_length

or s_data_precision != b_data_precision

or s_Data_Scale != b_Data_Scale

or s_nullable != b_nullable

or s_Data_default != b_Data_default';

commit;

END COMPARE_UC_TABLES;

begin

compare_uc_tables;

end;

/

select * from uc_table_different ;