修改数据库字符集

数据库导入导出的时候经常会提示字符集不同，可以通过下面方法修改数据库字符集：

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; ALTER DATABASE character set INTERNAL_USE zhs16gbk * ERROR at line 1: ORA-12719: operation requires database is in RESTRICTED mode SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSE '; VALUE ------------------------------------------------------------------------------- AL32UTF8 SQL> conn sys/system as sysdba Connected. SQL> startup ORA-01081: cannot start already-running ORACLE - shut it down first SQL> select name,value$ from props$ where name like '%NLS%'; NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET AL32UTF8 NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 10.2.0.4.0 20 rows selected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 2066080 bytes Variable Size 385878368 bytes Database Buffers 1207959552 bytes Redo Buffers 14708736 bytes Database mounted. SQL> alter session set sql_trace=true; Session altered. 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> set linesize 120; 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> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> shutdown immediate; ORA-01012: not logged on SQL> STARTUP ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 2066080 bytes Variable Size 385878368 bytes Database Buffers 1207959552 bytes Redo Buffers 14708736 bytes Database mounted. Database opened. SQL> select name,value$ from props$ where name like '%NLS%'; NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- ---------------------------------------- NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET ZHS16GBK NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- ---------------------------------------- NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- ---------------------------------------- NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- ---------------------------------------- NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- ---------------------------------------- NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NAME ------------------------------ VALUE$ ------------------------------------------------------------------------------- ---------------------------------------- NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 10.2.0.4.0 20 rows selected. SQL>