Shulou(Shulou.com)05/31 Report--

This article is about how to understand the synonym synonym. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

-- Target library operation: 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 (*)

-

thirty-five

-- Source database operation: 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 (*)

-

thirty-five

SQL > select count (*) from coe

COUNT (*)

-

thirty-five

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 select object_name,object_id,object_type,status from coe where rownum

[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

Query validation is exactly the same, with a synonym co and coe!

The above is how to understand the synonym synonym. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.