Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Using SQLcl & Liquibase DIFF command to compare schemas

11 Mins Read

Oracle SQL Developer has a ‘Database Diff’ Wizard. You can select a source and target connection, types of objects to compare, and we show you:

  • what’s different
  • what the code would be to get the differences sorted
Step 1 of Tools – Database Diff wizard

Once it’s done running, you’ll get this output:

Note there is no actual text report.

Fun fact: this code actually comes from Enterprise Manager. It remains a no-cost feature of SQL Developer, but all the output comes from EM sources.

There not being a convenient report raises comments like this from my friend on YouTube –

Hello Jeff, do we have a report of 2 database schema comparison? Currently I take screenshot between source and target DB Objects and is really tiresome

Roshan Raikar 

The answer is “no, but maybe yes?”

Hence the title of today’s post.

Liquibase’s open source project includes a ‘DIFF’ command. And SQLcl ships an Oracle-enhanced version of this. We ‘expose’ or surface the ‘DIFF’ command in SQLcl, so that means you can take advantage of this Liquibase feature!

Liquibase DIFF Docs

Liquibase returns the following types of differences:

  • Missing objects (in source but not in target)
  • Unexpected objects (in target but not in source)
  • Changed objects (things found in both, but with differences)

Liquibase’s Open Source project includes support for these types of objects:

  • Catalog
  • Column
  • Foreign Key
  • Index
  • Primary Key
  • Schema
  • Sequence
  • Unique Constraints
  • View

Let’s do a compare!

First, we need to investigate the DIFF command –

lb help diff

And the help but with the -examples flag –

lb help diff -ex

Most important note: you CONNECT to the target. And in the command, you include the connection details for the REFERENCE database.

I have two schemas in one database.

I’m going to connect to HRREST, and I’m going to see how it differs from my HR schema, in the same database.

lb diff -reference-url jdbc:oracle:thin:@localhost:1521/orcl -reference-username hr -reference-password oracle

Now I’m not spooling the output to a file. You’ll WANT to do that, because these reports are quite long and they might exhaust the buffer you have set in your terminal.

The trickiest part? Figuring out just what your ‘JDBC URL’ is.

Thankfully, SQLcl can help! Simply run ‘show connection’ –

jdbc:oracle:thin:@localhost:1521/orcl — ‘orcl’ is the SERVICE NAME, not the SID.

Here’s my actual report:

