Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle CREATE SYNONYM tips

Oracle Database Tips by Donald BurlesonMarch 1,  2015

Oracle CREATE SYNONYM

An Oracle synonym is named and points to a specific object. Using the CREATE SYNONYM command, we can create a private synonym for SCOTT.EMP command in the ROBERT schema as follows:

SQL> CREATE SYNONYM emp FOR SCOTT.EMP;

Now that we have run the Oracle Oracle CREATE SYNONYM command, when we issue the query with just the EMP (removing the SCOTT.) Oracle will follow the synonym and we will see data from the SCOTT.EMP table:

In this case, the synonym created with the Oracle Oracle CREATE SYNONYM command is would be a private Oracle synonym. Only the user ROBERT can use the synonym created by this Oracle Oracle CREATE SYNONYM command. A public synonym can be created using the CREATE PUBLIC SYNONYM command as follows:

SQL> CREATE PUBLIC SYNONYM emp FOR SCOTT.EMP;

Generally you will not use the Oracle Oracle CREATE SYNONYM command to create public synonyms, as they are generally to be avoided. The reason to avoid the use of the Oracle Oracle CREATE SYNONYM command to create public synonyms is that public synonyms have security and performance issues associated with them. You should only use the Oracle Oracle CREATE SYNONYM command to create public synonyms if it is absolutely necessary.

You can use the Oracle Oracle CREATE SYNONYM command to create a public and private synonym having the same name. In fact, you can create a public and private synonym with the Oracle Oracle CREATE SYNONYM command called EMP in the SCOTT schema and have a table called EMP in the same schema. Multiple synonyms and/or tables can get confusing (this is another reason we hate public synonyms and don't generally use the Oracle Oracle CREATE SYNONYM command to create them). The order of precedence with regards to the use of synonyms and local objects is as follows:

Note that a synonym can be created, using the Oracle Oracle CREATE SYNONYM command, for an object that does not exist.

Tip:  You can use this command to avoid the use of public synonyms.  By setting the current_schema attribute to the schema owner name it is not necessary to create public synonyms for production table names.:

alter session set current_schema='MYSCHEMA';

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.