如何理解同義詞synonym

本篇文章給大家分享的是有關如何理解同義詞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，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。