dblink、同义词
//dblink
select * from user_sys_privs t where t.privilege like upper('%link%'); //查看link相关的权限名称
select DB_LINK from all_db_links;
create public database link db5link connect to scott identified by tiger using 'DB5LINK';
SQL> select * from emp@DB5LINK;
SQL> desc emp5@DB5LINK;
SELECT * FROM SYS.link$; //sys用户登录可以看到dblink用户密码
DROP [PUBLIC] DATABASE LINK DB5LINK
//同义词,如果公有同义词和表同时存在,优先访问的是表
grant create [public] synonym to scott;//给与创建同义词的权限
revoke create synonym from scott; #撤销用户创建同义词的权限
grant select on YPGT_SBYD_GIS.T_YD_BYQ_JRD to APPS;//必须给表的select权限
create synonym emps for emp5@DB5LINK;
create or replace synonym APPS.T_YD_BYQ_JRD for YPGT_SBYD_GIS.T_YD_BYQ_JRD;
create synonym APPS.T_YD_JRD_JLX for YPGT_SBYD_GIS.T_YD_JRD_JLX;
Drop synonym syn_name #删除同义词
select * from dba_synonyms where owner = 'APPS';
select table_name,table_owner from dba_synonyms where owner='APPS' and table_name='T_YD_JRD_JLX';
Oracle同义词
同义词是数据库中对象的别名,出于发方便用户访问,隐藏对象的身份,可以是私有,也可以是公共的。
同义词可以为表、视图、实体化视图和存储代码(如程序包及过程)创建同义词。
访问其他用户的公共同义词、私有同义词,都需要源表的select权限。
与同义词相关的权限有CREATE SYNONYM、CREATE ANY SYNONYM、CREATE PUBLIC SYNONYM权限。
用户在自己的模式下创建私有同义词,这个用户必须拥有CREATE SYNONYM权限,否则不能创建私有同义词。
1.公共同义词
公共同义词通常是由应用的所有者为表和其他对象创建的,可以为数据库中的所有用户引用。
SQL>conn / as sysdba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdborcl;
SQL> grant create public synonym to hr;
SQL> conn hr/hr@pdborcl
SQL> create public synonym emps for hr.employees;
SQL> grant select on employees to scott;
SQL> conn scott/tiger@pdborcl
SQL> select * from emps;
//其他用户下表和同义词同名时,优先访问表的数据
2.私有同义词
私有同义词与公共同义词不同,只能由拥有表或者对象的模式引用,
SQL> show user;
USER is "HR"
SQL> create synonym myjobs for jobs;
Synonym created.
SQL> show user;
USER is "SCOTT"
SQL> select * from hr.myjobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 30006000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 42009000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 42009000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 25005500
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN Stock Manager 55008500
ST_CLERK Stock Clerk 20085000
SH_CLERK Shipping Clerk 25005500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 40009000
HR_REP Human Resources Representative 40009000
PR_REP Public Relations Representative 4500 10500
19 rows selected.
SQL> select * from myjobs;
select * from myjobs
*
ERROR at line 1:
ORA-00942: table or view does not exist
3 删除同义词
drop public synonym 同义词名;//删除公共同义词
drop synonym 同义词名; //删除私有同义词
4.访问方式
公共同义词不在user_synonyms视图中,私有同义词会出现在user_synonyms视图里,两者都在dba_synonyms里。
//同义词相关视图
SQL>show user;
User is "SYS"
SQL>set linesize 1000
col owner format a10
col synonym_name format a15
col table_name format a15
col table_owner format a15
col db_link format a10
col ORIGIN_CON_ID format 99
SQL> select * from dba_synonyms where table_owner='HR';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ORIGIN_CON_ID
---------- -------------------- -------------------- -------------------- -------------------- --------------------
PUBLIC EMPS HR EMPLOYEES 3
HR MYJOBS HR JOBS 3
PUBLIC EMP3 HR EMPLOYEES 3
PUBLIC JOB123 HR JOBS 3
SQL> show user;
USER is "HR"
SQL> select * from user_synonyms where table_owner='HR';
SYNONYM_NAMETABLE_OWNER TABLE_NAME DB_LINK ORIGIN_CON_ID
--------------- --------------- --------------- ---------- -------------
MYJOBS HR JOBS 3
//公共同义词
SQL> select * from job123;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 30006000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 42009000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 42009000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 25005500
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN Stock Manager 55008500
ST_CLERK Stock Clerk 20085000
SH_CLERK Shipping Clerk 25005500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 40009000
HR_REP Human Resources Representative 40009000
PR_REP Public Relations Representative 4500 10500
19 rows selected.
SQL> select * from hr.job123;
select * from hr.job123
*
ERROR at line 1:
ORA-00942: table or view does not exist
//私有同义词
SQL> select * from hr.myjobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 30006000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 42009000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 42009000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 25005500
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN Stock Manager 55008500
ST_CLERK Stock Clerk 20085000
SH_CLERK Shipping Clerk 25005500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 40009000
HR_REP Human Resources Representative 40009000
PR_REP Public Relations Representative 4500 10500
19 rows selected.
SQL> select * from myjobs;
select * from myjobs
*
ERROR at line 1:
ORA-00942: table or view does not exist
访问公共同义词不加用户名,加了会报错。
访问私有同义词要加用户名,不加会报错。