Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

user_synonyms vs dba_synonyms

Santosh PradhanJul 9 2012 — edited Jul 9 2012
Hi Friends,

I know what is the difference between user_synonyms and dba_synonyms, but i am failing to understand why i am not getting the output for one query that i am using.

Explanation: I have to refresh my UAT DB with Prod DB and after refresh i also have to create synonyms for the same. To do the same i am using the below mentioned query. The steps are as follows:

I am connected to Oracle by the user = schema_name.

When i am using the below query i am not getting any output
SELECT 'CREATE OR REPLACE SYNONYM '||synonym_name||' FOR SCHEMA_NAME.'||table_name||';' FROM user_synonyms WHERE table_owner='SCHEMA_NAME';
and when i am the below query, i am getting the output
SELECT 'CREATE OR REPLACE SYNONYM '||synonym_name||' FOR SCHEMA_NAME.'||table_name||';' FROM dba_synonyms WHERE table_owner='SCHEMA_NAME';
I am flustered.Kindly suggest on the same...

BR
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 6 2012
Added on Jul 9 2012
2 comments
629 views