Oracle Synonyms
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Note: Large OLTP systems where users log in to the database as their own user ID can benefit from explicitly qualifying the segment owner, rather than using public synonyms. This significantly reduces the number of entries in the dictionary cache.
Data Dictionary Objects
ALL_SYNONYMS DBA_SYNONYMS SYNONYMS
CDB_SYNONYMS SYN$ USER_SYNONYMS
Related System Privileges
CREATE ANY SYNONYM CREATE SYNONYM DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM DROP ANY SYNONYM  
GRANT create synonym TO uwclass;

REVOKE create synonym FROM uwclass;
Table For Create Synonym Demos CREATE TABLE reallyobnoxiouslynamedtable (
test VARCHAR2(20));
 
Create Synonym
Create Private Synonym CREATE OR REPLACE SYNONYM <synonym_name> FOR <object_name>;
desc reallyobnoxiouslynamedtable

SELECT *
FROM reallyobnoxiouslynamedtable;

CREATE OR REPLACE SYNONYM
simple FOR reallyobnoxiouslynamedtable;

desc user_synonyms

col synonym_name format a30
col table_owner format a30
col table_name format a30

SELECT synonym_name, table_owner, table_name
FROM user_synonyms;

INSERT INTO reallyobnoxiouslynamedtable VALUES ('ABC');
INSERT INTO simple VALUES ('DEF');

SELECT * FROM reallyobnoxiouslynamedtable;
SELECT * FROM simple;
Create Public Synonym CREATE OR REPLACE PUBLIC SYNONYM <synonym_name> FOR <object_name>;
CREATE OR REPLACE PUBLIC SYNONYM alltab FOR all_tables;

desc alltab

SELECT COUNT(*)
FROM alltab;
Create A Synonym For An Object Owned By A Different Schema CREATE OR REPLACE SYNONYM <synonym_name> FOR <schema_name>.<object_name>;
conn uwclass/uwclass@pdbdev

SELECT * FROM v_$session;
SELECT * FROM sys.v_$session;

-- as SYS grant SELECT privilege to schema
conn / as sysdba@pdbdev

GRANT SELECT ON v_$session TO uwclass;

-- log back on as user
conn uwclass/uwclass

SELECT * FROM v_$session;
SELECT * FROM sys.v_$session;

CREATE OR REPLACE SYNONYM sess FOR sys.v_$sessions;

SELECT * FROM sess;
Understand the TABLE_NAME column of xxx_synonyms CREATE VIEW test_view AS
SELECT SUBSTR(object_name,1,30) objname , object_type
FROM all_objects;

CREATE OR REPLACE FUNCTION test_func RETURN INTEGER IS
BEGIN
  RETURN 1;
END test_func;
/

CREATE OR REPLACE SYNONYM a FOR servers;
CREATE OR REPLACE SYNONYM b FOR test_view;
CREATE OR REPLACE SYNONYM c FOR test_func;

desc a
desc b
desc c

set linesize 121

SELECT s.synonym_name, s.table_name, o.object_type
FROM user_synonyms s, user_objects o
WHERE s.table_name = o.object_name;
 
Alter Synonym
Recompile Synonym ALTER SYNONYM <synonym_name> COMPILE;
CREATE SYNONYM test_syn FOR servers;

ALTER SYNONYM test_syn COMPILE;
Rename Synonym RENAME SYNONYM <current_name> TO <new_name>;
RENAME SYNONYM test_syn TO syn_test;

SELECT synonym_name
FROM user_synonyms;
 
Drop Synonym
Drop Private Synonym DROP SYNONYM <synonym_name> [FORCE];
DROP SYNONYM simple;
Drop Public Synonym DROP PUBLIC SYNONYM <synonym_name> [FORCE];
DROP PUBLIC SYNONYM alltab FORCE;
 
Demos
Drop All Synonyms conn uwclass/uwclass@pdbdev

SELECT synonym_name
FROM user_synonyms;

CREATE OR REPLACE PROCEDURE dropsyn AUTHID DEFINER IS
 CURSOR s_cur IS
 SELECT synonym_name
 FROM user_synonyms;

 RetVal  NUMBER;
 sqlstr  VARCHAR2(200);
BEGIN
  FOR s_rec IN s_cur LOOP
    sqlstr := 'DROP SYNONYM ' || s_rec.synonym_name;

    EXECUTE IMMEDIATE sqlstr;
  END LOOP;
END dropsyn;
/

SELECT synonym_name
FROM user_synonyms;
Create Private Synonyms SPOOL c:\temp\csyns.txt

SELECT 'create or replace synonym ' || object_name ||
' FOR ' || user || '.' || object_name ||';'
FROM user_objects
WHERE object_type <> 'SYNONYM'
AND object_type NOT LIKE '%BODY';

SPOOL OFF

Related Topics
DDL Event Triggers
DDL Statements

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved