Synonym synonyms in oralce

Set synonyms for ease of use
Select * from EMP; and so on are used for select * from ABC;
Create a common synonym
If the user's level is not enough to establish a public synonym, You can authorize it under system:
Grant create any synonym to Scott;
Grant create public synonym to Scott;
Cancel authorization:
Revoke create any synoym from Scott;
Revoke create public synoym from Scott;
Cancel synonym:
Drop public synonym ABC;
Synonym for canceling authorization:
Grant Dorp public synonym to Scott;
Cancel authorization:
Revoke drop public synonym to Scott;
15.9 why synonym is introduced
In some commercial databases, sometimes the design of information systems or developers intentionally define some long table names (or other objects) to increase the accessibility ). This makes it easier to reference these tables or objects and produces input errors. In addition, in actual commercial companies, some users think that an object name is meaningful and memorable, but other users may think that another name is more meaningful.
Synonym provided by Oracle is used to solve the above problems. Imagine that you use the supplier table many times a day in your daily work, and your English typing level is not high. In this case, you can use synonym to increase productivity.
15.10 how to create a synonym (Synonym)
Now you can use the following create synonym Statement (example 15-38) to create a synonym (alias) s for the supplier table.
Example 15-38
SQL> Create synonym s
2 For supplier;
Result 15-38
You have created a synonym.
Now you can use the synonym (alias) s as a supplier. You can use the following query statement (for example, 15-39) to verify this.
Example 15-39
SQL> select *
2 from S;
Result 15-39
S_code sname contact phone fax
------------------------------------------------------------------------
2000 xiankelai department store Zhang genfa 4444944 4444844
2010 xintaisoft commodity stone core 1741741 1741742
2021 daily tableware jinyuanbao 1671671 1671674
2032 food-first tableware Lu hecai 1681684 1681684
Then you may ask, how do you know which synonyms you have (Synonym?
Do you still remember the data dictionary user_objects? Since synonym is an object, they must be recorded in this data dictionary. Therefore, you can use the following query statement (for example, 15-41) to obtain all your Synonyms (Synonym) from the data dictionary user_objects. Of course, to make the SQL * Plus display output clearer, you should first use the following SQL * Plus formatting command (for example, 15-40 ).
Example 15-40
SQL> Col object_name for A20
Example 15-41
SQL> Select object_name, object_type, created, status
2From user_objects
3 Where object_type like 'syn % ';
Result 15-41
Object_name object_type created status
------------------------------------------------------
S synonym 28-4 month-03 valid
The result displayed in example 15-41 shows that there is only one Synonym (Synonym) in your mode (user), that is, the synonym (Synonym) s you just created.
However, the result displayed in example 15-41 does not tell you who the synonym (Synonym) is based on the table and who the table owner is. If you have such a synonym (Synonym), this information may be particularly important. You can use the following query statement (for example, 15-43) to obtain this information from the data dictionary user_synonyms. Of course, to make the SQL * Plus display output clearer, you should first use the following SQL * Plus formatting command (example 15-42 ).
Example 15-42
SQL> Col table_owner for A12
SQL> Col table_name for A12
Example 15-43
SQL> select synonym_name, table_owner, table_name
2 from user_synonyms;
Result 15-43
Synonym_name table_owner table_name
----------------------------------------------------
S Scott supplier
The result displayed in example 15-43 shows that there is only one Synonym (Synonym) in your mode (user), that is, the synonym (Synonym) s you just created. The synonym (Synonym) is based on the table supplier, and the table owner is Scott.
The statement format for creating a synonym (Synonym) is as follows:
Name of the synonym for create [public] Synonym
For Object Name;
Where:
Public: all users in the system can access the created synonym.
Ø synonym name: name of the created Synonym
Ø Object Name: the object name based on the created Synonym
Note the following when creating a synonym:
The object based on cannot be included in any software package.
A private synonym cannot be the same as any other object under this user.
The synonym (Synonym) s you just created is a private synonym, that is, it can only be directly referenced under your user (Scott), if other users reference it, it must be named as a user name. (Scott. s ). This is inconvenient. If your supplier table is shared and frequently used by all users, what should you do?
15.11 create a public synonym (Synonym)
You can create a common synonym (Synonym) for the supplier table ). For ease of demonstration, you should switch from the current Scott user to another user, such as system. You can use the following SQL * Plus command (for example, 15-44) to complete user switching.
Example 15-44
SQL> connect system/Manager
Result 15-44
Connected.
If you use the following query statement (for example, 15-45), you will not get the information you want.
Example 15-45
SQL> select *
2 from S;
Result 15-45
From S
*
Error is located in row 2nd:
ORA-00942: Table or view does not exist
If you use the following query statement (for example, 15-46), you will not get the information you want.
Example 15-46
SQL> select *
2 from supplier;
Result 15-46
From supplier
*
Error is located in row 2nd:
ORA-00942: Table or view does not exist
If you name username. (Scott.) before the synonym (Synonym) s, you can get the information you need. The following query statement (for example, 15-47 ).
Example 15-47
SQL> select *
2 from Scott. S;
Result 15-47
S_code sname contact phone fax
------------------------------------------------------------------------
2000 xiankelai department store Zhang genfa 4444944 4444844
2010 xintaisoft commodity stone core 1741741 1741742
2021 daily tableware jinyuanbao 1671671 1671674
2032 food-first tableware Lu hecai 1681684 1681684
Other users must use your username (.) When referencing this synonym (Synonym) S (.)., this is inconvenient and prone to input errors, so you can use the following DDL Statement (example 15-48) to create a common synonym (Synonym) for the supplier under Scott) SS.
Example 15-48
SQL> Create public synonym SS
2 For Scott. supplier;
Example 15-48 results
You have created a synonym.
Now you can use the synonym ss you just created to obtain the desired information. You can use the following query statement (for example, 15-49 ).
Example 15-49
SQL> select *
2 from SS;
Result 15-49
S_code sname contact phone fax
------------------------------------------------------------------------
2000 xiankelai department store Zhang genfa 4444944 4444844
2010 xintaisoft commodity stone core 1741741 1741742
2021 daily tableware jinyuanbao 1671671 1671674
2032 food-first tableware Lu hecai 1681684 1681684
You may also ask if other users can use the same method to access the synonym ss. The answer is yes. If you have any questions, use the following SQL * Plus command (for example, 15-50) to log on to the Oracle database as a sys user.
Example 15-50
SQL> connect sys/Oracle AS sysdba;
15-50 results
Connected.
Now you can use the synonym ss to obtain the desired information. You can use the following query statement (for example, 15-51 ).
Example 15-51
SQL> select *
2 from SS;
Result 15-51
S_code sname contact phone fax
------------------------------------------------------------------------
2000 xiankelai department store Zhang genfa 4444944 4444844
2010 xintaisoft commodity stone core 1741741 1741742
2021 daily tableware jinyuanbao 1671671 1671674
2032 food-first tableware Lu hecai 1681684 1681684
Oracle does not provide commands to modify Synonyms (Synonym. If you want to modify a synonym (Synonym), you need to delete it first, and then recreate the synonym (Synonym ).
15.12 delete a synonym (Synonym)
After a while, you can use the following DDL Statement (example 15-53) to delete the synonym (Synonym. However, you may have to use the following SQL * Plus command (example 15-52) to re-register it with Scott.
Example 15-52
SQL> connect Scott/Tiger
Result 15-52
Connected.
Example 15-53
SQL> drop synonym S;
Result 15-53
The synonym is discarded.
Although the result of 15-53 in this example shows that "synonyms have been discarded .", However, to be cautious, you should use the following query statement (example 15-54) to view the data dictionary user_objects.
Example 15-54
SQL> select object_name, object_type, created, status
2 from user_objects
3 where object_type like 'syn % ';
Result 15-54
Unselected row
Alternatively, use the following query statement (for example, 15-55) to view the data dictionary user _ synonyms.
Example 15-55
SQL> select synonym_name, table_owner, table_name
2 from user_synonyms;
Result 15-55
Unselected row
The results displayed in examples 15-54 and 15-55 further confirm that you have successfully deleted the synonym (Synonym) from the system.
The synonym statement format is as follows:
Name of the synonym for drop synonym;