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

Details

    • 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.

    Description

      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.

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: