Oracle compare and analyze scripts between different databases

The ORACLE tutorial you are looking at is :Oracle compare and analyze scripts between different databases.

In the development of Oracle database applications, database administrators are often required to compare the differences of objects under a certain schema between two different instances or the differences of table definitions under a certain schema between two different instances. This is often encountered in the development and application of database software. 1 general database software development is carried out on the development of the database first, development to a certain degree, the system is put into operation, at this time the software is in the maintenance stage. In view of the errors encountered in the system operation, bug, and the upgrade of the application system, often need to adjust the background procedures, database developers often encounter such an embarrassing thing, maintenance to 1 fixed period, the development library and runtime between 1 some differences, and what are these differences. Another situation is that if the application is used in many places at the same time, each background upgrade, those databases have been upgraded, those have not been upgraded, if there is no detailed record, it will cause us embarrassing things.

In addition, if you are setting up Oracle's high-level table replication environment, it is important to check the differences between the master node and the master definition node of the tables that need to be replicated before formally adding the tables that need to be replicated to the replication group, after you have planned the replication architecture and the tables that need to be replicated. If there is any subtle differences between the table definition, such as a field in a node is to allow Null, on the other 1 node is Not Null, when copying table to join the group will appear such errors, "ORA - 23309 object string. string of type string exists", in fact, the cause of this error is copying the definition between nodes and the master node table some differences, the definition of system is considered to be two different tables, But it has the same name in both databases. If many tables need to be replicated, manually comparing the differences between two node replication tables is a lot of work and not always accurate.

Embarcadero (www.embarcadero.com), a well-known provider of database software assistance products, provides a product called Change Manager in its complete set of database solutions. One of the major purposes of this product is to compare and analyze the differences between different instances. The Oracle OEM (oracle enterprise manager) package also includes a tool called DB-Diff, which is also used to compare the differences between the two databases.

In fact, these tools and software are not needed for normal database development to some extent, and these products are not freely available and used. To address these issues mentioned above, two SQL scripts are provided that make it easy to compare and analyze the differences in objects and table definitions between two different database instances.

1. Compare the object differences in a pattern under two different instances

Before you can run the following script, you must first create a database join between the database user and the two instances that you want to compare. Here is an example of running the script. Run the script under SQL/PLUS, and the following prompt appears:

Object owner (schema): repadmin

The database join name for the first instance (including @): @ora_zs

The database join name of the first instance (including @): @ora_sjjk

[NextPage]

Where the program asks the input object to belong to the host, that is, to the user, this is the repadmin user. Then the instance join names to be compared, ora_zs and ora_sjjk, and finally the comparison report.

Attached: PL/SQL script list:

2. Compare the differences in table definitions between two different schemas

The run method is similar to the first script, which I won't go into here. Here is the result of a running instance:

Object owner (schema): db_zgxt

The database join name for the first instance (including @): @ora_cx

The database join name of the first instance (including @): @ora_zs

On 1 page