Synonyms are a very powerful feature of Oracle. They are auxiliary names that relate to other database objects: tables, procedures, views, etc. They work like Unix hard links; a pointer to point to an object that exists somewhere else. As database systems grow and applications improve, there is usually a need to change the names of tables and views to better reflect their new functionality.
The problem comes in changing the name in all places before placing the revised application into production. Thanks to synonyms, both names might be used in the application and database. A curious feature of a synonym is that each may have its own synonym or many synonyms. According to the picture above: Public synonyms are non-schema objects, when private synonyms as tables are schema objects.
For example:. While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. Please re-enable javascript in your browser settings. Description A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. If you need to access the underlying object in a different schema, modify the definition of the synonym to point to the object in a different schema.
Thus, the applications based on the synonym continue to function without modification in the SQL statement. This ensures that you do not have to modify your SQL statements to perform operation on the object in different schemas.
Changes in the underlying objects : The synonyms insulate you from any changes in the name or location of the underlying objects on which you are performing an operation. You can modify the synonym definition to accommodate any changes in the name or location of the underlying objects. For example, suppose that you are using a table in one of your stored procedures. Now, if the table name changes or the table is moved to some other location then your stored procedure will stop working.
To work around this, you can use a synonym for the table in the stored procedure, and update the synonym definition if there is a change in the name or location of the table. Simplified and secure access : In a distributed environment, you must use the schema name along with the object names to ensure that you are accessing the correct object.
However, the database link must then be included in all subsequent calls to the procedure or function. If you specify dblink and omit schema , then the synonym refers to an object in the schema specified by the database link. Oracle recommends that you specify the schema containing the object in the remote database. If you omit dblink , then Oracle Database assumes the object is located on the local database. Restriction on Database Links You cannot specify dblink for a Java class synonym.
To create a PUBLIC synonym for the employees table in the schema hr on the remote database, you could issue the following statement:. A synonym may have the same name as the underlying object, provided the underlying object is contained in another schema. For example, the schemas oe and s h both contain tables named customers.
If the user sh then issues the following statement, then the database returns the count of rows from sh.
0コメント