| 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 |
