更改数据库编码格式

更改数据库编码格式 --查看oracle数据库字符集: select userenv('language') from dual; 查询结果: SIMPLIFIED CHINESE_CHINA.AL32UTF8 --修改oracle数据库字符集:(在SQL Plus中) sql> conn / as sysdba; sql> shutdown immediate; database closed. database dismounted. oracle instance shut down. sql> startup mount; oracle instance started. total system global area 135337420 bytes fixed size 452044 bytes variable size 109051904 bytes database buffers 25165824 bytes redo buffers 667648 bytes database mounted. sql> alter system enable restricted session; system altered. sql> alter system set job_queue_processes=0; system altered. sql> alter system set aq_tm_processes=0; system altered. sql> alter database open; database altered. sql> alter database character set internal_use JA16SJIS; sql> shutdown immediate; sql> startup; 其中,SERVICE_NAME 未SID的名称 非数据库名称 ORA-12518: TNS: 监听程序无法分发客户机连接 ORACLE 2008-07-12 13:25:55 阅读395 评论0 字号:大中小 今天在自己的T61上装了ORACLE10G，成功装好后，连数据库报如下错误: SQL> conn sys/sys@my10g as sysdba ERROR: ORA-12518: TNS: 监听程序无法分发客户机连接 SQL> conn sjh/sjh@my10g ERROR: ORA-12518: TNS: 监听程序无法分发客户机连接 C:\Documents and Settings\is_sjh>tnsping my10g TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-3月 - 2008 19:59:46 Copyright (c) 1997, 2005, Oracle. All rights reserved. 已使用的参数文件: c:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracsjh))) OK (20 毫秒) TNSNAMES.ORA的内容如下: MY10G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = oracsjh) ) ) listner.ora的内容如下: # listener.ora Network Configuration File: c:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = c:\oracle\product\10.2.0\db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = oracsjh) (ORACLE_HOME = c:\oracle\product\10.2.0\db_1) (SID_NAME = oracsjh) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = WWW-D413A159A8B.nj.chervon.com)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) ) 一再检查没发现什么问啊，后来用OS认证的方式连到DB上一看才知道自己犯了一个很低级的错误。INSTANCE NAME跟两个配置文件的配置不一样。将oracsjh改为跟数据库INSTANCE名一样:sjhorac 问题解决。为了证实自己的想法，我又让问题重现: TNSNAMES.ORA的内容如下: MY10G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ABC) ) ) listner.ora的内容如下: # listener.ora Network Configuration File: c:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = c:\oracle\product\10.2.0\db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = ABC) (ORACLE_HOME = c:\oracle\product\10.2.0\db_1) (SID_NAME = ABC) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = WWW-D413A159A8B.nj.chervon.com)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) ) 将SERVICE_NAME ，GLOBAL_DBNAME ，SID_NAME 改为ABC。重起LISTENER。 TNSPING依然是可以的。 C:\Documents and Settings\is_sjh>tnsping my10g TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-3月 - 2008 20:47:52 Copyright (c) 1997, 2005, Oracle. All rights reserved. 已使用的参数文件: c:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORAC))) OK (10 毫秒) --问题重现: SQL> conn sjh/sjh@my10g ERROR: ORA-12518: TNS: 监听程序无法分发客户机连接 总结:ORACLE网络配置这块要特别小心，大多数错误都是由这些配置文件没有配置好而引起的。当然也有些比较特殊的情况。在解决问题的过程中看了一些资料很值的参考: