1. 什么是同义词
A synonym is an alias for one of the following objects(实际上就是一种映射关系):
- table
- object table
- view
- object view
- sequence
- stored procedure
- stored function
- package
- materialized view
- java class
- used defined object object type
- another synonym
Synonyms 不能用在drop table、drop view、truncate table/cluster 语句中,否则,会出现0RA-00942:table or view does not exist错误。
A public synonym is owned by public rather than the one who has created it. Therefore, a public synonym is valid for each schema.
使用同义词有如下好处:
(1)对于开发人员来讲,不必关心数据库的具体对象名称,同时,也简化了数据库对象的访问。
(2)避免了应用程序直接访问数据库对象,提高了数据库的安全性。
(3)扩大了数据库的使用范围,能够在不同的数据库用户之间实现无缝交互。
(4)同义词可以创建在不同一个数据库服务器上,通过网络实现连接。
2. 创建同义词
授权:
grant create any synonym to Sharon;
grant create public synonym to Leo;
取消授权:
revoke create any synonym from Sharon;
revoke create public synonym from Leo;
创建同义词:
create synonym s_emp for emp;
create public synonym p_emp for emp;
若要创建一个远程数据库上某张表的同义词,需要先创建一个Database Link(数据库连接)来扩展访问,然后使用如下语句创建:
create public synonym p_emp for emp@DB_Link;
3. 删除同义词
drop public synonym world;
4. 查看所有同义词
select * from dab_synonyms;
5. 参考资料
http://database.ctocio.com.cn/tips/457/8286457.shtml
http://www.adp-gmbh.ch/ora/concepts/synonyms.html
http://www.qqread.com/oracle/2010/04/b493100.html