同义词:方案对象的别名,它不占用存储的空间,目的是在Oracle中为表、视图、序列、PL/SQL程序单元、用户自定义对象或其他的同义词创建友好的名称。
主要作用:
1. 安全性——防止暴露太多的信息给开发人员(无需使用类似[email protected]的格式)
2. 降低复杂环境的出错率
[email protected]> CREATE PUBLIC SYNONYM scottemp FOR scott.emp;
CREATE PUBLIC SYNONYM scottemp FOR scott.emp
*
第 1 行出现错误:
ORA-01031: 权限不足
[email protected]> conn sys as sysdba
已连接。
GLOBAL_NAME
----------------------------------------------------------------------------------------------------
[email protected]
[email protected]> CREATE PUBLIC SYNONYM scottemp FOR scott.emp;
同义词已创建。
[email protected]> conn scott/tiger
已连接。
GLOBAL_NAME
----------------------------------------------------------------------------------------------------
[email protected]
[email protected]> selecc count(*) from scottemp;
SP2-0734: 未知的命令开头 "selecc cou..." - 忽略了剩余的行。
[email protected]> select count(*) from scottemp;
COUNT(*)
----------
23
[email protected]> conn sys as sysdba
已连接。
GLOBAL_NAME
----------------------------------------------------------------------------------------------------
[email protected]
[email protected]> grant scott create pubic synonym;
grant scott create pubic synonym
*
第 1 行出现错误:
ORA-00990: 权限缺失或无效
[email protected]> grant create pubic synonym to scott;
grant create pubic synonym to scott
*
第 1 行出现错误:
ORA-00990: 权限缺失或无效
[email protected]> grant create public synonym to scott;
授权成功。
[email protected]> grant drop public synoym to scott;
grant drop public synoym to scott
*
第 1 行出现错误:
ORA-00990: 权限缺失或无效
[email protected]> grant drop public synonym to scott;
授权成功。
[email protected]> conn scott/tiger;
已连接。
GLOBAL_NAME
----------------------------------------------------------------------------------------------------
[email protected]
[email protected]> drop public synonym scottemp;
同义词已删除。
[email protected]> select count(*) from scottemp;
select count(*) from scottemp
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
[email protected]> CREATE PUBLIC SYNONYM scottemp FOR scott.emp;
同义词已创建。
[email protected]> desc user_synonyms;
名称 是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
[email protected]> 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
[email protected]> col synonym_name, table_name, table_owner for a20';
SP2-0735: 未知的 COLUMN 选项开头 "table_name..."
[email protected]> col synonym_name for a20';
SP2-0246: 非法的 FORMAT 字符串"a20'"
[email protected]> col synonym_name, table_name, table_owner for a20;
SP2-0735: 未知的 COLUMN 选项开头 "table_name..."
[email protected]> col synonym_name for a20;
[email protected]> col table_name for a20;
[email protected]> col table_owner for a20;
[email protected]> 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
[email protected]> commit;
提交完成。
[email protected]> spool out