With the “Exclude Schema Names” option enabled, SCO identifies false-positive mismatches for public synonyms.

In Oracle public synonym DDL w/o owner specified defaults the object owner to current user.

CREATE PUBLIC SYNONYM foo_bar FOR bar
/

SELECT *
FROM allsynonyms
WHERE synonymname = 'FOO_BAR'
/

SCO happily generates the Public synonym DDL w/o the owner specified (and it can be executed as such). However, on subsequent comparisons the underlying object’s qualified name is used and the public synonyms are all identified as being different.
e.g. CREATE OR REPLACE PUBLIC SYNONYM foobar FOR myschema.bar vs. CREATE OR REPLACE PUBLIC SYNONYM foo_bar FOR bar

This behavior feels like a bug.

Suggesting a fix:

Option 1
- Ignore the current schema owner value (source / target side) if the “Exclude Schema Names” option is selected and the target has no (or a different) owner on the target-side DDL
(This option seems more straightforward, but the implicit ambiguity could be considered a bit risky – esp. since the SCO generated public synonym DDL uses CREATE OR REPLACE PUBLIC SYNONYM)

Option 2
- One could also make the argument that public synonyms are a bit of a special case – because they are PUBLIC / DB instance wide.
Perhaps the default behavior ought to be for developers to be specific about the public synonym object ownership. Taking this approach, it may make sense to have a special configuration setting specific to excluding the schema name for the public synonym underlying object ownership (i.e. this would support moving objects cross schema while maintaining the public synonym – e.g. myschemav1 to myschemav2).

Additional considerations for either solution:
- Synonyms (public or private) are a bit of a special case because you can declare synonyms for another user’s objects.
- Public synonyms are even more of a special case because there is a single instance of the synonym name which is owned by PUBLIC – i.e. there is only one source of truth for the PUBLIC synonym at any point in time.
- SCO's use of the "OR REPLACE" caluse for PUBLIC (global) synonyms is potentially problematic as this can result in an unintentional repointing of the object a synonym points to (or evrn pointing a public synonym to a non-existant object) -- w/o warning!
-- Omitting the "OR REPLACE" clause would result in an error if the PUBLIC synonym already existed
- Also consider the deployment models:
-- Running deployments as SYSTEM (or an execution / super-user account w/ multi-schema – CREATE ANY * - privs)
--- in this case you will most certainly want / need to specify a qualified schema owner for nearly every type of object including synonyms (public or private).
-- Running deployments where the user doing the deployment is the object owner (i.e. not using SYSTEM or a multi-schema privileged user)
--- in this case the need to explicitly identify the object owner is somewhat lessend (assuming current user / deployer owne the object referenced by the synonym), but only somewhat as a synonym can be declared for another schema's objects

Here are some examples:

If deployment user is:
- “SCHEMA1”
-- CREATE OR REPLACE PUBLIC SYNONYM foobar FOR bar (is FOR SCHEMA1.bar - not SYSTEM.bar)
- “SCHEMA2”
-- CREATE OR REPLACE PUBLIC SYNONYM foobar FOR bar (is FOR SCHEMA2.bar - not SYSTEM.bar)

• “SCHEMA1”
  -- CREATE OR REPLACE PUBLIC SYNONYM foobar FOR SCHEMA_2.bar (schema owner is qualified; this is allowed – as I can create a public synonym for another user’s objects)
  --- HOWEVER -- this is where the addition of the OR REPLACE clause is actually a bit dangerous, because I can effectively “hi-jack” / repoint a public synonym w/o warning
  --- Using only the CREATE PUBLIC SYNONYM… I’d get an error alerting me that the name was already in use and I’d need to resolve that conflict before continuing
  -- May be a good idea to force an explicit drop and re-create – or at least make the inclusion of “ OR REPLACE” clause and optional behavior for the public synonyms?

• “SYSTEM” (or multi-schema privileged user)
  -- CREATE OR REPLACE PUBLIC SYNONYM foobar FOR <schemaowner>.bar (will most certainly require a schema owner name, as it is not a good practice to set-up your objects under the SYSTEM user. Likewise you wouldn’t want your deployment user account to be the designated object owner of any unqualified object names the public synonyms point to)
  (In this case there is even a greater risk of unqualified object names resulting in a public synonym that points to a non-existant objects -- e.g. SYSTEM.bar)

In Summary:
- Probably best practice to encourage the use of qualified object names for synonyms (public or private)
- May want to option toggle the use of the OR REPLACE clause for public synonyms
-- safest to default to forcing a drop / recreate of a public synonym if the name is already in use
-- some use cases may want to leverage the OR REPLACE clause (see next item), but that may be more of be a special use case (i.e. an override of default behavior)
- There may be some use cases where users intentionally want to omit the schema name of the underlying object, to facilitate re-pointing the underlying object a public synonym resolves to