Reference Database: HRREST @ jdbc:oracle:thin:@localhost:1521/orcl (Default Schema: HRREST)
Comparison Database: HRREST_TEST @ jdbc:oracle:thin:@localhost:1521/orcl (Default Schema: HRREST_TEST)
Compared Schemas: HRREST -> HRREST_TEST
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): NONE
Missing Column(s):
     HRREST.HRREST_HR2_EXPORT_JOB_1.ABORT_STEP
     HRREST.HRREST_HR2_EXPORT_JOB_1.ACCESS_METHOD
     HRREST.REDDIT_SURVEY.ADVISE_TO_YOUNGER_SELF
     HRREST.REDDIT_SURVEY.AGE
     HRREST.ITUNES.ALBUM
     HRREST.HRREST_HR2_EXPORT_JOB_1.ANCESTOR_OBJECT_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.ANCESTOR_OBJECT_SCHEMA
     HRREST.HRREST_HR2_EXPORT_JOB_1.ANCESTOR_OBJECT_TYPE
     HRREST.HRREST_HR2_EXPORT_JOB_1.ANCESTOR_PROCESS_ORDER
     HRREST.ITUNES.ARTIST
     HRREST.HRREST_HR2_EXPORT_JOB_1.BASE_OBJECT_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.BASE_OBJECT_SCHEMA
     HRREST.HRREST_HR2_EXPORT_JOB_1.BASE_OBJECT_TYPE
     HRREST.HRREST_HR2_EXPORT_JOB_1.BASE_PROCESS_ORDER
     HRREST.ITUNES.BIT_RATE
     HRREST.HRREST_HR2_EXPORT_JOB_1.BLOCK_SIZE
     HRREST.HRREST_HR2_EXPORT_JOB_1.CLUSTER_OK
     HRREST.REDDIT_SURVEY.COMFY_LANGS
     HRREST.ITUNES.COMMENTS
     HRREST.HRREST_HR2_EXPORT_JOB_1.COMPLETED_BYTES
     HRREST.HRREST_HR2_EXPORT_JOB_1.COMPLETED_ROWS
     HRREST.HRREST_HR2_EXPORT_JOB_1.COMPLETION_TIME
     HRREST.ITUNES.COMPOSER
     HRREST.REDDIT_SURVEY.CONCURRENT_PROJECTS
     HRREST.HRREST_HR2_EXPORT_JOB_1.CONTROL_QUEUE
     HRREST.HRREST_HR2_EXPORT_JOB_1.CREATION_LEVEL
     HRREST.HRREST_HR2_EXPORT_JOB_1.CREATION_TIME
     HRREST.HRREST_HR2_EXPORT_JOB_1.CUMULATIVE_TIME
     HRREST.REDDIT_SURVEY.CURRENT_THEME
     HRREST.REDDIT_SURVEY.DARK_OR_LIGHT_THEMES
     HRREST.HRREST_HR2_EXPORT_JOB_1.DATAOBJ_NUM
     HRREST.HRREST_HR2_EXPORT_JOB_1.DATA_BUFFER_SIZE
     HRREST.HRREST_HR2_EXPORT_JOB_1.DATA_IO
     HRREST.ITUNES.DATE_ADDED
     HRREST.ITUNES.DATE_MODIFIED
     HRREST.HRREST_HR2_EXPORT_JOB_1.DB_VERSION
     HRREST.HRREST_HR2_EXPORT_JOB_1.DEGREE
     HRREST.REDDIT_SURVEY.DISCORD_INTERACTIONS
     HRREST.ITUNES.DISC_COUNT
     HRREST.ITUNES.DISC_NUMBER
     HRREST.HRREST_HR2_EXPORT_JOB_1.DOMAIN_PROCESS_ORDER
     HRREST.HRREST_HR2_EXPORT_JOB_1.DUMP_ALLOCATION
     HRREST.HRREST_HR2_EXPORT_JOB_1.DUMP_FILEID
     HRREST.HRREST_HR2_EXPORT_JOB_1.DUMP_LENGTH
     HRREST.HRREST_HR2_EXPORT_JOB_1.DUMP_ORIG_LENGTH
     HRREST.HRREST_HR2_EXPORT_JOB_1.DUMP_POSITION
     HRREST.HRREST_HR2_EXPORT_JOB_1.DUPLICATE
     HRREST.HRREST_HR2_EXPORT_JOB_1.ELAPSED_TIME
     HRREST.ITUNES.EQUALIZER
     HRREST.HRREST_HR2_EXPORT_JOB_1.ERROR_COUNT
     HRREST.HRREST_HR2_EXPORT_JOB_1.EXTEND_SIZE
     HRREST.UNTAPPD.EXTRA_INFO
     HRREST.REDDIT_SURVEY.FAVE_TOOLS
     HRREST.REDDIT_SURVEY.FIELD
     HRREST.HRREST_HR2_EXPORT_JOB_1.FILE_MAX_SIZE
     HRREST.HRREST_HR2_EXPORT_JOB_1.FILE_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.FILE_TYPE
     HRREST.REDDIT_SURVEY.FIRST_LANG
     HRREST.REDDIT_SURVEY.FIZZBUZZ_CODE
     HRREST.HRREST_HR2_EXPORT_JOB_1.FLAGS
     HRREST.REDDIT_SURVEY.GENDER
     HRREST.ITUNES.GENRE
     HRREST.HRREST_HR2_EXPORT_JOB_1.GRANTOR
     HRREST.HRREST_HR2_EXPORT_JOB_1.GRANULES
     HRREST.ITUNES.GROUPING
     HRREST.HRREST_HR2_EXPORT_JOB_1.GUID
     HRREST.DEPARTMENTS.HEAD_COUNT
     HRREST.ITUNES.HOW_BIG
     HRREST.REDDIT_SURVEY.HTML_ESSAY
     HRREST.REDDIT_SURVEY.IDES
     HRREST.REDDIT_SURVEY.IMPOSTER_SYNDROME
     HRREST.HRREST_HR2_EXPORT_JOB_1.INSTANCE
     HRREST.HRREST_HR2_EXPORT_JOB_1.INSTANCE_ID
     HRREST.HRREST_HR2_EXPORT_JOB_1.IN_PROGRESS
     HRREST.HRREST_HR2_EXPORT_JOB_1.IS_DEFAULT
     HRREST.HRREST_HR2_EXPORT_JOB_1.JOB_MODE
     HRREST.HRREST_HR2_EXPORT_JOB_1.JOB_VERSION
     HRREST.ITUNES.KIND
     HRREST.HRREST_HR2_EXPORT_JOB_1.LAST_FILE
     HRREST.ITUNES.LAST_PLAYED
     HRREST.ITUNES.LAST_SKIPPED
     HRREST.HRREST_HR2_EXPORT_JOB_1.LAST_UPDATE
     HRREST.REDDIT_SURVEY.LEAST_FAVE_LANG
     HRREST.HRREST_HR2_EXPORT_JOB_1.LOAD_METHOD
     HRREST.ITUNES.LOCATION
     HRREST.HRREST_HR2_EXPORT_JOB_1.METADATA_BUFFER_SIZE
     HRREST.HRREST_HR2_EXPORT_JOB_1.METADATA_IO
     HRREST.ITUNES.MY_RATING
     HRREST.HRREST_HR2_EXPORT_JOB_1.NAME
     HRREST.ITUNES.NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_INT_OID
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_LONG_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_NUMBER
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_PATH_SEQNO
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_ROW
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_SCHEMA
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_TABLESPACE
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_TYPE
     HRREST.HRREST_HR2_EXPORT_JOB_1.OBJECT_TYPE_PATH
     HRREST.REDDIT_SURVEY.OCCUPATION
     HRREST.HRREST_HR2_EXPORT_JOB_1.OLD_VALUE
     HRREST.HRREST_HR2_EXPORT_JOB_1.OPERATION
     HRREST.HRREST_HR2_EXPORT_JOB_1.OPTION_TAG
     HRREST.HRREST_HR2_EXPORT_JOB_1.ORIGINAL_OBJECT_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.ORIGINAL_OBJECT_SCHEMA
     HRREST.HRREST_HR2_EXPORT_JOB_1.ORIG_BASE_OBJECT_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.ORIG_BASE_OBJECT_SCHEMA
     HRREST.REDDIT_SURVEY.OS_USED
     HRREST.HRREST_HR2_EXPORT_JOB_1.PACKET_NUMBER
     HRREST.HRREST_HR2_EXPORT_JOB_1.PARALLELIZATION
     HRREST.HRREST_HR2_EXPORT_JOB_1.PARENT_OBJECT_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.PARENT_OBJECT_SCHEMA
     HRREST.HRREST_HR2_EXPORT_JOB_1.PARENT_PROCESS_ORDER
     HRREST.HRREST_HR2_EXPORT_JOB_1.PARTITION_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.PHASE
     HRREST.HRREST_HR2_EXPORT_JOB_1.PLATFORM
     HRREST.ITUNES.PLAYS
     HRREST.EMPLOYEES.POTPOURRI
     HRREST.REDDIT_SURVEY.PREFERRED_LANG
     HRREST.REDDIT_SURVEY.PREF_BROWSER
     HRREST.REDDIT_SURVEY.PREF_FREE_LIC
     HRREST.REDDIT_SURVEY.PREF_LICENSE_MODEL
     HRREST.REDDIT_SURVEY.PREF_SHELL
     HRREST.REDDIT_SURVEY.PREF_TERMINAL
     HRREST.HRREST_HR2_EXPORT_JOB_1.PROCESSING_STATE
     HRREST.HRREST_HR2_EXPORT_JOB_1.PROCESSING_STATUS
     HRREST.HRREST_HR2_EXPORT_JOB_1.PROCESS_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.PROCESS_ORDER
     HRREST.REDDIT_SURVEY.PROGRAMMING_EXP
     HRREST.HRREST_HR2_EXPORT_JOB_1.PROPERTY
     HRREST.HRREST_HR2_EXPORT_JOB_1.PROXY_SCHEMA
     HRREST.HRREST_HR2_EXPORT_JOB_1.PROXY_VIEW
     HRREST.HRREST_HR2_EXPORT_JOB_1.QUEUE_TABNUM
     HRREST.REDDIT_SURVEY.REDDIT_INTERACTIONS
     HRREST.HRREST_HR2_EXPORT_JOB_1.REMOTE_LINK
     HRREST.DEPARTMENTS.RETENTION
     HRREST.REDDIT_SURVEY.R_PROGRAMMERHUMOR_MEMBER
     HRREST.ITUNES.SAMPLE_RATE
     HRREST.HRREST_HR2_EXPORT_JOB_1.SCN
     HRREST.HRREST_HR2_EXPORT_JOB_1.SEED
     HRREST.REDDIT_SURVEY.SELF_TAUGHT
     HRREST.HRREST_HR2_EXPORT_JOB_1.SERVICE_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.SIZE_ESTIMATE
     HRREST.ITUNES.SKIPS
     HRREST.HRREST_HR2_EXPORT_JOB_1.SRC_COMPAT
     HRREST.REDDIT_SURVEY.STACKOVERFLOW_FREQ
     HRREST.REDDIT_SURVEY.STACKOVERFLOW_USAGE
     HRREST.HRREST_HR2_EXPORT_JOB_1.START_TIME
     HRREST.HRREST_HR2_EXPORT_JOB_1.STATE
     HRREST.HRREST_HR2_EXPORT_JOB_1.STATUS_QUEUE
     HRREST.HRREST_HR2_EXPORT_JOB_1.SUBPARTITION_NAME
     HRREST.REDDIT_SURVEY.SURVEY_DATE
     HRREST.HRREST_HR2_EXPORT_JOB_1.TARGET_XML_CLOB
     HRREST.HRREST_HR2_EXPORT_JOB_1.TDE_REWRAPPED_KEY
     HRREST.HRREST_HR2_EXPORT_JOB_1.TEMPLATE_TABLE
     HRREST.ITUNES.TIME
     HRREST.HRREST_HR2_EXPORT_JOB_1.TIMEZONE
     HRREST.HRREST_HR2_EXPORT_JOB_1.TOTAL_BYTES
     HRREST.ITUNES.TRACK_COUNT
     HRREST.ITUNES.TRACK_NUMBER
     HRREST.HRREST_HR2_EXPORT_JOB_1.TRIGFLAG
     HRREST.HRREST_HR2_EXPORT_JOB_1.UNLOAD_METHOD
     HRREST.HRREST_HR2_EXPORT_JOB_1.USER_DIRECTORY
     HRREST.HRREST_HR2_EXPORT_JOB_1.USER_FILE_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.USER_NAME
     HRREST.HRREST_HR2_EXPORT_JOB_1.VALUE_N
     HRREST.HRREST_HR2_EXPORT_JOB_1.VALUE_T
     HRREST.HRREST_HR2_EXPORT_JOB_1.VERSION
     HRREST.ITUNES.VOLUME_ADJUSTMENT
     HRREST.HRREST_HR2_EXPORT_JOB_1.WORK_ITEM
     HRREST.HRREST_HR2_EXPORT_JOB_1.XML_CLOB
     HRREST.ITUNES.YEAR
