有没有好的方法来验证升级或迁移后数据库架构是否正确？
我们的客户正在从一种数据库版本升级到另一种数据库版本（具体来说，从 Oracle 9i 升级到 Oracle 10g 或 11g）。在一种情况下，客户导出旧数据库并将其导入新数据库，但由于某种原因未创建索引和约束。他们可能是故意这样做的，以加快导入过程，但我们仍在调查原因。
真正的问题是，有没有一种简单的方法可以验证导入后数据库的结构是否完整？我们可以对结构进行某种校验和吗？我们意识到我们可以执行一堆查询来查看所有表、索引、别名、视图、序列等是否存在，但这可能很难编写和维护。
更新
感谢您建议使用商业和/或 GUI 工具的答案，但我们确实需要一些可以与我们的产品打包在一起的免费工具。它还必须是命令行或脚本驱动的，以便我们的客户可以在任何环境（unix、linux、windows）中运行它。
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.
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).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
假设单个模式，类似这样 - 在迁移之前将 USER_OBJECTS 转储到表中。
然后，为了在迁移后进行验证，
一个问题是，如果您故意在版本之间删除对象，则还需要从 SAVED_USER_OBJECTS 中删除这些对象。如果存在错误版本的对象，这也不会出现。
如果您有多个模式，则每个模式都需要相同的东西，或者使用 ALL_OBJECTS 并提取/比较相关的用户模式。
您还可以对整个模式的 object_type||object_name 进行哈希/校验和（之前保存/之后比较），但计算成本与比较索引上的两个表没有太大不同。
Presuming a single schema, something like this - dump USER_OBJECTS into a table before migration.
Then to validate after your migration
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.
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.
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.
如果您愿意花一些钱，DBDiff 是一个高效的实用程序，它完全可以满足您的需求。
http://www.dkgas.com/oradbdiff.htm
If you are willing to spend some, DBDiff is an efficient utility that does exactly what you need.
http://www.dkgas.com/oradbdiff.htm
在 SQL DEVELOPER（免费的 Oracle 实用程序）中，有一个数据库架构差异功能。
值得一试。
希望有帮助。
SQL Developer - 下载
Roni。
In SQL DEVELOPER (the free Oracle utility) there is a Database Schema Differences feature.
It's worth to try it.
Hope it helps.
SQL Developer - download
Roni.
我不会编写检查脚本，我会编写一个程序来从特定版本的数据库生成检查脚本。只需检查元数据并记录其中的内容并将其写入文件，然后将该文件中的值与客户数据库中的值进行比较。如果您使用系统生成的名称作为约束，则这不会很好地工作，但仅验证事物是否存在可能就足够了。迁移数据库时删除索引和约束非常常见，因此您甚至可能不需要检查太多；如果缺少两三件东西，那么假设它们全部都丢失并不是没有道理的。您可能还想编写一个脚本来删除所有约束和索引并重新创建它们，然后让您的客户将其作为迁移后步骤运行。请确保按名称删除所有内容，这样就不会删除客户可能创建的任何自定义索引。
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.