Verifying a schema image

You might want to verify that the schema image matches the data definitions in the Oracle database. For example, if you delete the Customer table from the Oracle database, but not from the schema image, the Verify utility reports that there is an orphaned object. You can verify the schema information from a single table or from multiple tables, and then choose to update the table or tables so that the schema information matches the Oracle definitions.

The verify utility reads the definitions in the Oracle database and compares them to the information in the schema image. It reports the differences it finds and the degree of their severity. These are the categories of differences and how they impact your database applications:

  • Minor — These differences have no impact on the usability of your application.
  • Retained — The Update utility cannot correct these differences, hence the term "retained." You must determine how severely they impact your application and change the data definitions either in the schema holder using the Data Dictionary or in the Oracle database.
  • Severe — These differences might cause your application to malfunction. When the Verify utility detects severe differences, it automatically updates the schema image to resolve the discrepancies. It adjusts the information in the schema image to match the Oracle definitions. Severe differences in definitions that the DataServer uses internally also cause the schema image to be updated.

The following table lists the differences that the utility detects.

Table 1. Verify utility report
Database object Difference Category
Table Description Retained
Table Foreign type Severe
Table Name in OpenEdge Retained
Table Package name Severe
Table ROWID index Retained
Index Active Minor
Index Description Retained
Index Name in OpenEdge Retained
Index Unique Retained1
Index field Abbreviated Minor
Index field Ascending Severe
Index field Order Severe
Field Case-sensitivity Retained
Field Decimals Retained2
Field Description Retained
Field Extent Severe
Field Initial value Retained2
Field Mandatory Retained
Field Name in OpenEdge Retained
Field Order Retained
Field OpenEdge data type Retained
Field OpenEdge format Retained2
Field Shadow-column Name Severe

To verify a table:

  1. Choose DataServer > ORACLE Utilities > Verify Table Definition. The utility will ask you to verify that you are connected with a User ID and Password combination that has sufficient privileges. If the current User ID is correct, click OK. Otherwise enter a different User ID and Password combination.
  2. Once you are properly connected to Oracle, the following dialog box appears:
  3. Preselect the Oracle objects that the utility uses to update the schema image. By default, the wild card symbol (*) appears in the fill-in fields. The wild cards specify that the utility will use all objects in the Oracle database, including system catalog information. You can change the criteria by typing new information in the fill-in fields.
  4. By default, the utility verifies objects in the schema holder that match objects in the Oracle database. To check whether there are objects in Oracle that are not represented in the schema holder, deselect the Verify only objects that currently exist in the schema holder toggle box.
    Note: Deselecting the Verify only objects . . . toggle box and leaving the Object_Name and Object_Type as wild cards can cause this function to take a long time to complete if your Oracle database is large.
  5. There is a 32K limit on the contents of the verify report. Select Output differences to file if you anticipate a large volume of differences, or wish to save the report. The file that is produced is named <ldbname>+.vfy and is written to the working directory.
  6. Choose OK. The Select ORACLE Objects dialog box lists the objects and table information that you preselected:
  7. Select the objects you want to update, then choose OK.
  8. To select tables by matching a pattern, choose the Select Some button. The following dialog box appears:
  9. Type the pattern that you want to match, then choose OK to start the verification. If you did not choose to output the differences to a file, the following dialog box appears, listing the objects and the results of the verification:


  10. In Windows, the report window will display automatically. (If there are no differences, to report, a message confirms this situation.) When reading the text of the report, SH indicates the value in the schema image; NS indicates the value in the Oracle database.
    Note: On UNIX, choose the View Reports button to view a description of the differences found.

    For example:



  11. Chose Close to return to the Schema Verify dialog box.
  12. The utility automatically selects objects with severe differences for updating. You can select or deselect all other objects as you wish.

    You must resolve retained differences manually. Retained differences appear in reports until you resolve them.

  13. Choose OK to start the update or Cancel to quit the utility without updating the schema image.
1 When you update an index, the index is flagged as unique if it was defined as unique in either the Oracle database or the schema image.
2 If the corresponding information in the Oracle database is incompatible with the information in the schema holder, the affected fields are not updated. For example, if the Oracle data type is NUMBER and the OpenEdge data type is CHARACTER, the data type information is not updated.