Unexpected Column(s):
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ABORT_STEP
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ACCESS_METHOD
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ANCESTOR_OBJECT_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ANCESTOR_OBJECT_SCHEMA
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ANCESTOR_OBJECT_TYPE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ANCESTOR_PROCESS_ORDER
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.BASE_OBJECT_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.BASE_OBJECT_SCHEMA
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.BASE_OBJECT_TYPE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.BASE_PROCESS_ORDER
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.BLOCK_SIZE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.CLUSTER_OK
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.COMPLETED_BYTES
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.COMPLETED_ROWS
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.COMPLETION_TIME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.CONTROL_QUEUE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.CREATION_LEVEL
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.CREATION_TIME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.CUMULATIVE_TIME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DATAOBJ_NUM
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DATA_BUFFER_SIZE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DATA_IO
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DB_VERSION
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DEGREE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DOMAIN_PROCESS_ORDER
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DUMP_ALLOCATION
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DUMP_FILEID
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DUMP_LENGTH
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DUMP_ORIG_LENGTH
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DUMP_POSITION
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.DUPLICATE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ELAPSED_TIME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ERROR_COUNT
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.EXTEND_SIZE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.FILE_MAX_SIZE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.FILE_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.FILE_TYPE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.FLAGS
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.GRANTOR
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.GRANULES
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.GUID
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.INSTANCE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.INSTANCE_ID
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.IN_PROGRESS
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.IS_DEFAULT
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.JOB_MODE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.JOB_VERSION
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.LAST_FILE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.LAST_UPDATE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.LOAD_METHOD
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.METADATA_BUFFER_SIZE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.METADATA_IO
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_INT_OID
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_LONG_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_NUMBER
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_PATH_SEQNO
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_ROW
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_SCHEMA
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_TABLESPACE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_TYPE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OBJECT_TYPE_PATH
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OLD_VALUE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OPERATION
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.OPTION_TAG
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ORIGINAL_OBJECT_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ORIGINAL_OBJECT_SCHEMA
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ORIG_BASE_OBJECT_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.ORIG_BASE_OBJECT_SCHEMA
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PACKET_NUMBER
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PARALLELIZATION
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PARENT_OBJECT_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PARENT_OBJECT_SCHEMA
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PARENT_PROCESS_ORDER
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PARTITION_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PHASE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PLATFORM
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PROCESSING_STATE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PROCESSING_STATUS
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PROCESS_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PROCESS_ORDER
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PROPERTY
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PROXY_SCHEMA
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.PROXY_VIEW
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.QUEUE_TABNUM
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.REMOTE_LINK
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.SCN
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.SEED
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.SERVICE_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.SIZE_ESTIMATE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.SRC_COMPAT
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.START_TIME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.STATE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.STATUS_QUEUE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.SUBPARTITION_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.TARGET_XML_CLOB
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.TDE_REWRAPPED_KEY
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.TEMPLATE_TABLE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.TIMEZONE
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.TOTAL_BYTES
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.TRIGFLAG
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.UNLOAD_METHOD
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.USER_DIRECTORY
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.USER_FILE_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.USER_NAME
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.VALUE_N
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.VALUE_T
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.VERSION
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.WORK_ITEM
     HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1.XML_CLOB
