5 Replies Latest reply: Jun 7, 2013 4:12 AM by Stefan Jager RSS

    Retrieving LIVE Workspace Records from *_LT tables

    953138
      Hello,

      We have Workspace Manager enabled tables in multiple schemas in multiple remote databases. Long story short, we are using Golden Gate to replicate the _LT tables to a central source, since we cannot replicate the created views of the tables that Workspace Manager provides. 

      How can I differentiate LIVE workspace data from all the other versioned sets of data within the tables without the other _BASE, _AUX, etc. tables?  Is it safe to assume if the version=0 that it is part of LIVE?  Are there any exceptions to this rule that I need to look for?

      Any insight would be appreciated.
        • 1. Re: Retrieving LIVE Workspace Records from *_LT tables
          Ben Speckhard-Oracle
          Hi,

          You can use the all_version_hview view which maps versions to workspaces. Version 0 is currently always LIVE, but I would just use the view instead of making explicit assumptions.

          Regards,
          Ben
          • 2. Re: Retrieving LIVE Workspace Records from *_LT tables
            davidp 2
            I use savepoints and versioning history VIEW_WO_OVERWRITE and in a test environment with everything in LIVE we have multiple VERSION values - I think they correspond to the save points.
            select * from  all_version_hview  where workspace = 'LIVE';
            
               VERSION PARENT_VERSION WORKSPACE
            ---------- -------------- ------------------------------
                     9              8 LIVE
                     1              0 LIVE
                     2              1 LIVE
                     3              2 LIVE
                     4              3 LIVE
                     5              4 LIVE
                     0             -1 LIVE
                     6              5 LIVE
                     7              6 LIVE
                     8              7 LIVE
            There is quite a lot of data at different version numbers:
            select version, count(*) from STRAND_NH_LT GROUP BY VERSION order by version;
            
               VERSION   COUNT(*)
            ---------- ----------
                     0       8942
                     2      10047
                     3         64
                     4         50
                     5         94
                     6         53
                     7       1242
            so version=0 for LIVE is not safe in this environment.

            Can you get GoldenGate to transfer some of the relevant WMSYS tables ?
            • 3. Re: Retrieving LIVE Workspace Records from *_LT tables
              953138
              Unfortunately the WMSYS schema does not play well with Golden Gate, and lot of the "tables" are actually views, which we cannot replicate.

              After confirming that my assertion of version=0 was definitively wrong, I went with the approach of creating a database linked view to the replicated databases' all_version_hview view to figure out what versions were legitimate.
              • 4. Re: Retrieving LIVE Workspace Records from *_LT tables
                Ben Speckhard-Oracle
                Hi,

                The 0 version is currently always in/part of the LIVE workspace. However, there may be many more versions that are also contained in the LIVE workspace.

                Yes, you need to replicate the tables, not the views. Are you trying to go from a versioned environment to a non-versioned environment? Some information as to the overall goal you are trying to accomplish would be helpful.

                Thanks,
                Ben
                • 5. Re: Retrieving LIVE Workspace Records from *_LT tables
                  Stefan Jager
                  Have you looked at [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/long_repl.htm#g635775]Appendix C of the documentation, and the [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/long_intro.htm#sthref227]Replication Support Subprograms?

                  I have no experience with it, but
                  Documentation says:
                  Workspace Manager supports replication of all workspace-related entities (such as workspaces and savepoints), operations (such as CreateWorkspace and MergeWorkspace), and DML and DDL operations on version-enabled tables.
                  Maybe that's worth looking into for your situation? I don't know GoldenGate, so that may be blocking, but Oracle seems to support replication quite well for WM.

                  Cheers,
                  Stefan