oracle 比较两个模式并使用新模式的新列更新旧模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2332617/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare two schemas and update the old schema with the new columns of new schema
提问by coder247
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.
我有一个带有两个模式的 Oracle 数据库。一个是旧的，另一个是新的。我想用新架构的新列更新旧架构。
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.
通过这个查询，我得到了表格。如何使用新列更新旧架构表？我不需要数据，只需要列。
回答by Matthew Eyles
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.
模式比较工具是个好主意。数据库模式比大多数人认为的要复杂得多，两个数据库模式之间的每一个差异都有可能导致错误。
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.
如果您仍然热衷于自己动手，我发现的最佳方法是将模式定义提取到文本中，然后运行文本比较。只要所有内容都按字母顺序排序，您就可以使用 Microsoft Word（或 FC.EXE、DIFF 或等效工具）中的比较文档功能来突出显示差异。
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:
以下 SQLPlus 脚本按字母顺序输出模式定义，以便进行比较。有两个部分。第一部分列出了每一列，格式如下：
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)
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.
该脚本可作为提取某些 Oracle 模式详细信息的有用参考。当您在客户端站点并且您没有可用的常用工具时，或者当安全策略阻止您直接从您自己的 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;
/
Unfortunately, there are a few limitations:
不幸的是，有一些限制：
- 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.
- data_defaults 中嵌入的回车和空格以及检查约束定义可能会突出显示为差异，即使它们对模式的影响为零。
- 不包括备用键、唯一索引或性能索引。这将需要脚本中的第三个 SELECT 语句，引用 all_ind_columns 和 all_indexes 目录视图。
- 不包括安全细节、同义词、包、触发器等。包和触发器最好使用与您最初建议的方法类似的方法进行比较。模式定义的其他方面可以添加到上述脚本中。
- 上面的 FK 定义标识引用外键列，但不标识 PK 或被引用的表。还有一个我从来没有做过的细节。
Even if you don't use the script. There's a certain techie pleasure in playing with this stuff. ;-)
即使你不使用脚本。玩这些东西有一定的技术乐趣。;-)
Matthew
马修
回答by Peter Lang
I'm afraid I can't do more for you at the moment, but this should give you a basic idea.
恐怕我现在不能为你做更多的事情，但这应该给你一个基本的想法。
It selects ADDand DROPcolumn statements that you could execute after carefully reviewing them.
它选择ADD和DROP列语句，您可以在仔细查看它们之后执行它们。
It does not handle
它不处理
- created/dropped tables
- data type/precision changes of existing columns (
ALTER TABLE MODIFY) DEFAULT VALUES(so you can't apply it on a table with data when new column isNOT NULL)- Check constraints, Foreign Key constraints
- 创建/删除表
- 现有列的数据类型/精度变化 (
ALTER TABLE MODIFY) DEFAULT VALUES（因此当新列是 时，您不能将其应用于带有数据的表NOT NULL）- 检查约束，外键约束
I tried it with some basic data-types (NUMBER, VARCHAR2, DATE) and it worked. Good luck :)
我用一些基本的数据类型 ( NUMBER, VARCHAR2, DATE)尝试了它并且它起作用了。祝你好运 ：）
SELECT 'ALTER TABLE ' || LOWER(table_name)
|| ' ADD ' || LOWER(column_name) || ' ' || data_type
|| CASE WHEN data_type NOT IN ('DATE') THEN '(' || data_length || ')' END
|| CASE WHEN nullable='Y' THEN ' NOT NULL' END
|| ';' cmd
FROM all_tab_cols c2
WHERE owner = 'SCHEMA_1'
AND NOT EXISTS ( SELECT 1
FROM all_tab_cols c1
WHERE owner = 'SCHEMA_2'
AND c1.table_name = c2.table_name
AND c1.column_name = c2.column_name )
UNION ALL
SELECT 'ALTER TABLE ' || LOWER(table_name)
|| ' DROP COLUMN ' || LOWER(column_name) || ';'
FROM all_tab_cols c2
WHERE owner = 'SCHEMA_2'
AND NOT EXISTS ( SELECT 1
FROM all_tab_cols c1
WHERE owner = 'SCHEMA_1'
AND c1.table_name = c2.table_name
AND c1.column_name = c2.column_name )
ORDER BY cmd;
回答by Nick Pierpoint
I started writing an answer for this but my list of caveats became longer than the answer so I decided to scrap it.
我开始为此写一个答案，但我的警告列表变得比答案长，所以我决定放弃它。
You should go for a schema comparison tool.
您应该使用模式比较工具。
There are free versions available - take a look at this question on Server Fault:
有可用的免费版本 - 在 Server Fault 上查看这个问题：
https://serverfault.com/questions/26360/how-can-i-diff-two-oracle-10g-schemas
https://serverfault.com/questions/26360/how-can-i-diff-two-oracle-10g-schemas
My suggestion would be to download Oracle's SQL Developer and use the built-in schema diff tool (although this requires that you have the Change Management Pack license).
我的建议是下载 Oracle 的 SQL Developer 并使用内置的模式差异工具（尽管这需要您拥有 Change Management Pack 许可）。
