Is there a good way to verify that the database schema is correct after an upgrade or migration?

Question

We have clients who switch from one version of the database to another (for Oracle 9i - Oracle 10g or 11g). In one case, a client exported an old database and imported it into a new one, but for some reason indexes and restrictions were not created. They may have done this specifically to speed up the import process, but we are still exploring the reason.

The real question is: is there an easy way to ensure that the database structure is complete after import? Is there some kind of checksum we can do in the structure? We understand that we can do a bunch of queries to see if all tables, indexes, aliases, views, sequences, etc. exist, but it will probably be difficult to write and maintain.

Update

Thanks for the answers suggesting using commercial and / or graphical tools, but we really need something free that we can pack with our product. It should also be a command line or script, so our clients can run it in any environment (unix, linux, windows).

Answer 1

Assuming one schema, something like this, unload USER_OBJECTS into a table before the transfer.

 CREATE TABLE SAVED_USER_OBJECTS AS SELECT * FROM USER_OBJECTS

Then for confirmation after migration

 SELECT object_type, object_name FROM SAVED_USER_OBJECTS
 MINUS
 SELECT object_type, object_name FROM USER_OBJECTS

One of the problems is that you intentionally deleted objects between versions, you also need to remove from SAVED_USER_OBJECTS. Also, it will not be visible if the wrong version of the objects exists.

If you have several schemes, then each scheme requires the same OR OR use ALL_OBJECTS and extract / compare for the corresponding user schemes.

You can also make a hash / checksum for object_type || object_name for the whole schema (save before / compare after), but the cost of the calculation will not differ from comparing two tables in the indices.

Answer 2

, DBDiff - , , .

http://www.dkgas.com/oradbdiff.htm

Answer 3

SQL DEVELOPER ( Oracle) " ". .

, .

SQL Developer -

.

Answer 4

script, script . , , , . , , , , , . , ; , , . script, , . , , - , .