在本机安装Oracle Express精简版,为了占用更少的内存,使用时再启动,配置本机TNS:
---listener.ora 服务器监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = F:\Oracle\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC = //这里重点配置的
(GLOBAL_DBNAME = XE)
(ORACLE_HOME = F:\Oracle\app\oracle\product\11.2.0\server)
(SID_NAME = XE)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
---tnsname.ora 客户端连接
SENORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(INSTANCE_NAME = XE)
(SERVICE_NAME = XE)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
(INSTANCE_NAME = ORCL)
)
)
这样配置后,启动Oracle服务和TNSListener就可以使用客户端连接了。
---------
在实际开发中,需要连接服务器端,而本地数据库不需要启动,我们使用工具连接,PL/SQL可以使用instantclient来配置tnsname,只需要将instantclient放到PL/SQL根目录下,然后配置环境变量,增加以下两个变量:
TNS_ADMIN D:\Program Files\PLSQL Developer 9.04\instantclient
NLS_LANG SIMPLIFIED CHINESE_CHINA.ZHS16GBK
然后在instantclient中的tnsname.ora中配置连接信息就可以了。
---------
配置了上面的TNS_ADMIN后,Oracle不启动的情况下默认使用instantclient解析TNS,它便可以连接远程库了。Oracle启动后使用本地的TNSListener解析TNS,但它仍然加载instantclient下的配置文件tnsname.ora,远程库和本地库都就可以连接了。这样我们将所有的TNS配置都写在instantclient下的tnsname.ora中就可以了。
PL/SQL(instantclient三个环境变量):
NLS_LANG SIMPLIFIED CHINESE_CHINA.ZHS16GBK
TNS_ADMIN D:\Program Files\PLSQL Developer 9.04\instantclient
ORACLE_HOME D:\Program Files\PLSQL Developer 9.04\instantclient(本地安装Oracle后,可能有冲突like '%ORA-15277%',可以去掉这一个)