Comparison and analysis scripts for different Oracle databases _oracle

The Oracle tutorial you are looking at is: Oracle contrast analysis scripts between different databases.

Oracle database development applications often have such a need for database administrators, compared to two different instances of the object in a pattern of differences or comparisons between two different instances of the table definition of the difference, which is involved in the development of database software applications are often encountered. General database software development is first in the development of the database, developed to a certain extent, the system into operation, at this time the software in the maintenance phase. For the operation of the system encountered errors, bugs, as well as the application system upgrades, often need to adjust the background program, database developers often encounter such an embarrassing thing, maintenance to a certain period of time, the development of the library and the runtime a number of differences, and what these differences are. Another situation is that if the application is used in many places at the same time, every time the background upgrade, those databases have been upgraded, those have not been upgraded, if not detailed records, will also cause our embarrassing things.

In addition, if you want to build Oracle's Advanced table replication environment, after you have planned the replicated schemas and replicated tables, it is important to check the differences between the primary and primary defined nodes before you formally add the tables that need to be replicated to the replication group. If there is any slight difference between these table definitions, such as when a field is allowed null on one node and not NULL on the other, this error occurs when a table is added to a replication group, "ORA-23309 object string.string of type String exists ", the reason for this error is that there are some differences between the definitions of the tables in the Copy Master definition node and the master node, which are considered to be two different tables, but have the same name in two databases. If you need to replicate a lot of tables, it is much more work and not necessarily accurate to manually compare the differences between replicated tables for two nodes.

The industry-renowned database software helper product provider, USA Embarcadero (www.embarcadero.com), provides a product called change manager in its complete set of database solutions. A great use of the product is to compare and analyze the differences between different instances. The Oracle Company's OEM (Oracle Enterprise Manager) package also provides a tool called Db-diff, which is also used to compare differences between two databases.

In fact, for ordinary database development to some extent, there is no need for the above mentioned tool software, and these products are not easily available and used. For these issues mentioned above, two SQL scripts are provided for easy comparison and analysis of the differences in objects between two different database instances and table definitions.

Comparison of object differences in a pattern under two different instances

Before you run the following script, you must first create a database join between the ex-database user and the two instances that need to be compared. Here is an example of running the script. Running the script under Sql/plus, the system appears as follows:

Object owner (mode): repadmin

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

Database join name for the first instance (including @): @ora_sjjk

[NextPage]

Where the program requires the input object is the owner, that is, the user, this is the repadmin user. Then is the instance join name that needs to be contrasted, here is Ora_zs and ORA_SJJK, and finally gives a comparison report.

Attached: Pl/sql program script list:

The difference between the table definitions in the two different modes

The Run method is the same as the first script, which is no longer detailed here. The following is a running instance result:

Object owner (Mode): DB_ZGXT

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

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

Previous page