本篇文章给大家分享的是有关如何理解同义词synonym，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
--目标库操作：192.168.56.11
[oracle@rhel ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 23 17:20:05 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CONN hr/hr;
Connected.
SQL> create table syno as select * from user_objects;
Table created.
SQL>
SQL> select count(*) from syno;
COUNT(*)
----------
35
--源库操作：192.168.56.12
[oracle@wang admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 8 15:40:15 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn hr/hr;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
B_FILE_PACKAGE TABLE
B_PACKAGE_STATE_TRANS TABLE
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMPLOYEES_BAK TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
11 rows selected.
SQL> create database link ora connect to hr identified by hr using 'orcl';
Database link created.
SQL> create synonym co for syno@ora;
Synonym created.
SQL> create synonym coe for hr.syno@ora;
Synonym created.
SQL> select count(*) from co;
COUNT(*)
----------
35
SQL> select count(*) from coe;
COUNT(*)
----------
35
SQL> desc co
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL>
SQL> desc coe
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> select object_name,object_id,object_type,status from co where rownum <=2;
OBJECT_NAM OBJECT_ID OBJECT_TYPE STATUS
---------- ---------- -------------------------------------- --------------
REGIONS 51846 TABLE VALID
REG_ID_PK 51847 INDEX VALID
SQL> select object_name,object_id,object_type,status from coe where rownum <=2;
OBJECT_NAM OBJECT_ID OBJECT_TYPE STATUS
---------- ---------- -------------------------------------- --------------
REGIONS 51846 TABLE VALID
REG_ID_PK 51847 INDEX VALID
SQL>
[oracle@wang admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
[oracle@wang admin]$
[oracle@wang admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 08-DEC-2017 17:27:17
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (LOAD_BALANCE = yes) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (10 msec)
[oracle@wang admin]$
SQL> select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME,DB_LINK from dba_synonyms where SYNONYM_NAME in ('CO11','COE11');
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------
HR CO SYNO ORA
HR COE HR SYNO ORA
SQL> col HOST for a20
SQL> select * from dba_db_links where DB_LINK='ORA';
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ --------------- ------------------------------ -------------------- ------------
HR ORA HR orcl 08-DEC-17
查询验证完全相同，连个同义词co和coe！
以上就是如何理解同义词synonym，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。
