Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rupali.

Asked: January 29, 2001 - 2:17 am UTC

Last updated: June 12, 2007 - 2:07 pm UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Dear Tom,

I was trying to export a Single User with Exp80.exe. Kindly do tell me what exactly is expected to happen when there are some Public Variables or Synonyms declared by this User? These synonyms may be for his own objects or even for those objects on which he his having grants.

Thanks,

Rupali





and Tom said...

Public synonyms are never exported except during a FULL database export.

Public synonyms are not owned by anyone. All public synonyms show up in DBA_SYNONYMS with an owner of PUBLIC.

The best you could do is to create an auxillary script to be run with the export that would create a public synonym for each object of that user that has a public synonym. For example:


select 'create public synonym ' || synonym_name ||
' for ' || table_owner || '.' || table_name || ';'
from dba_synonyms
where owner = 'PUBLIC'
and table_owner = 'THAT USER'
/

spool that output and it'll rebuild the public synonyms for you.


Rating

  (2 ratings)

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

Comments

A reader, June 12, 2007 - 12:54 pm UTC

i have synonyms already created can you give me script to export those synonyms.

any help will be appreciated
Tom Kyte
June 12, 2007 - 2:07 pm UTC

umm, that is what this page already has on it???

synonym with database link

Khalid Al-Mansour, May 10, 2016 - 9:44 am UTC

There are public synonyms that reference object on another database using database link. the script for creating the public synonym should be changed to consider them.

or you can use it but run it two times one for local objects and another for remote objects and change the create statement.
Regards,
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.