Database Administration Question:

Download Questions PDF

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

A synonym (synonym-name) is an alias for an object (such as a table). The object does not need to exist at the time of its creation. Synonyms can't be used in a drop and truncate statements. If this is tried, it results in a ORA-00942: table or view does not exist

The syntax for creating a synonym is:

create [or replace] [public] synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];

The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.

The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.

The object_name phrase is the name of the object for which you are creating the synonym. It can be one of the following:

table package
view materialized view
sequence java class schema object
stored procedure user-defined object
function synonym

Type of Synonym are:-
Public (Accessible to all)
Private (Accessible to owner)