Third, after schema objects and private synonyms This is how public synonyms and private synonyms compare: Criteria If a private synonym is not found, Oracle will look in the public synonyms. If a schema object is not found, Oracle will look in the private synonyms for the user. Oracle will look at the user’s schema for the object name. The order of precedence for objects and synonyms is: Just like how, in mathematics, the multiplication and division are performed before the addition and subtraction, Oracle has an order in the way that the objects are looked up.
This is a valid scenario, and Oracle has rules on which order they are processed in. What happens if you have a public synonym with the same name as an existing object? Or a private synonym? The synonym name must be unique within the schema.
Private synonym: can only be accessed by the person who created the synonym. The user who creates the synonym it does not own it – it’s owned by the PUBLIC user group. Public synonym: can be accessed by any user on the database. There are two types of synonyms that can be created on an Oracle database: public and private. They both have their advantages, but they should only be used when appropriate.ĭifference Between Public and Private Synonym in Oracle Synonyms can be created on tables, views, sequences, stored procedures, and more. If you want to SELECT data from a table and use WHERE conditions or other logic, then a view is better.Īlso, a view can only be created on objects with data, such as tables and other views. If you just want to provide an alias for a table or another object, you can use a synonym. Well, a view contains more complicated logic. Why would you use a synonym instead of a view? The examples later in this article will demonstrate this further.Īs mentioned above, a synonym is an alias or alternative name for an object.Ī view object is an SQL query that is saved and run when other queries use that view. It also simplifies access to objects, especially when multiple databases and schemas are involved. You can provide access to an object to a user using a synonym, and you don’t need to worry about who owns the original object. 
The main reason is to simplify the user access to an object. Why would you want to create a synonym? Why not just rename the original object? How to Find All Synonyms In the DatabaseĪ synonym is an object in a database that represents an alternative name for other objects, such as tables, views, sequences, and stored procedures.Difference Between Public and Private Synonym in Oracle.
0 kommentar(er)
