Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-1631

The oracle connector scans too many objects while attempting to determine the most recent ddl time

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Minor Minor
    • 1.0.0.CR1
    • 0.10.0.Final
    • oracle-connector
    • None
    • Hide

      Have a last_ddl_time that is prior to any snapshots existing, and attempt to register an oracle connector.

      Show
      Have a last_ddl_time that is prior to any snapshots existing, and attempt to register an oracle connector.

      When the oracle connector registration occurs, it runs a query like

      SELECT MAX(TIMESTAMP_TO_SCN(last_ddl_time)) from all_objects where owner = 'USER'
      

      This is running timestamp_to_scn on all ddl times, which is unnecessary from a performance perspective as well as can lead to errors like

      Caused by: java.lang.RuntimeException: java.sql.SQLException: ORA-08180: no snapshot found based on specified time
      ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1
      

      when the snapshots don't exist. This is particularly obvious when it's a pre-existing database with changes from long ago.

      Proposal would be to change the query to

      SELECT TIMESTAMP_TO_SCN(MAX(last_ddl_time)) from all_objects where owner = 'USER'
      

      Which will call timestamp_to_scn on only the last change.

              Unassigned Unassigned
              shelfoo Sheldon Fuchs (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: