Skip to main content

Importance of Synonym in Oracle Database #Diksha Oracle Training Centre

Importance of Synonym in Oracle Database. 


Hello Everyone,

Today I will discuss about Synonym in Oracle as Synonym play important role in database to hide information of object. Before Starting this blog please go through my previous blogs of SQL in my archive folder. Let’s Discuss about Synonym. Synonym in database is an object which is basically used as alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. We generally use synonyms for granting access to an object from another schema and you don't want the users from which Schema it’s own the object.

In Another word we can say synonym is an alias for any table, view, snapshot, sequence, procedure, function, or package. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.

Synonyms are basically used for security purpose and convenience.

For example, Synonym can do the following:

·        Hide  the name and owner of an object.

·        Provide location transparency for remote objects of a distributed database.

·        Simple use of SQL statements for database users.

There are two types of Synonym:

·        Public Synonym

·        Private Synonym

 We can create both public and private synonyms.

Note : We need CREATE PUBLIC SYNONYMPermission to create public synonyms

Syntax for creating Synonym :

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name

  FOR [schema .] object_name [@ dblink];

Public synonym is global and owned by the special user group named PUBLIC and every user in a database can access it whereas a private synonym is in the schema of a specific user who has control over its availability to others by using Grant and Revoke.

 Syntax and example for Creating Public and Private Synonym:

 Public Synonym :

CREATE PUBLIC SYNONYM emp1

FOR dotc.employee;

This first CREATE SYNONYM example demonstrates how to create a synonym called emp. Now, users of other schemas can reference the table called employee without having to prefix the table name with the schema named dotc. Below is the example:

SELECT *

FROM emp1;

If we need to make any changes in Synonym as per requirement for the same name we can use Replace keyword.

CREATE OR REPLACE PUBLIC SYNONYM emp1

FOR dotc.employee;

Note : Once we define a synonym for an object, you can reference it in the SQL statements such as the SELECT, INSERT, UPDATE, and DELETE statement.

Below is the output of synonym emp1



 

 Private Synonym:

 

In Private we just omit the Public keyword, A private Synonym must be unique on its schema.

Syntax :

CREATE  SYNONYM emp

FOR dotc.employee;

Private Synonym has limitation to its own schema only, but we can give grant access to other user if required like select etc.

Synonyms are very useful in both distributed and non distributed database environments because they hide the identity of the objects, including its location in a distributed system. This is advantageous because if the underlying object must be renamed or moved, then only the synonym needs to be redefined. Applications based on the synonym continue to work without any modification. Synonym also simplify SQL statement for user in a distributed database system.

 One Basic Example:

A table employee is in the schema owned by the user DOTC. The SELECT privilege for the employee table is granted to PUBLIC. At this point, you have to query the table employee with a SQL statement  similar to the below query:

SELECT * FROM dotc.employee;

Here we must include both the schema that contains the table along with the table name to perform the query.

Suppose that the database administrator (DBA) creates a public synonym with the following SQL statement:

CREATE PUBLIC SYNONYM emp FOR dotc.employee;

After the public synonym is created, you can query the table employee with a simple SQL statement:

SELECT * FROM emp;

Note : Here  the public synonym emp hides the name of the table employee and the name of the schema that is dotc which contains the table employee.

So we are now able to understand the usage of Synonym in Database and  it’s importance . Synonymy used to hide table name as well as schema and we can simply query the synonym for data also it’s used for security purpose as it’s hide the information from the user.

Please go through this blog carefully  as Interviewer can ask frequent questions on Synonym to check it’s advantages in database. Please let me know if you have any doubts.

Thanks

Comments

Post a Comment

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows

Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database. Hello Everyone, Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view. https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose. Let’s understand   about   Materialized view : A materialized view is a view that stores the results of the view’s query. Whenever you query the ma

Top 50 Interview Questions On SQL and PL/SQL #DOTC_Mdb

                    Top 50 Interview Questions On SQL and PL/SQL. Today we will Discuss Top 50 interview questions and answers of SQL and PL/SQL which is frequently asked in interview.     Question 1: What is SQL and Classification of SQL? Answer SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.   Classification of SQL is as below: DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc DML (Data Manipulation Language) : Commands are  insert,update and delete . TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point. DCL (Data Control Language) : Commands are Grant , Revoke Question 2:    What is meant by Joins? What are the types of join? Answer Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.   There are various types of Joins as li