CREATE SYNONYM

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


© Copyright SS64.com 1999-2013
Some rights reserved