Skip to Main Content
  • Questions

  • Create public synonyms based on the public synonyms shown in Sql Developer Public Synonyms folder

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Robert.

Asked: December 02, 2020 - 2:16 am UTC

Last updated: December 04, 2020 - 3:16 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

I am trying to copy a list of public synonyms defined in a schema (Create syntax) to replicate them in another schema, particularly certain sys.V$* views.
What table(s) are used to achieve this.

Thanks in advance

and Chris said...

You can get the DDL for objects with dbms_metadata.

So to find all the public synonyms for objects in a given schema, run:

set long 10000
select dbms_metadata.get_ddl ( 'SYNONYM', synonym_name, 'PUBLIC' ) 
from   dba_synonyms
where  table_owner = 'CHRIS';

DBMS_METADATA.GET_DDL('SYNONYM',SYNONYM_NAME,'PUBLIC')                                         

  CREATE OR REPLACE NONEDITIONABLE PUBLIC SYNONYM "RUNSTATS_PKG" FOR "CHRIS"."RUNSTATS_PKG"


I'm concerned by this:

particularly certain sys.V$* views.

NEVER MESS WITH PUBLIC SYNONYMS ON SYS OBJECTS! The v_$ views already have public synonyms on them, there's no need to make new ones. If you're thinking of changing these... just don't!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

*Public* synonyms replicated to another schema??

Kim Berg Hansen, December 03, 2020 - 12:08 pm UTC

What I don't understand about the question is, that the question wants to replicate *public* synonyms *defined* in one schema to another schema?

If they are *public*, then they are available in all schemas, right? They aren't really "defined in one schema", other than in the sense that a given user issued the CREATE PUBLIC SYNONYM command.

If it's about public synonyms defined on objects in a given schema, then again I don't see the use case for replicating them, since if they are *public*, how can they be re-created in another schema?

It would make sense to replicate *private* synonyms from one schema to another, but *public*?

Or am I mis-understanding something?
Connor McDonald
December 04, 2020 - 3:16 am UTC

Sounds like potential a grant issue here not a synonym issue
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.