"Referring to Objects in Remote Databases" for more information on referring to database linksĬREATE DATABASE LINK for more information on creating database linksĬREATE SYNONYM: Examples To define the synonym offices for the table locations in the schema hr, issue the following statement: Restriction on Database Links You cannot specify dblink for a Java class synonym. If you omit dblink, then Oracle Database assumes the object is located on the local database. Oracle recommends that you specify the schema containing the object in the remote database. If you specify dblink and omit schema, then the synonym refers to an object in the schema specified by the database link. However, the database link must then be included in all subsequent calls to the procedure or function.ĭblink 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. 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. SchemaSpecify the schema in which the object resides. Restriction on the FOR Clause 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. See Also: "CREATE SYNONYM: Examples" and "Oracle Database Resolution of Synonyms: Example" Specify the name of the synonym to be created. You cannot specify a schema for the synonym if you have specified PUBLIC.
If you omit schema, then Oracle Database creates the synonym in your own schema. Specify the schema to contain the synonym. 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. Notes on Public Synonyms The following notes apply to public synonyms: A private synonym name must be unique in its schema.
If you omit this clause, then the synonym is private and is accessible only within its schema. 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. Restriction on Replacing a Synonym You cannot use the OR REPLACE clause for a type synonym that has any dependent tables or dependent valid user-defined object types. Use this clause to change the definition of an existing synonym without first dropping it. Specify OR REPLACE to re-create the synonym if it already exists. To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.ĭescription of the illustration create_synonym.gif
To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM system privilege.
To create a private synonym in your own schema, you must have the CREATE SYNONYM system privilege. See Also: Oracle Database Concepts for general information on synonyms
0 kommentar(er)
