Identify the public synonyms that will become invalid when you enable editioning for the selected schemas. Remember the rule here is that no non-editioned object can depend on an editioned object. The special Oracle users, like SYS and PUBLIC, can never be editioned, so by extension none of their objects can depend on anything in the schemas that you are editioning.
SELECT synonym_name, table_owner, table_name FROM dba_synonyms WHERE owner = ‘PUBLIC’ AND table_owner IN (SELECT owner FROM editioned_users_obj_cnt);
Now that you’ve found them what do you do? It would be nice if you could ascertain that some or all are not in use and just drop them. Doing this for objects that reside in the database is possible.
First, you should identify any dependencies on these public synonyms you need to drop and make them explicit references. This SQL will show the objects that need to be edited:
SELECT DISTINCT t.owner,t.name,t.type object_type,s.owner synonym_owner,s.synonym_name,s.table_owner || ‘.’ || s.table_name real_object
FROM dba_dependencies t JOIN dba_synonyms s ON (s.owner = t.referenced_owner AND s.synonym_name = t.referenced_name AND t.referenced_type = ‘SYNONYM’)
WHERE s.owner = ‘PUBLIC’ AND s.table_owner IN (SELECT owner FROM editioned_users_obj_cnt);
Fix those dependencies and check by running the SQL again and assure that it is clean.
For external connections, you cannot query a dependencies view. You can drop the synonyms on a test system and fix what breaks (either fixing the code to not need a synonym or create the private synonym),or you can simply create all the private synonyms that might possibly be used. The first way is obviously cleaner and better, but time consuming and more dangerous if you can’t test everything.
If you decide to take the approach of creating every possibly needed private synonym, this SQL will help you to do that. If you can all a filter to further restrict the grantee to a list of actual users that login on behalf of your app, even better. Be warned that running the resulting DDL will cause a lot of object invalidations, so schedule downtime appropriately.
SELECT ‘create or replace synonym ‘ || grantee || ‘.’ || table_name || ‘ for ‘ || owner || ‘.’ || table_name || ‘;’
FROM ((SELECT r.grantee, p.owner, p.table_name FROM dba_role_privs r JOIN dba_tab_privs p ON (p.grantee = r.granted_role)
UNION
SELECT grantee, owner, table_name FROM dba_tab_privs)MINUS SELECT owner, table_owner, table_name FROM dba_synonyms) a
WHERE (owner, table_name) IN(SELECT table_owner, table_name FROM dba_synonyms WHERE owner = ‘PUBLIC’ AND table_owner IN (SELECT owner FROM editioned_users_obj_cnt));