Home » RDBMS Server » Server Utilities » unable to create public synonym (AIX, oracle 11.2.0.2)
unable to create public synonym [message #508504] Sun, 22 May 2011 11:28 Go to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I have exported and imported a schema from one server to another. In the source schema, I have a public synonym. I do not know the name of that synonym. In the destination schema, the public synonym is missing. How to create the public synonym which is missing in the destination database?
In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.

[EDITED by LF: fixed topic title typo; was "synymym"]

[Updated on: Mon, 23 May 2011 01:23] by Moderator

Report message to a moderator

Re: unable to create public synymym [message #508507 is a reply to message #508504] Sun, 22 May 2011 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.

So either it does not exist, either you you wrongly queried.
Schema export does not contain the public synonyms this is the expected behaviour.
As you are in 11.2 use data pump instead then you can use the INCLUDE parameter to add the public synonyms.

By the way, it is not a good idea to use public synonyms, envisage to convert them to private ones.

Regards
Michel

[Updated on: Sun, 22 May 2011 12:02]

Report message to a moderator

Re: unable to create public synymym [message #508515 is a reply to message #508507] Sun, 22 May 2011 14:59 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I gave this command and I was able to view the name of the
public synonym.

select synonym_name from dba_synonyms where owner in 'PUBLIC' and
table_owner in 'schema_name';
Re: unable to create public synymym [message #508539 is a reply to message #508515] Mon, 23 May 2011 01:11 Go to previous message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you know what you have to do.

By the way, use "=" and not "IN" when there is only one value.

Regards
Michel
Previous Topic: parameter files
Next Topic: validation after expdp and impdp
Goto Forum:
  


Current Time: Wed Apr 16 03:00:10 CDT 2014

Total time taken to generate the page: 0.09327 seconds