Ask Anantha

The web gives me a definition of Synonym as "A word that means the same as another word." Well Oracle also defines its Synonyms in the same way. Then what is a Private Synonym? It is nothing more than a Synonym accessible to users of the particular schema where the synonym is created. Public synonyms on the other hand once created by any user, will be accessible for all schemas in a particular database. How to create a (Private) Synonym? The syntax for creating a synonym is CREATE [ OR REPLACE ] SYNONYM synonym_name FOR object; Example: CREATE SYNONYM clerk FOR employee; In our context employee is a table (or can be a view). As Clerk is also an employee, it makes understanding more easy. You can query from clerk as you would do from employee table/view. Now you must have got the idea of why a synonym is used.  Note: The usage of a (private) synonym from public synonym will be where you need to expose your not-so critical data to all database users. If your data is considered ...

The web gives me a definition of Synonym as "A word that means the same as another word." Well Oracle also defines its Synonyms in the same way. Then what is a Public Synonym? It is nothing more than a Synonym accessible to all users of a Oracle database. How to create a Public Synonym? The syntax for creating a public synonym is CREATE [ OR REPLACE ] PUBLIC SYNONYM synonym_name FOR object; Example: CREATE PUBLIC SYNONYM clerk FOR employee; In our context employee is a table (or can be a view). As Clerk is also an employee, it makes understanding more easy. You can query from clerk as you would do from employee table/view. Now you must have got the idea of why a synonym is used.  Note: The usage of a public synonym from private synonym will be where you need to expose your not-so critical data to all database users. If your data is considered private, you should not be creating public synonyms for this purpose. How to destroy a Public Synonym? To destroy or drop a public sy...

This tip comes from Vikash Varma , Oracle DBA at Intelligent Consulting, in New Jersey . "ORA-00980: synonym translation is no longer valid" is a common error encountered in a development environment. This can happen for many reasons. Some of them are: You created a synonym on non-existing object by mistake. For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing. You dropped an object but you did not drop the synonyms referencing the object. You dropped a user, but you did not drop synonyms referencing the objects owned by that user. When an object is dropped, synonyms referring to the object are not dropped. The following script lists all such invalid synonyms: select * from dba_synonyms s where table_owner not in('SYSTEM','SYS') and db_link is null and not exists ( select 1 from dba_objects ...

Posted in ITToolbox by Lewis The full link for the article is here . Today I am going to talk about synonyms and what you should NOT do with them. Since this is a MINI tip, it will be short and sweet. I will not cover every aspect of synonyms. A quick definition: two words that can be interchanged in a context are said to be synonymous relative to that context. In Oracle terms: An alias for a table, view, materialized view, sequence, procedure, function, package, type, Java class schema object, user-defined object type, or another synonym. So that means that instead of referencing a table like owner.table, you can create a synonym for that owner.table and reference it by the synonym name. For example, SQL> connect system@unixxe Enter password: ****** Connected. SQL> select count(*) from employees; select count(*) from employees * ERROR at line 1: ORA-00942: table or view does not exist . . SQL> select count(*) from hr.employees; . COUNT(*) ---------- 107 . SQL> create publi...