I am having a problem merging a user workspace into a LIVE workspace.
I currently have two users both have WM_ADMIN_ROLE.
User 1 (is the owner of the tables)
user 2 (is a another user with access the user1s schema and is where the LIVE tables are held)
user2 will create a workspace and make some changes. At some point in the future user1 will be required to merge the two workspaces together.
The merge process is handled via a procedure within a package which is executed by user1, but when the procedure is executed the following is displayed.
ORA-20044: insuffient privileges to merge data
ORA-06512: at "WMSYS.LT", line 6028
ORA-06512: at "NSD.NSD_WORKSPACE_UTILS", line 587
ORA-06512: at line 2
The package is simply
PROCEDURE merge_workspace(p_workspace VARCHAR2) IS
dbms_wm.mergeworkspace(p_workspace, remove_workspace => TRUE);
However if i try are simply execute exec dbms_wm.mergeworkspace('worlspace', remove_workspace => TRUE); in the command line it will merge
I have also looked into the DBMS_WM.GETPRIVS thinking that was the issue but user1 has CREATE,FREEZE,ROLLBACK,MERGE,REMOVE,ACCESS on user2 workspace.
I hope this is clear, and many thanks in advance
What version of OWM are you using?
However, I was not able to reproduce this, so I am unsure as to why you would be getting that error. It may depend on the package definition(user vs definer's rights), or how the table privileges were granted(through roles vs directly to the user).
If you have a testcase that demonstrates it, then feel free to post it or file an SR.
Apologies for the late response.
It may well be worth stating that I am new to using Oracle Workspace Manager.
Here is some background of how the Tables/Database is set up.
Users are assigned to a role called 'ADVANCED_USER' which grants the ability to SELECT, INSERT, UPDATE and DELETE on all tables in the database and also applies to another schema created by a different user. A GRANT EXECUTE to the packages controlling the updates has also given to the 'ADVANCED_USER'
In addition to this individual users are also assigned WM_ADMIN_ROLE.
I am however unsure by what you mean when you say +‘package definition (user vs definer's rights)’+. I naively assume that it would be whatever the default would be as I have not knowingly changed or set anything when creating the package.
It may take some time for me to produce a test case, but I will try and post one ASAP.
Edited by: MunboJumbo on May 5, 2010 5:28 PM
Packages are by default definer's rights, which means the package runs with the privileges of the owner of the package, not necessarily the user running it. Typically, privileges granted through roles are not enabled within procedures/packages, and instead the privileges need to be granted directly.
If you do get a testcase, I could confirm whether or not this is the cause of the behavior you are seeing.