Oracle数据库测试挑战-比较架构数据 [英] Oracle Database Testing Challenge - Compare Schema Data

问题描述

想知道是否有人可以帮助我指出正确的方向来应对这一挑战，或者告诉我我为通过sql尝试而疯狂.如果sql太过挑战，是否有任何免费或廉价的工具可以帮助我实现自动化?

Wondering if someone can help point me in the right direction with this challenge, or tell me I'm crazy for trying this via sql. If sql would be too challenging, are there any free or inexpensive tools that would help me automate this?

我正在测试新旧Oracle数据库之间的一些数据.我想做的是能够为架构中的所有表动态生成此查询.

I'm working on testing some data between an old and new Oracle database. What I'd like to do is be able to dynamically generate this query for all tables in a schema.

Select Column_1, Column_2 FROM Table_1
MINUS
Select Column_1, Column_2 FROM Table_1@"OLD_SERVER"

一个要注意的是，为每个表选择的列应仅是不以'ETL'开头的列，因为这些列预计会随迁移而改变.

One catch is that the columns selected for each table should only be columns that do not begin with 'ETL' since those are expected to change with the migration.

要保持这种动态状态，我可以使用all_tab_columns遍历每个表吗?

To keep this dynamic, can I use the all_tab_columns to loop through each table?

因此，对于一个简化的示例，假设此查询返回了以下结果，并且您可以期望OLD和NEW数据库中ALL_TAB_COLUMNS的结果相同:

So for a simplified example, let's say this query returned the following results, and you can expect the results from ALL_TAB_COLUMNS to be identical between the OLD and NEW database:

select TABLE_NAME, COLUMN_NAME from ALL_TAB_COLUMNS where owner = 'OWNER1'

TABLE_NAME, COLUMN_NAME
-----------------------
TABLE1, COLUMN_1
TABLE1, COLUMN_2
TABLE1, ETLCOLUMN_3

TABLE2, COLUMN_A
TABLE2, COLUMN_B
TABLE2, ETLCOLUMN_C

我将如何编写一个查询，该查询将在旧数据库和新数据库上的相同表和列(不以ETL开头)之间运行减号，并输出结果以及表名和运行日期，以及然后循环到下一张表并执行相同的操作?

How would I write a query that would run a minus between the same table and columns (that do not begin with ETL) on the old and new database, and output the results along with the table name and the date ran, and then loop through to the next table and do the same thing?

推荐答案

首先-检查一下: http://docs.oracle.com/cd/E11882_01 /server.112/e41481/spa_upgrade.htm#RATUG210

第二个-您想编写一个发出查询的查询-问题是在user_tab_columns中每一列都是一行. 为此，建议您阅读以下内容: http://www.dba-oracle.com/t_converting_rows_columns .htm
您的源表是USER_TAB_COLUMNS，在运行查询时，您可以添加一个地方，上面写着其中column_name不像'ETL％'等. 之后-查询将类似于:
选择选择"

Second - you would like to write a query that issues a query - The problem is that in user_tab_columns each column is a row. for doing that I would recommend you reading this : http://www.dba-oracle.com/t_converting_rows_columns.htm
The source table for you is USER_TAB_COLUMNS, and when running the query you can add a where that says "where column_name not like 'ETL%' etc. After that - the query would look something like:
select 'select '

 || listagg..... (from the link) || 'from table name' sql 
from user_tab_columns
where column_name not like 'ETL%'
and table_name = 'table name'
group by table_name

而且顺便说一句-您并不疯狂-在更改系统之前，您需要能够签署升级才能成功-这是唯一的方法.

and btw - you're not crazy - before changing a system you need to be able to sign the upgrade will succeed - this is the only way to do it.

顺便说一句-如果您将更深入地描述系统和升级-我相信社区将能够帮助您找到更深入地测试系统的方法，并向您指出要测试的内容.
在许多情况下，仅测试输出是不够的....

btw - if you'll describe in more depth the system and the upgrade - I'm sure the community will be able to help you find ways to test it in more depth, and will point you out to things to test.
Testing only the output is not enough in many cases....

祝你好运！

这篇关于Oracle数据库测试挑战-比较架构数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！