On database "LOCAL" schema "USERS", I have a table "Accounts" .
On database "REMOTE" schema "USERS", I have the same table "Accounts".
The LOCAL.USERS.Accounts table includes an after insert/update/delete trigger that is intended to apply any LOCAL.USERS.Accounts edits to the REMOTE.USERS.Accounts table; to keep the data synchronized.
LOCAL.USERS has a public database link to REMOTE (one that does not specify IDENTIFY AS).
When I directly UPDATE LOCAL.USERS.Account, my changes are reflected immediately on REMOTE.USERS.Accounts -- the trigger works as intended.
However, when I try to use a package in LOCAL schema ACTORS to UPDATE LOCAL.USERS.Account, I get an error:
ORA-01017: INVALID USERNAME/PASSWORD; LOGON DENIED ORA-02063: PRECEDING LINE FROM REMOTE
The REMOTE database does not have a ACTORS schema, which I assume is the root of the problem.
How can I determine, from REMOTE's perspective, which schema is trying to connect? How can I force the trigger to apply edits to REMOTE as the USERS schema and not the ACTORS schema?
Creating a private database link in the LOCAL USERS schema, with a IDENTIFY AS USERS clause, forced the trigger to connect to the REMOTE database as USERS (which has an account) and not ACTORS (which does not have an account on the remote database).