oracle9204更改字符集步骤方法
来源:互联网 发布:淘宝卖家怎么注销 编辑:程序博客网 时间:2024/04/18 21:48
oracle9204更改字符集步骤方法(WE8ISO8859P1->ZHS16GBK):
由于我们经常做不同数据库的导出导入时遇到的服务器的字符集不同,导致我们原有的无法正常导入工作,
这时需要我们做数据库字符集的更改工作,
字符集是在安装数据库时选定的,一旦数据库创建之后,数据库的字符集是不能改变的,如果要改变数据库的字符集, 只有重新创建数据库。
但通常也可以在特定的条件下通过一些其他途径来完成字符集的转换(这里强烈建议创建数据库时就要考虑好字符集)
单字节=>单字节
多字节=>多字节
且是子集到超集时,可以用alter database CHARACTER set 新的字符集;
通常对于单字节向多字节转换和非子集到超集转换时采用exp/imp+internal_use的方法
这里只介绍WE8ISO8859P1->ZHS16GBK的步骤方法:
检查当前的字符集为WE8ISO8859P1:
SQL> set wrap off
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET WE8ISO8859P1
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
SQL>
将数据库启动到RESTRICTED模式下做字符集更改:
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 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 ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
提示我们的字符集:新字符集必须为旧字符集的超集,这时我们可以跳过超集的检查做更改:
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
Database altered.
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
重启检查是否更改完成:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
SQL>
- oracle9204更改字符集步骤方法
- oracle9204更改字符集步骤方法
- oracle9204更改字符集步骤方法
- oracle server端更改字符集方法
- MyEclipse2016更改字符集设置的方法
- mysql 查看字符集, 更改字符集
- oracle 更改服务端字符集 更改oracle字符集
- SQL 字符集的更改
- 学习字符集的更改
- 更改MySQL默认字符集
- Oracle更改字符集 ZHS16GBK
- oracle更改字符集
- Oracle字符集更改
- 字符集的更改
- 更改oracle字符集
- ORACLE11G 字符集更改
- Oracle10g更改数据库字符集
- oracle9-10更改字符集
- win2003 network load balance
- Windows Deployment and Resource Kits
- Linux:让内存不再泄漏
- dba对redolog的文件及其使用率的检查调整
- 处理ora-00312的错误(磁盘空间导致00312错误)
- oracle9204更改字符集步骤方法
- linux中top的隐含参数秘密
- 用netstat命令发现大量的TIME_WAIT现象
- 对netstat中出现warning, got duplicate tcp line的处理:
- 使用logminer做redolog的分析(做在线日志分析)
- Red hat AS4开启telnet过程 :
- BIOS与CMOS区别
- 关于redhat上iscsi的安装:iSCSI target HowTo on Enterprise Linux (RHEL4)
- 今天我在CSDN的BLOG开通了