CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Synonyms
Version 11.1
 
General
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.
Related Data Dictionary Objects
syn$ all_synonyms dba_synonyms synonyms user_synonyms
Related System Privileges
create synonym create any synonym
create public synonym drop any synonym
drop public synonym  
GRANT create synonym TO uwclass;

REVOKE create synonym FROM uwclass;
Demo Table For Create Synonym CREATE TABLE obnoxiouslynamedtable (
test VARCHAR2(20));
 
Create Synonym

Create Private Synonym
CREATE OR REPLACE SYNONYM <synonym_name> FOR <object_name>
desc obnoxiouslynamedtable

SELECT *
FROM obnoxiouslynamedtable;

CREATE OR REPLACE SYNONYM
simple FOR obnoxiouslynamedtable;

desc user_synonyms

SELECT synonym_name, table_owner, table_name
FROM user_synonyms;

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

SELECT * FROM obnoxiouslynamedtable;
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

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

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

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
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

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

CREATE SYNONYM a FOR servers;

CREATE SYNONYM b FOR test_view;

CREATE 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;
 
Drop Synonym
Drop Private Synonym DROP SYNONYM <synonym_name>
DROP SYNONYM simple;
Drop Public Synonym DROP PUBLIC SYNONYM <synonym_name>
DROP PUBLIC SYNONYM alltab;
 
SQL Statements And Code

Drop All Synonyms
SELECT synonym_name
FROM user_synonyms;

CREATE OR REPLACE PROCEDURE dropsyn 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: emp\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
Aliases
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [95 users online]    © 2010 psoug.org