Changed Column(s):
     HRREST.DATABASECHANGELOG_DETAILS.AUTHOR
          type changed from 'VARCHAR(255 BYTE)' to 'UNDEFINED(0)'
     HRREST.LOCATIONS.CITY
          remarks changed from 'A not null column that shows city where an office, warehouse, or
production site of a company is located.' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.COMMENTS
          type changed from 'VARCHAR(255 BYTE)' to 'UNDEFINED(0)'
     HRREST.EMPLOYEES.COMMISSION_PCT
          remarks changed from 'Commission percentage of the employee; Only employees in sales
department elgible for commission percentage' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.CONTEXTS
          type changed from 'VARCHAR(255 BYTE)' to 'UNDEFINED(0)'
     HRREST.COUNTRIES.COUNTRY_ID
          remarks changed from 'Primary key of countries table.' to 'null'
     HRREST.LOCATIONS.COUNTRY_ID
          remarks changed from 'Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.' to 'null'
     HRREST.COUNTRIES.COUNTRY_NAME
          remarks changed from 'Country name' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.DATEEXECUTED
          nullable changed from 'false' to 'true'
          type changed from 'TIMESTAMP(6)(11)' to 'UNDEFINED(0)'
     HRREST.DEPARTMENTS.DEPARTMENT_ID
          remarks changed from 'Primary key column of departments table.' to 'null'
     HRREST.EMPLOYEES.DEPARTMENT_ID
          remarks changed from 'Department id where employee works; foreign key to department_id
