Oracle Synonyms
Version 23c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 <EDITIONABLE | NONEDITIONABLE> SYNONYM
[IF NOT EXISTS <synonym_name>]
[SHARING = <METADATA | NONE>]
FOR <object_name> [@ <dblink_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 <EDITIONABLE | NONEDITIONABLE> PUBLIC SYNONYM
[IF NOT EXISTS <synonym_name>]
[SHARING = <METADATA | NONE>]
FOR <object_name> [@ <dblink_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 <EDITIONABLE | NONEDITIONABLE> [PUBLIC] SYNONYM
[IF NOT EXISTS <schema_name.>] <synonym_name>]
[SHARING = <METADATA | NONE>]
FOR [<schema_name>.]<object_name> [@ <dblink_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;
Understanding 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;
Create Schema Synonyms SQL> conn / as sysdba

SQL> ALTER SYSTEM SET "_enable_schema_synonyms"=TRUE
  2  COMMENT='Schema synonyms enabled 08-Jul-2023'
  3  CONTAINER=ALL
  4  SID='*'
  5  SCOPE=SPFILE;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 2550136832 bytes
Fixed Size 3048872 bytes
Variable Size 671091288 bytes
Database Buffers 1862270976 bytes
Redo Buffers 13725696 bytes
Database mounted.
Database opened.

SQL> CREATE SCHEMA SYNONYM dvault FOR dvsys;
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT


SQL> alter pluggable database pdbdev open;

Pluggable database altered.

SQL> conn sys@pdbdev as sysdba
Enter password:
Connected.

SQL> CREATE SCHEMA SYNONYM dev FOR uwclass;

Schema synonym created.

SQL> SELECT COUNT(*) FROM dev.servers;

  COUNT(*)
----------
       141

SQL> DROP synonym dev;
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


SQL> DROP SCHEMA SYNONYM dev;

Schema synonym dropped.
 
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
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx