Synonyms in Oracle SQL - Day 42
In Oracle SQL, a synonym is an alias or alternative name for a database object, such as a table, view, sequence, procedure, or function. Synonyms are used to simplify database access and provide a level of abstraction, making it easier for users to interact with database objects without needing to know their exact names or locations.
Here's a short description of synonyms in Oracle SQL:
1. Creating Synonyms:
```sql
CREATE [PUBLIC] SYNONYM synonym_name FOR object_name;
```
`synonym_name`: The name you want to give to the synonym.
`object_name`: The name of the actual database object (table, view, etc.) for which you want to create the synonym.
2. Using Synonyms:
Once a synonym is created, you can use it in SQL statements instead of the original object name. For example:
```sql
-- Using the synonym in a SELECT statement
SELECT * FROM synonym_name;
-- Using the synonym in a PL/SQL procedure
CREATE OR REPLACE PROCEDURE my_procedure AS
BEGIN
INSERT INTO synonym_name (column1, column2) VALUES ('Value1', 'Value2');
END;
```
3. Advantages of Synonyms:
Simplification: Synonyms make database access more straightforward, especially when working with objects in different schemas or databases.
Security: You can grant users access to synonyms without revealing the actual object names, providing an additional layer of security.
Schema Changes: If the underlying object's name changes, you can update the synonym definition, and the applications using the synonym won't be affected.
4. Public vs. Private Synonyms:
Public synonyms are accessible to all users in the database, and they are created in a special schema called PUBLIC. They can be useful for sharing common objects across different user schemas.
Private synonyms are created in individual user schemas and are only accessible to that specific user or other users granted explicit access to them.
5. Removing Synonyms:
```sql
DROP [PUBLIC] SYNONYM synonym_name;
```
Use the `DROP` command to remove a synonym. The `PUBLIC` keyword is used to drop a public synonym.
In summary, synonyms in Oracle SQL act as aliases for database objects, providing a convenient and secure way to access those objects. They simplify database interactions and protect sensitive object names, enhancing the overall manageability and security of the database system.