1 Reply Latest reply: May 7, 2012 12:56 PM by Ben Speckhard-Oracle RSS

    Link to versioned tables from other schema

    aschilling
    aschilling May 4, 2012 8:26 AM
      hi!

      we are currently evaluating possible data synchronization mechanisms for a future database A that should use data from an existing database B.
      option 1 is the simplest: export data from B, import in A and you're done. problems are of course the usual ones like hassle with import/export, how up-to-date the data is, etc.
      however we are more interested in option 2: link to the tables in B from A. if we are not wrong here we could establish this by using dblink and synonyms.
      importand fact is, the tables in B are versioned with OWM. is it in any way possible to link to those in that case? and if so, how would workspace/savepoint selection be handled?
      we suspect it's not possible, at least not if the tables in database A aren't versioned as well. or one would need to omit real foreign keys when referencing versioned tables in B from A, which isn't exactly good either.

      thanks for any hints in advance!

      regards,

      Andreas
        • 1. Re: Link to versioned tables from other schema
          Ben Speckhard-Oracle
          Ben Speckhard-Oracle May 7, 2012 12:56 PM (in response to aschilling)
          Hi Andreas,

          You can use links/synonyms. GotoWorkspace works the same as it would for the local database. You just have to execute it on the remote database.

          For example,
          SQL> exec dbms_wm.GotoWorkspace@dblink('Workspace1') ;

          You should be able to use tables from different databases within the same query. However, there isn't any support for foreign key constraints where the tables are defined on different databases and only the child tables is versioned. In the case, the parent table needs to be part of the same db as the child.

          Regards,
          Ben