The issue here is, an user can not merge a workspace to "LIVE" because another user is currently in his workspace. The other user is a remote connection through DBLink, which has no reason to be in this workspace, even shouldn't have privilege to be in this workspace.
In the source code for view WMSYS.WM$WORKSPACE_SESSIONS_VIEW
CREATE OR REPLACE FORCE VIEW WMSYS.WM$WORKSPACE_SESSIONS_VIEW
(USERNAME, WORKSPACE, SID, SADDR)
select st.username, wt.workspace, st.sid, st.saddr
from v$lock dl,
where dl.type = 'UL' and
dl.id1 - 1 = wt.workspace_lock_id and
dl.sid = st.sid;
It joins v$lock and v$session to wmsys.wm$workspaces_table.workspace_lock_id. For sure in our customized code we won't issue a statement of GotoWorkspace('xxx') for the remote user. So what trigger this session to have a lock to this workspace?
Is it possible that Oracle didn't recycle the session ID so that another user pick up the session?
I did some tests:
Connected as user1, go into workspace W1, then issue a long run query. Before the query finished, closed sqlplus, disconnected the session.
At this point, I could see the session had gone, but this user still showed in dba_workspace_sessions for this workspace. It took seconds to clean up.
Then I tried to connect with another user to try to grab the session id, to see if it's possible to grab the session id while it's still showing in dba_workspace_sessions. But either I'm not fast enough, or Oracle did check dba_workspace_sessions and wait till it's clean to let the session id to be reused.
Yes, DBLink is using "connect to". This doesn't happen often, I should say, very seldom. I can't reproduce the problem so far.
Because there are hundres connections through this DBLink, so that I think it's just coincidently this id shows in the workspace. And I believe it's more a background process issue more than customized code because if we have to specify a workspace's name to go into it, there's no way the customized code can figure out a way to find this name of the new created workspace by some other users, and go into it.
The database manages all of the connections without using the dba_workspace_sessions view. So, if a connection was being used, it would not be anything Workspace Manager related. OWM just uses the provided session/transaction views to construct dba_workspace_sessions.
However, you mentioned that the user should not even be able to access the workspace. Could you list the privileges the user does have, as well as the privileges on the workspace that the user is accessing?
SQL> select * from dba_workspace_privs where workspace='<workspace>' ;
SQL> select * from dba_wm_sys_privs where grantee='<user>';
Also, are there any system logon triggers defined that are placing the user in the workspace?
There is also the possibility that there is a conflict on the distinct ids that Workspace Manager uses to obtain the lock. It would be possible for another session to issue dbms_lock.request with the same id as the one used for the particular workspace, to make it appear (through the view) that the user is in the workspace, while in reality they are not in the workspace. The user would not have any access to the data within the workspace. Are you aware of any other applications obtaining locks using the dbms_lock package that could be creating such a conflict?