Create a synonym.
Syntax:
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym
FOR [schema.]object [@dblink]
'PUBLIC' will create a public synonym, accessible to all users (with the appropriate privileges.)/p>
Unlike Views, Synonyms do not need to be recompiled when the underlying table is redefined.
There is a small performance hit when accessing data through a public synonym.
Oracle will resolve object names in the following order:
current user
private synonym
public synonym
An alternative method to access data in another schema is to use:
ALTER SESSION set current_schema = Other_Schema
Script to drop and recreate synonyms for every object in a schema.
The output of this select is a SQL script with all the 'Create Synonym…' statements:
Select 'DROP PUBLIC SYNONYM '||object_name||' ;'||chr(13)||chr(10)||'Create
public synonym '||object_name||' for MySchemaName.'||object_name||' ;'
from user_objects
where object_type in (
'TABLE',
'VIEW',
'SEQUENCE',
'PROCEDURE',
'PACKAGE',
'FUNCTION'
)
"A synonym is a word you use when you can't spell the word you first thought of" - Burt Bacharach
Related Oracle Commands:
DROP SYNONYM
CREATE VIEW
Commands that can use synonyms:
AUDIT
COMMENT
DELETE
EXPLAIN PLAN
GRANT
INSERT
LOCK TABLE
NOAUDIT
REVOKE
SELECT
UPDATE
Related Views:
DBA_SYNONYMS ALL_SYNONYMS USER_SYNONYMS PUBLICSYN