Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 9: SQL Statements for Other Database Objects
Lastly, we create synonym using CREATE SYNONYM statement. Synonym is
basically an alias or another name given to already existing database
object e.g. table, view, sequence, stored procedure, stored function,
package etc. The last three names you just read are also object of
oracle database but the fall under the category of PL/SQL language. It
can be further clarified by looking at the following real world example.
Example:
CREATE SYNONYM offices
FOR hr.locations;
So instead of typing the full name for the object i.e.
schema.object_name, you have to just type the synonym name. If you want
to grant access to other users, the users need not to know which schema
this object is in. Moreover, if you drop this synonym all users will not
be having access to hr.locations table any more. Remember dropping the
synonym will not drop the underlying table or object.
Example:
CREATE PUBLIC SYNONYM employees
FOR hr.employees@sales;
Public synonym are accessible to all the users provided they have access
to underlying object. The above statement will create a employee synonym
for a employees table in hr schema in a remote sales database.
Example:
DROP SYNONYM offices;
We use DROP SYNONYM statement to drop the synonym completely from the
database. The underlying object will not get removed.
I thing you must be looking that I haven’t discussed something like
ALTER SYNONYM. So just keep in your mind there is no such statement. If
you want to alter synonym just recreate it by first dropping it and then
using CREATE SYNONYM statement.
Chapter 9: SQL Statements for Other Database Objects
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: