Create and list the SYNONYM objects in Oracle

Create and list the SYNONYM objects in Oracle

SYNONYM is used to give the alternative name to the objects for make your database object name more secure.
You can also give synonym name to the database link to make it short or easy to use instead of defining the dblink again and again.
You can create alternative name of a table, view, sequence, procedure, stored function, and materialized view.

Check the SYNONYM present in Schema

col synonym_name for a12
col table_owner for a12
col table_name for a12
col db_link for a20
select synonym_name,table_owner,table_name from dba_synonyms where owner='TEST';

SYNONYM_NAME TABLE_OWNER  TABLE_NAME   DB_LINK
------------ ------------ ------------ -------------
TEST         TEST         TEST2

Syntax for creating SYNONYM in Oracle Database

CREATE [OR REPLACE] [PUBLIC] SYNONYM schema.synonym_name FOR schema.object;

Example of creating Synonym

CREATE SYNONYM emp_add FOR hr.addresses;

Example of creating Synonym for DBLINK

CREATE PUBLIC SYNONYM emp FOR hr.employees@SchoolABC;

Drop Synonym

DROP SYNONYM synonym_name;

Note: You can use other object in your schema by creating synonym with same name in you schema.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.