同义词
为了查询其它用户所拥有的表,你需要将该用户名加句点作为前缀加在表名前面,如:
SQL> show user;
USER is "SYS"
SQL> select * from scott.jocky;
ID NAME SEX MAIL SALARY
---------- ---------- ----- ------------------------- ----------
1 jocky man jocky2813@sina.cn 10000
2 mike man mike@sina.cn 6000
3 john woman john@sina.cn 4800
4 blues man blues@sina.cn 7500
5 write woman write@sina.cn 6500
而创建同义词可以除去对象名必须带的方案限制,所以说该方法对于具有特别长的对象名字很有作用。
语法:
CREATE [PUBLIC] SYNONYM synonym FOR object;
PUBLIC:创建一个可以被所有用户访问的同义词
synonym:要被创建的同义词的名字
object:指出要创建同义词的对象,这里得注意了,对象不能包含包
创建和删除同义词(默认情况下创建和删除同义词是数据库管理员的权限)
SQL> show user;
USER is "SYS"
SQL> create public synonym sunboy for scott.jocky;
Synonym created.
SQL> select * from sunboy;
ID NAME SEX MAIL SALARY
---------- ---------- ----- ------------------------- ----------
1 jocky man jocky2813@sina.cn 10000
2 mike man mike@sina.cn 6000
3 john woman john@sina.cn 4800
4 blues man blues@sina.cn 7500
5 write woman write@sina.cn 6500
删除同义词:
SQL> drop public synonym sunboy;
Synonym dropped.
SQL> select * from sunboy;
select * from sunboy
*
ERROR at line 1:
ORA-00942: table or view does not exist
为了查询其它用户所拥有的表,你需要将该用户名加句点作为前缀加在表名前面,如:
SQL> show user;
USER is "SYS"
SQL> select * from scott.jocky;
ID NAME SEX MAIL SALARY
---------- ---------- ----- ------------------------- ----------
1 jocky man jocky2813@sina.cn 10000
2 mike man mike@sina.cn 6000
3 john woman john@sina.cn 4800
4 blues man blues@sina.cn 7500
5 write woman write@sina.cn 6500
而创建同义词可以除去对象名必须带的方案限制,所以说该方法对于具有特别长的对象名字很有作用。
语法:
CREATE [PUBLIC] SYNONYM synonym FOR object;
PUBLIC:创建一个可以被所有用户访问的同义词
synonym:要被创建的同义词的名字
object:指出要创建同义词的对象,这里得注意了,对象不能包含包
创建和删除同义词(默认情况下创建和删除同义词是数据库管理员的权限)
SQL> show user;
USER is "SYS"
SQL> create public synonym sunboy for scott.jocky;
Synonym created.
SQL> select * from sunboy;
ID NAME SEX MAIL SALARY
---------- ---------- ----- ------------------------- ----------
1 jocky man jocky2813@sina.cn 10000
2 mike man mike@sina.cn 6000
3 john woman john@sina.cn 4800
4 blues man blues@sina.cn 7500
5 write woman write@sina.cn 6500
删除同义词:
SQL> drop public synonym sunboy;
Synonym dropped.
SQL> select * from sunboy;
select * from sunboy
*
ERROR at line 1:
ORA-00942: table or view does not exist
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9472111/viewspace-746621/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9472111/viewspace-746621/