Comparison and analysis scripts between different Oracle databases

The Oracle tutorial is the comparison and analysis script between different Oracle databases.

Oracle Database development applications often have such requirements for database administrators. We can compare the differences between objects in a certain mode between two different instances or compare the differences defined in the following table in a certain mode of two different instances, this is often encountered in the development and application of database software. Generally, database software is developed first on the development database. After development to a certain extent, the system is put into operation, and the software is in the maintenance stage. For errors and bugs encountered during system operation, as well as application system upgrades, it is often necessary to adjust the backgroundProgramDatabase developers often encounter such an embarrassing situation. during the maintenance period, there are some differences between the Development Library and the running library. What are these differences. Another case is that if the application is used in many places at the same time, the database is upgraded each time, and those databases are not upgraded yet, if there is no detailed record, it will also cause us embarrassment.

In addition, if you want to build an ORACLE Advanced table replication environment, after planning the replication architecture and the copied table, before formally adding the table to the replication group, an important task is to check the differences between the tables to be copied on the master node and the Master defined node. If there are any minor differences between these table definitions, for example, if a field allows null on one node and not null on another node, this error occurs when you add a table to a replication group, "ORA-23309 object string. string of Type string exists ", in fact, the cause of this error is that there are some differences in the table definitions between the replication master definition node and the master node. The system considers it to be two different tables, however, the two databases have the same name. If there are many tables to be copied, it takes a lot of work to manually compare the two nodes to copy the table, and it is not necessarily accurate.

Embarcadero (www.embarcadero.com), an industry-renowned provider of database software assistance products, provides a product called Change Manager in its full range of database solutions, A major purpose of this product is to compare and analyze the differences between different instances. Oracle Enterprise Manager also provides a tool called DB-diff to compare the differences between two databases.

In fact, the tools and software mentioned above are not required for general database development to some extent. Moreover, these products are not available and used at will. To address these problems mentioned above, two SQL scripts are provided to easily compare and analyze the differences between objects and table definitions between two different database instances.

1. Comparison of object differences in a mode between two different instances

Before running the following script, you must first create the database connection between the database user and the two instances to be compared. The following is an example of running the script. Run the script in SQL/plus with the following prompt:

Object owner (mode): repadmin

Database connection name of the first instance (including @): @ ora_zs

Database connection name of the first instance (including @): @ ora_sjjk

[Nextpage]

The program requires the input object owner, that is, compare the user, here is the repadmin user. Then there is the name of the instance to be compared. Here is ora_zs and ora_sjjk, and a comparison report is provided.

Appendix: PL/SQL script list:

Ii. Comparison of the following table definitions of two different models

The running method is the same as that of the first script. The following is a running instance result:

Object owner (mode): db_zgxt

Database connection name of the first instance (including @): @ ora_cx

Database connection name of the first instance (including @): @ ora_zs

Previous Page