column of the departments table' to 'null'
     HRREST.JOB_HISTORY.DEPARTMENT_ID
          remarks changed from 'Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table' to 'null'
     HRREST.DEPARTMENTS.DEPARTMENT_NAME
          remarks changed from 'A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting.' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.DEPLOYMENT_ID
          type changed from 'VARCHAR(10 BYTE)' to 'UNDEFINED(0)'
     HRREST.DATABASECHANGELOG_DETAILS.DESCRIPTION
          type changed from 'VARCHAR(255 BYTE)' to 'UNDEFINED(0)'
     HRREST.EMPLOYEES.EMAIL
          remarks changed from 'Email id of the employee' to 'null'
     HRREST.EMPLOYEES.EMPLOYEE_ID
          remarks changed from 'Primary key of employees table.' to 'null'
     HRREST.JOB_HISTORY.EMPLOYEE_ID
          remarks changed from 'A not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table' to 'null'
     HRREST.JOB_HISTORY.END_DATE
          remarks changed from 'Last day of the employee in this job role. A not null column. Must be
greater than the start_date of the job_history table.
(enforced by constraint jhist_date_interval)' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.EXECTYPE
          nullable changed from 'false' to 'true'
          type changed from 'VARCHAR(10 BYTE)' to 'UNDEFINED(0)'
     HRREST.DATABASECHANGELOG_DETAILS.FILENAME
          type changed from 'VARCHAR(255 BYTE)' to 'UNDEFINED(0)'
     HRREST.EMPLOYEES.FIRST_NAME
          remarks changed from 'First name of the employee. A not null column.' to 'null'
     HRREST.EMPLOYEES.HIRE_DATE
          remarks changed from 'Date when the employee started on this job. A not null column.' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.ID
          type changed from 'VARCHAR(255 BYTE)' to 'UNDEFINED(0)'
     HRREST.EMPLOYEES.JOB_ID
          remarks changed from 'Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.' to 'null'
     HRREST.JOBS.JOB_ID
          remarks changed from 'Primary key of jobs table.' to 'null'
     HRREST.JOB_HISTORY.JOB_ID
          remarks changed from 'Job role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.' to 'null'
     HRREST.JOBS.JOB_TITLE
          remarks changed from 'A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.LABELS
          type changed from 'VARCHAR(255 BYTE)' to 'UNDEFINED(0)'
     HRREST.EMPLOYEES.LAST_NAME
          remarks changed from 'Last name of the employee. A not null column.' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.LIQUIBASE
          type changed from 'VARCHAR(20 BYTE)' to 'UNDEFINED(0)'
     HRREST.DEPARTMENTS.LOCATION_ID
          remarks changed from 'Location id where a department is located. Foreign key to location_id column of locations table.' to 'null'
     HRREST.LOCATIONS.LOCATION_ID
          remarks changed from 'Primary key of locations table' to 'null'
     HRREST.DEPARTMENTS.MANAGER_ID
          remarks changed from 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.' to 'null'
     HRREST.EMPLOYEES.MANAGER_ID
          remarks changed from 'Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)' to 'null'
     HRREST.JOBS.MAX_SALARY
          remarks changed from 'Maximum salary for a job title' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.MD5SUM
          type changed from 'VARCHAR(35 BYTE)' to 'UNDEFINED(0)'
     HRREST.JOBS.MIN_SALARY
          remarks changed from 'Minimum salary for a job title.' to 'null'
     HRREST.EMPLOYEES.PHONE_NUMBER
          remarks changed from 'Phone number of the employee; includes country code and area code' to 'null'
     HRREST.LOCATIONS.POSTAL_CODE
          remarks changed from 'Postal code of the location of an office, warehouse, or production site
