Compare the Difference Between Similar Terms

Difference Between

Home / Technology / IT / Database / Difference Between Synonym and Alias

Difference Between Synonym and Alias

November 14, 2011 Posted by Admin

Synonym vs Alias (in ORACLE databases) | Private synonyms and Public synonyms
 

In English, synonym and alias have nearly the same meanings. But in databases those are two different things. Especially in ORACLE databases, both of their usage is different. Synonyms are used to refer objects of a schema or a database from another schema. So synonym is a database object type. But aliases are coming in a different way. That means; they are not database objects. Aliases are used to refer tables, views and columns inside queries.

Synonyms

These are a type of database objects. They refer to other objects in the database. The most common usage of synonym is, to refer an object of a separate schema by using another name. But synonyms can be created to refer the objects of another database, as well (in distributed databases, using database links). Tables, views, functions, procedures, packages, sequences, materialized views, java class objects and triggers can be used as references for the synonyms. There are two types of synonyms.

  1.  Private synonyms (can be used only by the user who created them.)
  2.  Public synonyms (can be used by all users who have the appropriate privileges)

Here, is a simple syntax to create a synonym in a separate database,

create synonym myschema.mytable1 for [email protected]_link1

Since we have a synonym named mytable1 in myschema for [email protected]_link1 (distributed database table), we can easily refer the distributed database table using mytable1. We don’t need to use the long object name with database link everywhere.

Alias

These are just another name for a view, a table, or a column inside a query. They are not database objects. Therefore, aliases are not valid everywhere in the schema/database. They are valid inside the query only. Let us see this example,

                                    select tab1.col1 as c1,tab2.col2 as c2

                                       from user1.tab1 tab1,user1.tab2 tab2

                                       where tab1.col1 = tab2.col2

Here, c1 and c2 are column aliases, which are used for tab1.col1 and tab2.col2, and tab1 and tab2 are table aliases, which are used for user1.table1 and user2.table2. All of these aliases are valid inside this query only. 

 

What is the difference between Synonym and Alias (in ORACLE databases)?

  • Synonyms are a database object type. But aliases are just a name to refer a table, view or a column inside a query. Not a database object.
  • Synonyms can be created for tables, views, functions, procedures, packages, sequences, materialized views, java class object types and triggers. But aliases are used only for views, tables and their columns.
  • Since synonyms are a database object, they are valid inside the schema (private synonym) or inside the database (public synonym). But aliases valid inside the query where they are being used.
  • Each schema needs “create synonym” privilege to create synonyms. But there is no any privilege to use aliases.

 

Related posts:

Difference Between SQL and Microsoft SQL Server Difference Between Deferred Update and Immediate Update Difference Between JDO and Value Object Difference Between Database and Instance Difference Between ODBC and ADO

Filed Under: Database Tagged With: alias, alias in oracle, alias in oracle database, alias vs, aliases, aliases vs, database object, Private synonyms, Public synonyms, queries, query, synonym, synonym in oracle, synonym in oracle database, synonym vs, synonyms, synonyms vs

About the Author: Admin

Coming from Engineering cum Human Resource Development background, has over 10 years experience in content developmet and management.

Comments

  1. pawan kumar says

    November 13, 2012 at 6:06 am

    SYNONYM: is dropped when the table or tablespace is dropped. Synonym is available only to the creator.

    ALIAS: is retained even if table or tablespace is dropped. ALIAS can be
    created even if the table does not exist. It is used mainly in
    distributed environment to hide the location info from programs. Alias
    is a global object & is available to all.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Request Article

Featured Posts

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and SARS

Difference Between Coronavirus and SARS

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Covid 19

Difference Between Coronavirus and Covid 19

You May Like

Difference Between HTC Rezound and Motorola Droid Razr

Difference Between HTC Rezound and Motorola Droid Razr

Difference Between Firefox and Chrome (2014)

Difference Between Firefox and Chrome (2014)

Difference Between Similarity and Identity in Sequence Alignment

Difference Between Similarity and Identity in Sequence Alignment

Difference Between Movie and Video

Difference Between Ascending and Descending Aorta

Difference Between Ascending and Descending Aorta

Latest Posts

  • What is the Difference Between Omphalocele and Gastroschisis
  • What is the Difference Between Autonomic and Somatic Reflexes
  • What is the Difference Between Plagiocephaly and Craniosynostosis
  • What is the Difference Between Coconut Oil and Virgin Coconut Oil
  • What is the Difference Between Wintrobe and Westergren Tube
  • What is the Difference Between Coarctation and Hypoplastic Aortic Arch
  • Home
  • Vacancies
  • About
  • Request Article
  • Contact Us

Copyright © 2010-2018 Difference Between. All rights reserved. Terms of Use and Privacy Policy: Legal.