Compare application database created in SQL Server and Oracle

Question

I looked around for this task I have on my hands but did not find anything helpful. I am primarily a Java person with sound knowledge of database from software development point of view. I do have some knowledge of DBA functions with what can and cannot be done but not able to come up with a good solution.

The task I have is to compare the databases created in SQL Server and Oracle by our application installer.

I think I have been able to come up with some queries (of course, by searching online) in SQL Server that will give me things like number of tables in a schema, each table's columns with data types and indexes, different types of constraints, triggers, etc. (with their count) created for each of those tables. I can provide those SQLs if somebody is interested. However, Oracle seem to be more tricky. I would appreciate if somebody can help or maybe point me in the right direction.

I am trying to find out somethings like following:

1. Number of tables created
2. Number of indexes, constraints (with their types), triggers for each of those tables
3. Number of stored procedures/functions created
4. Number of views created

Any help will be greatly appreciated.

Thank you.

Answer 1

First off, if you are already comfortable writing Java code, I'm not sure that I would be writing a bunch of SQL to do this comparison. JDBC already has a DatabaseMetaData class that has methods like getTables to get all the tables. That would give you one API to work with and let you leverage the fact that the folks that wrote the JDBC drivers already wrote all the code to query the data dictionary tables in whatever database you are using. This will also let you focus on differences in how the objects your installer creates will be perceived by the application.

If you are going to write specific SQL, the Oracle data dictionary tables are pretty easy to work with. The ones you'll care about are going to follow the pattern [user|all|dba]_<<type of thing>>. The [user|all|dba] prefix indicates whether you are looking for objects that you own (user), objects that you have access to (all), or all objects in the database (dba). Normal users often don't have access to the dba views because that is a potential security issue-- generally you don't want people to know that an object exists if they don't have access to it. In my examples, I'll use the all versions of the objects but you can change all to user or dba depending on what you're after.

• all_tables will show you information about all the tables you have access to. You probably want to add a filter on owner for the schema(s) that your installer touches since you may have access to tables that are not part of your application.
• all_indexes, all_constraints, and all_triggers will show you information about indexes, constraints, and triggers. Again, you may want to add a predicate on owner to limit yourself to the schema(s) that you care about.
• all_procedures will show you information about procedures and functions both stand-alone and in packages.
• all_views will show you information about all views.

If you are really just interested in counts, you may be able to simply go to all_objects and do a count grouping by object_type. I'm guessing that you'll want to see attributes of the various objects so you'll want to go to the various object-specific views.