Create a synonym.
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:
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||' ;'
where object_type in (
"A synonym is a word you use when you can't spell the word you first thought of" - Burt Bacharach
Commands that can use synonyms:
DBA_SYNONYMS ALL_SYNONYMS USER_SYNONYMS PUBLICSYN