of a company.' to 'null'
     HRREST.COUNTRIES.REGION_ID
          remarks changed from 'Region ID for the country. Foreign key to region_id column in the departments table.' to 'null'
     HRREST.REGIONS.REGION_ID
          remarks changed from 'Primary key of regions table.' to 'null'
     HRREST.REGIONS.REGION_NAME
          remarks changed from 'Names of regions. Locations are in the countries of these regions.' to 'null'
     HRREST.EMPLOYEES.SALARY
          remarks changed from 'Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.SQL
          type changed from 'CLOB' to 'UNDEFINED(0)'
     HRREST.JOB_HISTORY.START_DATE
          remarks changed from 'A not null column in the complex primary key employee_id+start_date.
Must be less than the end_date of the job_history table. (enforced by
constraint jhist_date_interval)' to 'null'
     HRREST.LOCATIONS.STATE_PROVINCE
          remarks changed from 'State or Province where an office, warehouse, or production site of a
company is located.' to 'null'
     HRREST.LOCATIONS.STREET_ADDRESS
          remarks changed from 'Street address of an office, warehouse, or production site of a company.
Contains building number and street name' to 'null'
     HRREST.DATABASECHANGELOG_DETAILS.SXML
          type changed from 'CLOB' to 'UNDEFINED(0)'
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s):
     SYS_MTABLE_000013F2E_IND_1 UNIQUE  ON HRREST.HRREST_HR2_EXPORT_JOB_1(PROCESS_ORDER, DUPLICATE)
     SYS_MTABLE_000013F2E_IND_2 ON HRREST.HRREST_HR2_EXPORT_JOB_1(OBJECT_SCHEMA, ORIGINAL_OBJECT_NAME, OBJECT_TYPE)
     SYS_MTABLE_000013F2E_IND_3 ON HRREST.HRREST_HR2_EXPORT_JOB_1(OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME)
     SYS_MTABLE_000013F2E_IND_4 ON HRREST.HRREST_HR2_EXPORT_JOB_1(BASE_PROCESS_ORDER)
     SYS_MTABLE_000013F2E_IND_5 ON HRREST.HRREST_HR2_EXPORT_JOB_1(ORIGINAL_OBJECT_SCHEMA, ORIGINAL_OBJECT_NAME, PARTITION_NAME)
     SYS_MTABLE_000013F2E_IND_6 ON HRREST.HRREST_HR2_EXPORT_JOB_1(SEED)
