RE: Oracle 10g upgrade with Workspace Manager

From: Stephens, Chris <>
Date: Fri, 16 May 2008 09:01:14 -0500
Message-ID: <>

You have my sympathies.  

OWM's functionality is extremely useful in certain situations but the feature is a major pain in the @#$! from and administrative perspective. We have experienced severe performance problems and while I don't consider myself proficient at tuning, some of the execution plans generated against version enabled tables would cause even the most seasoned professional to head for the bottle.  

I had a similar situation. We consolidated a few development databases and I needed to move several application schemas including the version enabled schema into a new development database. Luckily the versions were the same and we didn't have to deal with availability issues. In the end I used datapump excluding the schema with version enabled tables to move most everything over. I then created the version enabled tables manually + the workspaces and scripted out the data migration using the OWM supplied export packages (dbms_wm.export + dbms_wm.import). You may be able to upgrade your OWM release in place in case the dbms_wm export and import procedures don't work between versions.  

Of course it took me several iterations to get it all right and in the process discovered that dropping a schema with version enabled tables isn't the best idea in the world.  

Good luck and may the force be with you.  

[] On Behalf Of
Sent: Friday, May 16, 2008 8:47 AM
Subject: Oracle 10g upgrade with Workspace Manager  

One of our last databases to upgrade to 10G makes extensive use of Oracle Workspace Manager. At 9i OWM does not support schema exports. Most of our other databases were upgraded with dbua. There are some issues with OWM and its TIMESTAMP WITH TIME ZONE that must be treated manually after dbua in this instance. For that, and a mess of other reasons, I'd like to create a fresh 10G database, a full database export from 9i to the new 10g. Of course, we're going to try this on a staging machine first, but has anyone else done this already? My concern is the differences in how OWM is configured, with a lot stuff in the SYS schema in 9i which moves to WMSYS is 10G. And probably other things I'm forgetting. All in all, most problems in this databases are because of OWM and there is usually little information about it because it's not widely used. I was to avoid as much heartache as possible.

Sherrie Kubis
Oracle DBA Supervisor
Information Resources Department
Application Systems Section
Southwest Florida Water Management District 352-796-7211 x4033


Good judgement comes from experience,
and a lot of that comes from bad judgement.

IMPORTANT NOTICE: All E-mail sent to or from this address are public record and archived. The Southwest Florida Water Management District does not allow use of District equipment and E-mail facilities for non-District business purposes.  

        This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

-- Received on Fri May 16 2008 - 09:01:14 CDT

Original text of this message