You can specify a complete or partial database link to create a synonym for a schema object on a remote database where the object is located. However, the database link must then be included in all subsequent calls to the procedure or function. Alternatively, you can create a local public synonym on the database where the object resides.

If you are creating a synonym for a procedure or function on a remote database, then you must specify schema in this CREATE statement. If you do not qualify object with schema, then the database assumes that the schema object is in your own schema. Specify the schema in which the object resides. The schema object cannot be contained in a package. The schema object need not currently exist and you need not have privileges to access the object. The schema object for which you are creating the synonym can be of the following types: Specify the object for which the synonym is created. If you do so, then all PL/SQL units that use that name will be invalidated. Take care not to create a public synonym with the same name as an existing schema. If you create a public synonym and it subsequently has dependent tables or dependent valid user-defined object types, then you cannot create another database object of the same name as the synonym in the same schema as the dependent objects. The following notes apply to public synonyms: A private synonym is accessible to users other than the owner only if those users have appropriate privileges on the underlying database object and specify the schema along with the synonym name. A private synonym name must be unique in its schema. If you omit this clause, then the synonym is private. When resolving references to an object, Oracle Database uses a public synonym only if the object is not prefaced by a schema and is not followed by a database link. However each user must have appropriate privileges on the underlying object in order to use the synonym. Public synonyms are accessible to all users.

Specify PUBLIC to create a public synonym. You can refer to synonyms in the following DDL statements: AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT. You can refer to synonyms in the following DML statements: SELECT, INSERT, UPDATE, DELETE, FLASHBACK TABLE, EXPLAIN PLAN, LOCK TABLE, MERGE, and CALL. Appropriate privileges must be granted to a user before the user can use the synonym. However, synonyms are not a substitute for privileges on database objects. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. Synonyms provide both data independence and location transparency. A synonym places a dependency on its target object and becomes invalid if the target object is changed or dropped. Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, operator, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.

Synonym account for