We have customers who are upgrading from one database version to another (Oracle 9i to Oracle 10g or 11g to be specific). In one case, a customer exported the old database and imported it into the new one, but for some reason the indexes and constraints didn't get created. They may have done this on purpose to speed up the import process, but we're still looking into the reason why.
我们的客户正在从一个数据库版本升级到另一个数据库版本(Oracle 9i到Oracle 10g或11g是特定的)。在一种情况下,客户导出旧数据库并将其导入新数据库,但由于某种原因,未创建索引和约束。他们可能是故意这样做的,以加快进口过程,但我们仍在调查原因。
The real question is, is there a simple way that we can verify that the structure of the database is complete after the import? Is there some sort of checksum that we can do on the structure? We realize that we could do a bunch of queries to see if all the tables, indexes, aliases, views, sequences, etc. exist, but this would probably be difficult to write and maintain.
真正的问题是,有一种简单的方法可以在导入后验证数据库的结构是否完整?我们可以在结构上做某种校验和吗?我们意识到我们可以做一堆查询来查看是否存在所有表,索引,别名,视图,序列等,但这可能很难编写和维护。
Update
Thanks for the answers suggesting commercial and/or GUI tools to use, but we really need something free that we could package with our product. It also has to be command line or script driven so our customers can run it in any environment (unix, linux, windows).
感谢建议使用商业和/或GUI工具的答案,但我们真的需要免费的东西,我们可以用我们的产品包装。它还必须是命令行或脚本驱动,以便我们的客户可以在任何环境(unix,linux,windows)中运行它。
4 个解决方案
#1
3
Presuming a single schema, something like this - dump USER_OBJECTS into a table before migration.
假定单个模式,类似这样 - 在迁移之前将USER_OBJECTS转储到表中。
CREATE TABLE SAVED_USER_OBJECTS AS SELECT * FROM USER_OBJECTS
Then to validate after your migration
然后在迁移后进行验证
SELECT object_type, object_name FROM SAVED_USER_OBJECTS
MINUS
SELECT object_type, object_name FROM USER_OBJECTS
One issue is if you have intentionally dropped objects between versions you will also need to delete the from SAVED_USER_OBJECTS. Also this will not pick up if the wrong version of objects exist.
一个问题是,如果您故意在版本之间删除对象,则还需要从SAVED_USER_OBJECTS中删除它。如果存在错误版本的对象,也不会发现这种情况。
If you have multiple schemas, then the same thing is required for each schema OR use ALL_OBJECTS and extract/compare for the relevant user schemas.
如果您有多个模式,那么每个模式都需要相同的东西,或者使用ALL_OBJECTS并提取/比较相关的用户模式。
You could also do a hash/checksum on object_type||object_name for the whole schema (save before/compare after) but the cost of calculation wouldn't be that different from comparing the two tables on indexes.
您还可以在object_type || object_name上为整个模式执行哈希/校验和(保存之前/之后比较),但计算成本与比较索引上的两个表没有什么不同。
#2
1
If you are willing to spend some, DBDiff is an efficient utility that does exactly what you need.
如果您愿意花一些钱,DBDiff是一个高效的实用工具,可以满足您的需求。
#3
1
In SQL DEVELOPER (the free Oracle utility) there is a Database Schema Differences feature. It's worth to try it.
在SQL DEVELOPER(免费的Oracle实用程序)中,有一个数据库模式差异功能。值得尝试一下。
Hope it helps.
希望能帮助到你。
SQL Developer - 下载
Roni.
#4
0
I wouldn't write the check script, I'd write a program to generate the check script from a particular version of the database. Just go though the metatdata and record what's there and write it to a file, then compare the values in that file against the values in the customer's database. This won't work so well if you use system-generated names for your constraints, but it is probably enough to just verify that things are there. Dropping indexes and constraints is pretty common when migrating a database, so you might not even need to check too much; if two or three things are missing, then it's not unreasonable to assume they all are. You might also want to write a script that drops all the constraints and indexes and re-creates them, and just have your customers run that as a post-migration step. Just be sure you drop everything by name, so you don't delete any custom indexes your customer might have created.
我不会编写检查脚本,我会编写一个程序来从特定版本的数据库生成检查脚本。只需查看元数据并记录其中的内容并将其写入文件,然后将该文件中的值与客户数据库中的值进行比较。如果您为约束使用系统生成的名称,这将无法正常工作,但它可能只是验证事情是否存在。在迁移数据库时,删除索引和约束是很常见的,因此您甚至可能不需要检查太多;如果缺少两三件事,那么假设他们都是不合理的并不是不合理的。您可能还希望编写一个删除所有约束和索引并重新创建它们的脚本,然后让您的客户将其作为迁移后步骤运行。请确保按名称删除所有内容,这样就不会删除客户可能创建的任何自定义索引。
#1
3
Presuming a single schema, something like this - dump USER_OBJECTS into a table before migration.
假定单个模式,类似这样 - 在迁移之前将USER_OBJECTS转储到表中。
CREATE TABLE SAVED_USER_OBJECTS AS SELECT * FROM USER_OBJECTS
Then to validate after your migration
然后在迁移后进行验证
SELECT object_type, object_name FROM SAVED_USER_OBJECTS
MINUS
SELECT object_type, object_name FROM USER_OBJECTS
One issue is if you have intentionally dropped objects between versions you will also need to delete the from SAVED_USER_OBJECTS. Also this will not pick up if the wrong version of objects exist.
一个问题是,如果您故意在版本之间删除对象,则还需要从SAVED_USER_OBJECTS中删除它。如果存在错误版本的对象,也不会发现这种情况。
If you have multiple schemas, then the same thing is required for each schema OR use ALL_OBJECTS and extract/compare for the relevant user schemas.
如果您有多个模式,那么每个模式都需要相同的东西,或者使用ALL_OBJECTS并提取/比较相关的用户模式。
You could also do a hash/checksum on object_type||object_name for the whole schema (save before/compare after) but the cost of calculation wouldn't be that different from comparing the two tables on indexes.
您还可以在object_type || object_name上为整个模式执行哈希/校验和(保存之前/之后比较),但计算成本与比较索引上的两个表没有什么不同。
#2
1
If you are willing to spend some, DBDiff is an efficient utility that does exactly what you need.
如果您愿意花一些钱,DBDiff是一个高效的实用工具,可以满足您的需求。
#3
1
In SQL DEVELOPER (the free Oracle utility) there is a Database Schema Differences feature. It's worth to try it.
在SQL DEVELOPER(免费的Oracle实用程序)中,有一个数据库模式差异功能。值得尝试一下。
Hope it helps.
希望能帮助到你。
SQL Developer - 下载
Roni.
#4
0
I wouldn't write the check script, I'd write a program to generate the check script from a particular version of the database. Just go though the metatdata and record what's there and write it to a file, then compare the values in that file against the values in the customer's database. This won't work so well if you use system-generated names for your constraints, but it is probably enough to just verify that things are there. Dropping indexes and constraints is pretty common when migrating a database, so you might not even need to check too much; if two or three things are missing, then it's not unreasonable to assume they all are. You might also want to write a script that drops all the constraints and indexes and re-creates them, and just have your customers run that as a post-migration step. Just be sure you drop everything by name, so you don't delete any custom indexes your customer might have created.
我不会编写检查脚本,我会编写一个程序来从特定版本的数据库生成检查脚本。只需查看元数据并记录其中的内容并将其写入文件,然后将该文件中的值与客户数据库中的值进行比较。如果您为约束使用系统生成的名称,这将无法正常工作,但它可能只是验证事情是否存在。在迁移数据库时,删除索引和约束是很常见的,因此您甚至可能不需要检查太多;如果缺少两三件事,那么假设他们都是不合理的并不是不合理的。您可能还希望编写一个删除所有约束和索引并重新创建它们的脚本,然后让您的客户将其作为迁移后步骤运行。请确保按名称删除所有内容,这样就不会删除客户可能创建的任何自定义索引。