Sunday, 28 May 2017

synonym in Oracle SQL


Synonym:-A synonym is an alias for a table, view, snapshot, sequence, procedure, function,
or package.

There are two types to SYNONYMS they are
1) PRIVATE SYNONYM :-This can be access only who created .
2) PUBLIC SYNONYM :-This can be access by any user.

Syntax for Private SYNONYMS:-

Create or replace synonym <synonym_name> for <object_name>;

Syntax for Public SYNONYMS:-

Create or replace public synonym <synonym_name> for <object_name>;

Example:- Below is the private synonym

SQL>create or replace synonym employee for emp;

Data selection from private synonym:-

SQL>select * from employee;

Example:- Below is the public synonym
SQL>create or replace public synonym employee1 for emp;--Public

Data selection from public synonym:-

SQL>select * from employee1;

Note:- Only public synonym can be access by any user but private synonym can be accessed by creator.

Dropping Synonyms:-

SQL>drop synonym employee;  --drop private synonym
SQL>drop public synonym employee1;  --drop public synonym

Listing information about synonyms:-
SQL>select * from user_synonyms;

1 comment:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.

    Hurry-Up Enroll Now and get Free Access to the Entire Course


Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...