tnsnames.ora 檔案預設在 network/admin/tnsnames.ora
有些情況如你沒有權限修改network/admin/tnsnames.ora, 你可以在$HOME下創建一個.tnsnames.ora檔案
[oracle@orcl admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oral.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.example.com)
)
)
測試TNS
$ sqlplus sys/chen@orcl
HOST可以使用IP地址 HOST = 192.168.0.5 建議改為hostname 例如HOST = oral.example.com這樣方便伺服器更換IP。
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.5)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.example.com)
)
)
SERVICE_NAME通過show parameter service_name;查詢
$ sqlplus user@orcl
Oracle 10G 之前多採用 SID = oradb
10G之後更多使用 SERVICE_NAME = orcl.example.com
oradb10g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = oradb10g)
)
)
oradb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = oradb)
)
)