Schema

Database objects are identified by schema and name. Name check is case-insensitive, so for example table myTable vs MyTable is considered as the same table.

All objects are built by reading object properties from different system metadata tables and views. Functions, Procedures and Sequences also use DBMS_METADATA.GET_DDL function to retrieve object definitions. Application supports the most commonly used object attributes. However in many cases it is only the subset of Oracle database specifications, and the application does not support some Oracle database language features. Such features are not recognized and if an object has changes in any non-supported attribute then such change is ignored. In case of object merge these attributes can be lost in the target database. Below you will find the information about supported/unsupported features.

The Pro version also allows you to compare results of DBMS_METADATA.GET_DDL calls, this can be useful if you want to check how a database server generates table scripts. There is an appropriate button in the Table structure diff tab. For any other object (function, etc.) you can compare DBMS_METADATA.GET_DDL results using the Query result diff tab.

Schemas

Oracle database schemas are essentially the same thing as users. Since it is not possible to extract user password and replicate CREATE USER statement to create the same user with the same password, the application currently generates just a dummy CREATE SCHEMA SQL statement for schema definitions. Obviously that's not good and it is a subject of further improvements (at least needs to be replaced with CREATE USER). Object list shows schema names and user creation time. List of schemas is compared but can't be synchronized and marked with DIFF ONLY on the home tab.

Table definitions

Here is the high-level presentation of supported/unsupported table definition attributes, based on Oracle CREATE TABLE and constraint specifications. Note that this definition does not include inline_constraint because the application generates scripts for constraints as out_of_line_constraint script parts. Unsupported ones are shown as ~~crossed out~~:

CREATE
  [ SHARDED | DUPLICATED | [ IMMUTABLE ] BLOCKCHAIN | IMMUTABLE  ]
  TABLE schema.table
  [ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ]
  {
      relational_table ::= 
      [ ( relational_properties ::=
          column_definition ::= column
            [ datatype [ COLLATE column_collation_name ] ]
            [ SORT ]
            [ VISIBLE | INVISIBLE ]
            [
                DEFAULT [ ON NULL ] expr
              | identity_clause ::=
                GENERATED
                [ ALWAYS | BY DEFAULT [ ON NULL ] ]
                AS IDENTITY
                [ ( identity_options ) ]
            ]
            [ ENCRYPT encryption_spec ]
            [ inline_ref_constraint ]
        | virtual_column_definition ::= column
          [ datatype [ COLLATE column_collation_name ] ]
          [ VISIBLE | INVISIBLE ]
          [ GENERATED ALWAYS ]
          AS ( column_expression )
          [ VIRTUAL ]
          [ evaluation_edition_clause ]
          [ unusable_editions_clause ]
        | period_definition ::= PERIOD FOR valid_time_column [ ( start_time_column, end_time_column )]
        | out_of_line_constraint
        | out_of_line_ref_constraint 
        | supplemental_logging_props 
        [, ...] ) ]
      [ immutable_table_clauses ]
      [ DEFAULT COLLATION collation_name ]
      [ ON COMMIT { DROP | PRESERVE DEFINITION } ]
      [ ON COMMIT { DELETE | PRESERVE ROWS } ]
      [ physical_properties ::= 
      {
          [ deferred_segment_creation ] segment_attributes_clause [ table_compression ] [ inmemory_table_clause ] [ ilm_clause ]
        | [ deferred_segment_creation ]
          {
            ORGANIZATION { HEAP ... | INDEX ... | EXTERNAL ... }
          | EXTERNAL PARTITION ATTRIBUTES ...
          }
        | CLUSTER ...
      }]
      [ table_properties ]
    | object_table
    | XMLType_table
  }
  [ MEMOPTIMIZE FOR READ ]
  [ MEMOPTIMIZE FOR WRITE ]
  [ PARENT [ schema. ] table ]
    
out_of_line_constraint ::=
  [ CONSTRAINT constraint_name ]
  {
      {
          UNIQUE ( column [, ...] )
        | PRIMARY KEY ( column [, ...] )
        | FOREIGN KEY ( column [, ...] ) references_clause
      }
      constraint_state
    | CHECK ( condition ) constraint_state precheck_state

Indexes

Currently Indexes are compared only as part of table definitions. Index changes can be ignored for table definition if needed, depending on application settings. That's the another subject of further improvements, we plan to allow us to compare indexes as a separate object type.

Here is the high-level presentation of supported/unsupported index attributes, based on Oracle CREATE INDEX specification. Unsupported ones are shown as ~~crossed out~~:

CREATE [ UNIQUE | BITMAP ] INDEX schema.index ON
  [
      cluster_index_clause
    | table_index_clause ::= schema.table
      ( index_expr ::= { column | column_expression }
        [ ASC | DESC ] [, ...] ) 
      index_properties
    | bitmap_join_index_clause
  ]
  [ USABLE | UNUSABLE ]
  [ [ DEFERRED | IMMEDIATE ] INVALIDATION ]

Views

Here is the presentation of supported/unsupported view features, based on Oracle CREATE VIEW specification. Unsupported ones are shown as ~~crossed out~~:

CREATE [ [ NO ] FORCE ] [ EDITIONING | EDITIONABLE .. | NONEDITIONABLE ] VIEW schema.view
  [ SHARING .. ]
  [ {
      ( { alias .. | out_of_line_constraint } [, ... ] )
    | object_view_clause
    | XMLType_view_clause
  } ]
  [ DEFAULT COLLATION collation_name ]
  [ BEQUEATH .. ]
  [ annotations_clause ]
  AS subquery
  subquery_restriction_clause
  [ CONTAINER_MAP | CONTAINERS_DEFAULT ]

Last updated: 2023-10-06