Unexpected Index(s):
     SYS_MTABLE_000013F2E_IND_1 UNIQUE  ON HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1(PROCESS_ORDER, DUPLICATE)
     SYS_MTABLE_000013F2E_IND_2 ON HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1(OBJECT_SCHEMA, ORIGINAL_OBJECT_NAME, OBJECT_TYPE)
     SYS_MTABLE_000013F2E_IND_3 ON HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1(OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME)
     SYS_MTABLE_000013F2E_IND_4 ON HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1(BASE_PROCESS_ORDER)
     SYS_MTABLE_000013F2E_IND_5 ON HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1(ORIGINAL_OBJECT_SCHEMA, ORIGINAL_OBJECT_NAME, PARTITION_NAME)
     SYS_MTABLE_000013F2E_IND_6 ON HRREST_TEST.HRREST_TEST_HR2_EXPORT_JOB_1(SEED)
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s):
     HRREST_HR2_EXPORT_JOB_1
     ITUNES
     REDDIT_SURVEY
Unexpected Table(s):
     HRREST_TEST_HR2_EXPORT_JOB_1
Changed Table(s):
     COUNTRIES
          remarks changed from 'country table. Contains 25 rows. References with locations table.' to 'null'
     DEPARTMENTS
          remarks changed from 'Departments table that shows details of departments where employees
