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
2 FROM 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 shown in example 15-41 shows:In your mode (user), there is only one Synonym (Synonym), that is, the Synonym you just created (Synonym) s.
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

1 2 Next Page