在本机安装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%',可以去掉这一个)