源码-Oracle数据库管理-第八章-序列和同义词-Part 2（同义词）

同义词：方案对象的别名，它不占用存储的空间，目的是在Oracle中为表、视图、序列、PL/SQL程序单元、用户自定义对象或其他的同义词创建友好的名称。

主要作用：

1. 安全性——防止暴露太多的信息给开发人员（无需使用类似apps.dept@db03的格式）

2. 降低复杂环境的出错率

scott_pd@ORCL> CREATE PUBLIC SYNONYM scottemp FOR scott.emp;
CREATE PUBLIC SYNONYM scottemp FOR scott.emp
*
第 1 行出现错误:
ORA-01031: 权限不足


scott_pd@ORCL> conn sys as sysdba
已连接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
sys_pd@ORCL

sys_pd@ORCL> CREATE PUBLIC SYNONYM scottemp FOR scott.emp;

同义词已创建。

sys_pd@ORCL> conn scott/tiger
已连接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott_pd@ORCL

scott_pd@ORCL> selecc count(*) from scottemp;
SP2-0734: 未知的命令开头 "selecc cou..." - 忽略了剩余的行。
scott_pd@ORCL> select count(*) from scottemp;

  COUNT(*)
----------
        23

scott_pd@ORCL> conn sys as sysdba
已连接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
sys_pd@ORCL

sys_pd@ORCL> grant scott create pubic synonym;
grant scott create pubic synonym
      *
第 1 行出现错误:
ORA-00990: 权限缺失或无效


sys_pd@ORCL> grant create pubic synonym to scott;
grant create pubic synonym to scott
      *
第 1 行出现错误:
ORA-00990: 权限缺失或无效


sys_pd@ORCL> grant create public synonym to scott;

授权成功。

sys_pd@ORCL> grant drop public synoym to scott;
grant drop public synoym to scott
      *
第 1 行出现错误:
ORA-00990: 权限缺失或无效


sys_pd@ORCL> grant drop public synonym to scott;

授权成功。

sys_pd@ORCL> conn scott/tiger;
已连接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott_pd@ORCL

scott_pd@ORCL> drop public synonym scottemp;

同义词已删除。

scott_pd@ORCL> select count(*) from scottemp;
select count(*) from scottemp
                     *
第 1 行出现错误:
ORA-00942: 表或视图不存在


scott_pd@ORCL> CREATE PUBLIC SYNONYM scottemp FOR scott.emp;

同义词已创建。

scott_pd@ORCL> desc user_synonyms;
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- ------------------------------------
 SYNONYM_NAME                                          NOT NULL VARCHAR2(30)
 TABLE_OWNER                                                    VARCHAR2(30)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 DB_LINK                                                        VARCHAR2(128)

scott_pd@ORCL> select synonym_name, table_name, table_owner from all_synonyms where table_owner='SCOTT' and table_name='EMP';

SYNONYM_NAME
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
TABLE_OWNER
------------------------------------------------------------
SCOTTEMP
EMP
SCOTT


scott_pd@ORCL> col synonym_name, table_name, table_owner for a20';
SP2-0735: 未知的 COLUMN 选项开头 "table_name..."
scott_pd@ORCL> col synonym_name for a20';
SP2-0246: 非法的 FORMAT 字符串"a20'"
scott_pd@ORCL> col synonym_name, table_name, table_owner for a20;
SP2-0735: 未知的 COLUMN 选项开头 "table_name..."
scott_pd@ORCL> col synonym_name for a20;
scott_pd@ORCL> col table_name for a20;
scott_pd@ORCL> col table_owner for a20;
scott_pd@ORCL> select synonym_name, table_name, table_owner from all_synonyms where table_owner='SCOTT' and table_name='EMP';

SYNONYM_NAME         TABLE_NAME           TABLE_OWNER
-------------------- -------------------- --------------------
SCOTTEMP             EMP                  SCOTT

scott_pd@ORCL> commit;

提交完成。

scott_pd@ORCL> spool out