Public Synonym Generation w/ "Exclude Schema Names" Enabled Identifies Mis-Matches

mbruegel

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 all_synonyms 

 WHERE synonym_name = '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 foo_bar FOR my_schema.bar vs.  CREATE OR REPLACE PUBLIC SYNONYM foo_bar FOR bar

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.  my_schema_v1 to my_schema_v2).