Analysis of OCP question _ 007: Synonym

Synonyms are the aliases of the base table. They are defined only in the data dictionary and are not stored.
Synonyms provide location transparency for distributed databases, and there is no difference in accessing resources in different regions like accessing local resources.

(I) syntax

Syntax for creating Synonyms:

Private synonyms without public
A private synonym is a synonym created by a common user. It is private because only the user who creates the synonym can use this synonym.
Public synonyms are created by the database administrator. Of course, public synonyms allow all users to use them.
If you want to create a synonym for a table on a remote database, you must first create a database link and then use @ db_link_name

When the same name appears, the priority is as follows:
Private synonym> Public Synonym
Base table> Public Synonym
If an object with the same name or a public synonym exists, the database preferentially selects the object as the target.
If private and public objects with the same name exist, the database preferentially selects private synonyms as the target.
The base table and private synonym cannot have the same name; otherwise, a ORA-00955 error is reported.

(Ii) synonym operations

① View Synonyms

sys@ORCL> select table_name from dict where table_name like '%SYNONYM%';TABLE_NAME------------------------------DBA_SYNONYMSUSER_SYNONYMSALL_SYNONYMS

② Delete Synonyms

Drop [public] synonym [schema.] synonym name [force];

Drop synonym sysn_test;

Drop public synonym public_test; -- when the original object of the synonym is deleted, the synonym is not deleted.

③ Compile Synonyms

Alter synonym t compile; -- when the original object of the synonym is re-created, the synonym must be re-compiled.
After you perform DDL operations on the original object, the synonym status changes to invalid.
When this synonym is referenced again, it will be automatically compiled and the state will change to valid without manual intervention. Of course, the premise is that the name of the original object is not changed.


(3) permission management for synonyms

The permissions related to synonyms include create synonym, create any synonym, and create public synonym.

① If a user creates a private synonym in his/her own mode, the user must have the create synonym permission; otherwise, the user cannot create a private synonym.
② If you want to create a synonym in other modes, you must have the create any synonym permission.
③ Create public synonym system permission is required to create a public synonym.


(Iv) OCP questions

Q: 16 Mary has a view called EMP_DEPT_LOC_VU that was created based onthe EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scotton this view. Which option enables Scott to eliminate the need to qualify the view with the nameMARY.EMP_DEPT_LOC_VU each time the view is referenced?A. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE PRIVATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU; then he can prefix the columns with this synonym. B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU; then he can prefix the columns with this synonym. C. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE LOCAL SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU; then he can prefix the columns with this synonym. D. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU ON mary(EMP_DEPT_LOC_VU); then he can prefix the columns with this synonym. E. Scott cannot create a synonym because synonyms can be created only for tables. F. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and grant SELECT privilege on that synonym to Scott. Answer: B