Since you do not seem to about the history or any child workspaces, then one option is to drop the current tables on the new database. Then use dbms_wm.Export from the original source db and export the tables using the LIVE workspace and the versioned_db parameter set to false. Import the tables using dbms_wm.Import, and then version enable them.
If this suggestion isn't feasible for some reason, you can find the version in the LIVE workspace on the original source db by querying the all_version_hview view. Keep in mind that this view might be showing different results compared to the time when the export was done. I would need to know alot more about your usage as to whether that would be the case, and if it would even make a difference in this instance. After you determine the versions, they can then be used to match up with version column in the _HIST view or the _LT table. You need to be sure to only keep the most recent change for a particular primary key, if multiple ones exist in the LIVE workspace. At this point, you can version enable the table again.
There is no way to only import the WMSYS data for a single schema.
Thank you - that is very helpful. Your first solution looked like it would be best.
Yesterday I looked at obtaining the LIVE version data from the source DB instance, but the drift in data content since the time the original export was done meant that the data refresh would have been fraught with risk, just as you identified.
I convinced the powers-that-be that a do-over of the full export/import was the best way to go, however, this time we first un-versioned the tables in the source DB.
I intend to study the documentation for how best to perform this process if we *did* want to retain all the workspaces and versioned data, but can I beg your indulgence and ask you what you would recommend?
Assuming a clean install of Oracle 11g (complete with WMSYS) as the destination, would it be appropriate to:
- drop the WMSYS schema in the destination;
- export the MY_SCHEMA and WMSYS from the source;
- import into the destination
- upgrade the WMSYS in the destination from 10g to 11g
Is this the basic process, or am I missing something important?
On 11g, we only support full database import/export using the same version of Workspace Manager on both the source and target databases. Since your upgrade is from 10g to 11g, this by default is not supported. In the past, we have recommended to uninstall the current version of Workspace Manager from the target, install the same version that is on the source. This will allow the import to succeed, after which you can upgrade Workspace Manager back to its original version.
The restriction with having to have the same version on both the source and target has been relaxed as of 220.127.116.11. We support imports into 18.104.22.168 using a dumpfile created with at least version 22.214.171.124.