| General Information |
| 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 |
| ALL_SYNONYMS |
DBA_SYNONYMS |
SYN$ |
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;
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; |
| |
| 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; |
| |
| SQL Statements And Code |
| Drop All Synonyms |
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 |
