比较两个模式并使用新模式的新列更新旧模式 [英] Compare two schemas and update the old schema with the new columns of new schema
问题描述
我有一个有两个模式的Oracle数据库。一个是旧的，另一个是新的。我想用新模式的新列更新旧模式。
I've an Oracle database with two schemas. One is old and another is new. I would like to update the old schema with the new columns of the new schema.
我通过以下查询获得具有更改的表。
I get the tables which have changes with the following query.
select distinct table_name
from
(
select table_name,column_name
from all_tab_cols
where owner = 'SCHEMA_1'
minus
select table_name,column_name
from all_tab_cols
where owner = 'SCHEMA_2'
)
使用此查询我获得表。如何使用新列更新旧的模式表？我不需要数据，只是列。
With this query I get the tables. How can I update the old schema tables with the new columns? I don't need the data, just the columns.
推荐答案
模式比较工具是一个好主意。数据库模式比大多数人更加复杂，两个数据库模式之间的每一个差异都有可能导致错误。
A schema comparison tool is a good idea. The database schema is far more complicated than most people give credit, and every difference between two database schemas has the potential to cause bugs.
如果你还想做它自己，我发现的最好的方法是将模式定义提取到文本，然后运行文本比较。只要一切都按字母顺序排序，您就可以使用Microsoft Word（或FC.EXE，DIFF或等效文件）中的比较文档功能来突出显示差异。
If you're still keen to do it yourself, the best approach I've found is to extract the schema definitions to text, then run a text compare. As long as everything is sorted alphabetically, you can then use Compare Documents feature in Microsoft Word (or FC.EXE, DIFF or equivalent), to highlight the differences.
以下SQLPlus脚本按字母顺序输出模式定义，以允许比较。有两个部分。第一部分以以下格式列出每个列：
The following SQLPlus script outputs the schema definition alphabetically, to allow comparison. There are two sections. The first section lists each column, in the format:
table_name.column_name: data_type = data_default <nullable>
第二部分列出了索引和约束，如下所示：
The second section lists indexes and constraints, as follows:
PK constraint_name on table_name (pk_column_list)
FK constraint_name on table_name (fk_column_list)
CHECK constraint_name on table_name (constraint_definition)
脚本作为提取一些Oracle模式详细信息的有用参考。当您在客户端网站，您没有可用的常用工具，或者当安全策略阻止您直接从您自己的PC访问客户端网站数据库时，这是很好的知识。
The script serves as a useful references for extracting some of the Oracle schema details. This can be good knowledge to have when you're out at client sites and you don't have your usual tools available, or when security policies prevent you from accessing a client site database directly from your own PC.
set serveroutput on;
set serveroutput on size 1000000;
declare
rowcnt pls_integer := 0;
cursor c_column is
select table_name, column_name, data_type,
data_precision, data_length, data_scale,
data_default, nullable,
decode(data_scale, null, null, ',') scale_comma,
decode(default_length, null, null, '= ') default_equals
from all_tab_columns where owner = 'BCC'
order by table_name, column_name;
cursor c_constraint is
select c.table_name, c.constraint_name,
decode(c.constraint_type,
'P','PK',
'R','FK',
'C','CHECK',
c.constraint_type) constraint_type,
c.search_condition,
cc.column_1||cc.comma_2||cc.column_2||cc.comma_3||cc.column_3||cc.comma_4||cc.column_4||
cc.comma_5||cc.column_5||cc.comma_6||cc.column_6||cc.comma_7||cc.column_7 r_columns
from all_constraints c,
( select owner, table_name, constraint_name, nvl(max(position),0) max_position,
max( decode( position, 1, column_name, null ) ) column_1,
max( decode( position, 2, decode(column_name, null, null, ',' ), null ) ) comma_2,
max( decode( position, 2, column_name, null ) ) column_2,
max( decode( position, 3, decode(column_name, null, null, ',' ), null ) ) comma_3,
max( decode( position, 3, column_name, null ) ) column_3,
max( decode( position, 4, decode(column_name, null, null, ',' ), null ) ) comma_4,
max( decode( position, 4, column_name, null ) ) column_4,
max( decode( position, 5, decode(column_name, null, null, ',' ), null ) ) comma_5,
max( decode( position, 5, column_name, null ) ) column_5,
max( decode( position, 6, decode(column_name, null, null, ',' ), null ) ) comma_6,
max( decode( position, 6, column_name, null ) ) column_6,
max( decode( position, 7, decode(column_name, null, null, ',' ), null ) ) comma_7,
max( decode( position, 7, column_name, null ) ) column_7
from all_cons_columns
group by owner, table_name, constraint_name ) cc
where c.owner = 'BCC'
and c.generated != 'GENERATED NAME'
and cc.owner = c.owner
and cc.table_name = c.table_name
and cc.constraint_name = c.constraint_name
order by c.table_name,
decode(c.constraint_type,
'P','PK',
'R','FK',
'C','CHECK',
c.constraint_type) desc,
c.constraint_name;
begin
for c_columnRow in c_column loop
dbms_output.put_line(substr(c_columnRow.table_name||'.'||c_columnRow.column_name||': '||
c_columnRow.data_type||'('||
nvl(c_columnRow.data_precision, c_columnRow.data_length)||
c_columnRow.scale_comma||c_columnRow.data_scale||') '||
c_columnRow.default_equals||c_columnRow.data_default||
' <'||c_columnRow.nullable||'>',1,255));
rowcnt := rowcnt + 1;
end loop;
for c_constraintRow in c_constraint loop
dbms_output.put_line(substr(c_constraintRow.constraint_type||' '||c_constraintRow.constraint_name||' on '||
c_constraintRow.table_name||' ('||
c_constraintRow.search_condition||
c_constraintRow.r_columns||') ',1,255));
if length(c_constraintRow.constraint_type||' '||c_constraintRow.constraint_name||' on '||
c_constraintRow.table_name||' ('||
c_constraintRow.search_condition||
c_constraintRow.r_columns||') ') > 255 then
dbms_output.put_line('... '||substr(c_constraintRow.constraint_type||' '||c_constraintRow.constraint_name||' on '||
c_constraintRow.table_name||' ('||
c_constraintRow.search_condition||
c_constraintRow.r_columns||') ',256,251));
end if;
rowcnt := rowcnt + 1;
end loop;
end;
/
$ b
Unfortunately, there are a few limitations:
- 在data_defaults中嵌入的回车符和空格，以及检查约束定义，可能会突出显示为差异，即使它们对模式没有影响。
- 不包括备用键，唯一索引或性能索引。这将需要脚本中的第三个SELECT语句，引用all_ind_columns和all_indexes目录视图。
- 不包括安全详细信息，同义词，包，触发器等。包和触发器将是最好的比较使用类似于您最初提出的方法。
- 上面的FK定义标识引用外键列，但不标识PK或正在引用的表。
- Embedded carriage returns and whitespace in data_defaults, and check constraint definitions, may be highlighted as differences, even though they have zero effect on the schema.
- Does not include alternate keys, unique indexes or performance indexes. This would require a third SELECT statement in the script, referencing all_ind_columns and all_indexes catalog views.
- Does not include security details, synonyms, packages, triggers, etc. Packages and triggers would be best compared using an approach similar to the one you originally proposed. Other aspects of the schema definition could be added to the above script.
- The FK definitions above identify the referencing foreign key columns, but not the PK or the table being referenced. Just one more detail I never got around to doing.
即使你不使用脚本。有一些技术人员在玩这个东西的乐趣。 ; - ）
Even if you don't use the script. There's a certain techie pleasure in playing with this stuff. ;-)
马修
这篇关于比较两个模式并使用新模式的新列更新旧模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
