Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 02-02-2010
Member
 
Join Date: Jan 2010
Posts: 48
The synonyms in oracle database

I am SQL beginner and want to know about the existence of synonyms in database and their usage. I am aware about only one feature, it provides the second and short name for a database object that has a large name.Can you provide me some more information and possible features of synonyms.Please help me !!!!
Reply With Quote
  #2  
Old 02-02-2010
Member
 
Join Date: Apr 2008
Posts: 1,948
The synonyms in oracle database

Synonyms :

The synonyms provide an alternative name for any database object (Table,view,sequence, procedure, stored function, package) and much more.
It allows applications to execute without any updation without any headache of which user owns the table or view and which database holds particular object.

You can use synonyms with following DML statements : SELECT, INSERT, UPDATE, DELETE, EXPLAIN PLAN and LOCK TABLE
and with DML statements like AUDIT, NOAUDIT, GRANT, REVOKE and COMMENT.
Reply With Quote
  #3  
Old 02-02-2010
Member
 
Join Date: May 2008
Posts: 2,008
Creating synonyms in oracle database

There are two ways of creating synonyms for different purpose that we would discuss here.
1. Simple synonyms
2. Public synonyms

Creating Simple SYNONYMS:

Assume, The table Library_booksheld by SCOTT schema and you want to create a synonyms named "L_books". To do this you need to use this statements :

Code:
SQL> CREATE SYNONYM L_books
                FOR SCOTT.Library_books;
Reply With Quote
  #4  
Old 02-02-2010
Member
 
Join Date: Apr 2008
Posts: 2,001
Creating public synonyms in oracle database

Creating PUBLIC Synonyms :

You can create a PUBLIC synonyms on any object of any database that is on remote location.Here I will show you a statement which creates a synonyms named "Emp" for database object "EMPLOYEES" that is on remote location and the name of database is "Org_database". To create the synonyms for EMPLOYEES table in Kelfro schema use below statement

Code:
SQL> CREATE PUBLIC SYNONYM emp 
         FOR Kelfro.employees@Org_database;

Last edited by kelfro : 02-02-2010 at 10:36 PM.
Reply With Quote
  #5  
Old 02-02-2010
Member
 
Join Date: May 2008
Posts: 2,293
Dropping synonyms in oracle database

Dropping Synonyms :

You can drop the created synonyms of any simple or Public synonyms using this drop statement for synonyms.Before dropping any synonyms you should have privilege to drop.

Code:
SQL> DROP PUBLIC SYNONYM emp;
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "The synonyms in oracle database"
Thread Thread Starter Forum Replies Last Post
Statpack in oracle database Reegan Networking & Security 5 16-02-2010 08:00 PM
Dynamic SQL in Oracle database Adriana_andros Software Development 5 11-02-2010 07:49 PM
Java in oracle 9i database Gerri Software Development 4 03-02-2010 08:35 PM
Top-N Analysis in Oracle database LaMarcus Software Development 3 28-01-2010 12:05 PM


All times are GMT +5.5. The time now is 02:57 PM.