work. Contains 27 rows; references with locations, employees, and job_history tables.' to 'null'
     EMPLOYEES
          remarks changed from 'employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.' to 'null'
     ESQL_3082
          remarks changed from 'Data Pump Master Table EXPORT                         SCHEMA' to 'null'
     ESQL_3202
          remarks changed from 'Data Pump Master Table EXPORT                         SCHEMA' to 'null'
     JOBS
          remarks changed from 'jobs table with job titles and salary ranges. Contains 19 rows.
References with employees and job_history table.' to 'null'
     JOB_HISTORY
          remarks changed from 'Table that stores job history of the employees. If an employee
changes departments within the job or changes jobs within the department,
new rows get inserted into this table with old job information of the
employee. Contains a complex primary key: employee_id+start_date.
Contains 25 rows. References with jobs, employees, and departments tables.' to 'null'
     LOCATIONS
          remarks changed from 'Locations table that contains specific address of a specific office,
warehouse, and/or production site of a company. Does not store addresses /
locations of customers. Contains 23 rows; references with the
departments and countries tables.' to 'null'
     REGIONS
          remarks changed from 'Regions table that contains region numbers and names. Contains 4 rows; references with the Countries table.' to 'null'
     all_the_hrs_1
          remarks changed from 'Data Pump Master Table EXPORT                         SCHEMA' to 'null'
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s):
     DATABASECHANGELOG_DETAILS
          definition changed from 'SELECT  da.deployment_id,    da.id,    da.author,    da.filename,  da.sql,     da.sxml,    d.dateexecuted,    d.exectype,    d.md5sum,  d.description,    d.comments,    d.liquibase,    d.contexts,    d.labels
FROM HRREST.DATABASECHANGELOG d  LEFT JOIN HRREST.DATABASECHANGELOG_ACTIONS da ON d.id = da.id AND d.author = da.author AND d.filename = da.filename ORDER BY   1,7' to 'SELECT  da.deployment_id,    da.id,    da.author,    da.filename,  da.sql,     da.sxml,    d.dateexecuted,    d.exectype,    d.md5sum,  d.description,    d.comments,    d.liquibase,    d.contexts,    d.labels
FROM HRREST_TEST.DATABASECHANGELOG d  LEFT JOIN HRREST_TEST.DATABASECHANGELOG_ACTIONS da ON d.id = da.id AND d.author = da.author AND d.filename = da.filename ORDER BY   1,7'


Operation completed successfully.

SQL>

Closing Thoughts

I’m not in love with the format of the report, the lists don’t seem to be sorted in the way I would do it, but ok. And most noticeable, there’s no ‘delta code’ to show you how to ‘fix’ the differences.

Like, love, or leave the report…it’s a report that’s available to you, from the command-line.

And while there is no ‘delta script’ available, there IS a command called ‘diff-changelog’ – where the output of the compare is a changeLog that outputs a changeLog to synchronize the target from the reference!

lb help diff-changelog -ex
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

Write A Comment