\u4fee\u6539\u5b57\u7b26\u96c6\uff08ZHS16GBK\u5230UTF-8\uff09<\/p>\n
\u6570\u636e\u5e93\u7248\u672c<\/p>\n
\r\nSQL> set line 400\r\nSQL> select * from gV$version;\r\n\r\n INST_ID BANNER\r\n---------- --------------------------------------------------------------------------------\r\n 1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\n 1 PL\/SQL Release 11.2.0.3.0 - Production\r\n 1 CORE 11.2.0.3.0 Production\r\n 1 TNS for Linux: Version 11.2.0.3.0 - Production\r\n 1 NLSRTL Version 11.2.0.3.0 - Production\r\n 2 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\n 2 PL\/SQL Release 11.2.0.3.0 - Production\r\n 2 CORE 11.2.0.3.0 Production\r\n 2 TNS for Linux: Version 11.2.0.3.0 - Production\r\n 2 NLSRTL Version 11.2.0.3.0 - Production\r\n\r\n10 rows selected.\r\n<\/pre>\n\u6570\u636e\u5e93\u5b57\u7b26\u96c6<\/p>\n
\r\nSQL> select userenv('language') from dual;\r\n\r\nUSERENV('LANGUAGE')\r\n----------------------------------------------------\r\nAMERICAN_AMERICA.ZHS16GBK\r\n\r\nSQL> select * from nls_database_parameters;\r\n\r\nPARAMETER VALUE\r\n------------------------------ --------------------------------------------------------------------------------\r\nNLS_LANGUAGE AMERICAN\r\nNLS_TERRITORY AMERICA\r\nNLS_CURRENCY $\r\nNLS_ISO_CURRENCY AMERICA\r\nNLS_NUMERIC_CHARACTERS .,\r\nNLS_CHARACTERSET ZHS16GBK\r\nNLS_CALENDAR GREGORIAN\r\nNLS_DATE_FORMAT DD-MON-RR\r\nNLS_DATE_LANGUAGE AMERICAN\r\nNLS_SORT BINARY\r\nNLS_TIME_FORMAT HH.MI.SSXFF AM\r\n\r\nPARAMETER VALUE\r\n------------------------------ --------------------------------------------------------------------------------\r\nNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM\r\nNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR\r\nNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR\r\nNLS_DUAL_CURRENCY $\r\nNLS_COMP BINARY\r\nNLS_LENGTH_SEMANTICS BYTE\r\nNLS_NCHAR_CONV_EXCP FALSE\r\nNLS_NCHAR_CHARACTERSET AL16UTF16\r\nNLS_RDBMS_VERSION 11.2.0.3.0\r\n\r\n20 rows selected.\r\n<\/pre>\n\u6570\u636e\u5e93\u7528\u6237\u73af\u5883\u53d8\u91cf<\/p>\n
\r\n[oracle@11grac1 ~]$ env | grep NLS_LANG\r\nNLS_LANG=AMERICAN_AMERICA.zhs16gbk\r\n<\/pre>\nOS\u5b57\u7b26\u96c6\u8bbe\u7f6e<\/p>\n
\r\n[root@11grac1 ~]# cat \/etc\/sysconfig\/i18n\r\nLANG="zh_CN.UTF-8"\r\n<\/pre>\n\u6d4b\u8bd5\u6570\u636e<\/p>\n
\r\nSQL> conn test\/test\r\nConnected.\r\n\r\nSQL> create table yallonking (id number,varchar_col1 varchar2(40),nvarchar_col2 nvarchar2(40));\r\n\r\nTable created.\r\n\r\nSQL> desc yallonking;\r\n Name Null? Type\r\n ----------------------------------------- -------- ----------------------------\r\n ID NUMBER\r\n VAR_COL1 VARCHAR2(40)\r\n NVAR_COL2 NVARCHAR2(40)\r\n\r\nSQL> select * from yallonking;\r\n\r\n ID VARCHAR_COL1 NVARCHAR_COL2\r\n---------- ---------------------------------------- --------------------------------------------------------------------------------\r\n 1 \u6211\u662f\u4e2d\u6587\u5b57\u7b26 \u6211\u662f\u4e2d\u6587\u5b57\u7b26\r\n 2 i am english char i am english nvarchar\r\n 3 \u6211\u662f\u4e2d\u6587\u5206\u53f7\uff1b \u6211\u662f\u4e2d\u6587\u5206\u53f7\uff1b\r\n 4 i am english char ; i am english char ;\r\n 5 \u6211\u662f\u4e2d\u6587\u6570\u5b57123 \u6211\u662f\u4e2d\u6587\u6570\u5b57123\r\n 6 i am english number 123 i am english number 123\r\n\r\n6 rows selected.\r\n\r\n<\/pre>\n\u4e0b\u8fb9\u6d4b\u8bd5\u5c06\u6570\u636e\u5e93\u5b57\u7b26\u96c6\u8f6c\u5316\u4e3aUTF-8<\/p>\n
\r\n[oracle@11grac1 ~]$ csscan system\/oracle FULL=Y FROMCHAR=ZHS16GBK TOCHAR=UTF8 LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2\r\n\r\n\r\nCharacter Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Mon Jun 17 18:42:38 2013\r\n\r\nCopyright (c) 1982, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n\r\nCSS-00107: Character set migration utility schema not installed\r\n\r\nScanner terminated unsuccessfully.\r\n<\/pre>\n\u5b89\u88c5csscan<\/p>\n
\r\n[oracle@11grac1 ~]$ exit\r\nexit\r\n\r\nSQL> show user\r\nUSER is "TEST"\r\nSQL> conn \/as sysdba\r\nConnected.\r\nSQL> @?\/rdbms\/admin\/csminst.sql\r\n\r\n\u7701\u7565\u90e8\u5206\u8f93\u51fa\r\nGrant succeeded.\r\n\r\n\r\nGrant succeeded.\r\n\r\nDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n\r\n<\/pre>\n\u8bed\u6cd5\uff1a<\/p>\n
\r\n[oracle@11grac1 ~]$ csscan help =y\r\n\r\n\r\nCharacter Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Mon Jun 17 19:56:41 2013\r\n\r\nCopyright (c) 1982, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\n\r\nYou can let Scanner prompt you for parameters by entering the CSSCAN \r\ncommand followed by your username\/password: \r\n \r\n Example: CSSCAN \\"SYSTEM\/MANAGER AS SYSDBA\\" \r\n \r\nOr, you can control how Scanner runs by entering the CSSCAN command \r\nfollowed by various parameters. To specify parameters, you use keywords: \r\n \r\n Example: \r\n CSSCAN \\"SYSTEM\/MANAGER AS SYSDBA\\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3 \r\n \r\nKeyword Default Prompt Description \r\n---------- ------- ------ ------------------------------------------------- \r\nUSERID yes username\/password \r\nFULL N yes scan entire database \r\nUSER yes owner of tables to be scanned \r\nTABLE yes list of tables to scan \r\nCOLUMN yes list of columns to scan \r\nEXCLUDE list of tables to exclude from scan \r\nTOCHAR yes new database character set name \r\nFROMCHAR current database character set name \r\nTONCHAR new national character set name \r\nFROMNCHAR current national character set name \r\nARRAY 1024000 yes size of array fetch buffer \r\nPROCESS 1 yes number of concurrent scan process \r\nMAXBLOCKS split table if block size exceed MAXBLOCKS \r\nCAPTURE N capture convertible data \r\nSUPPRESS maximum number of exceptions logged for each table\r\nFEEDBACK report progress every N rows \r\nBOUNDARIES list of column size boundaries for summary report \r\nLASTRPT N generate report of the last database scan \r\nLOG scan base file name of report files \r\nPARFILE parameter file name \r\nPRESERVE N preserve existing scan results \r\nLCSD N no enable language and character set detection \r\nLCSDDATA LOSSY no define the scope of the detection \r\nHELP N show help screen (this screen) \r\nQUERY N select clause to scan subset of tables or columns \r\n---------- ------- ------ ------------------------------------------------- \r\nScanner terminated successfully.\r\n\r\n<\/pre>\n\r\n[oracle@11grac1 ~]$ csscan \\"sys\/oracle as sysdba\\" FULL=Y\r\n\r\n\r\nCharacter Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Mon Jun 17 20:35:34 2013\r\n\r\nCopyright (c) 1982, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n\r\nCurrent database character set is ZHS16GBK.\r\n\r\nEnter new database character set name: > UTF8\r\n\r\nEnter array fetch buffer size: 1024000 > 1024000\r\n\r\nEnter number of scan processes to utilize(1..): 1 > 10\r\n\r\nEnumerating tables to scan...\r\n\r\n. process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]\r\n. process 5 scanning SYS.SOURCE$[AAAADgAABAAASyAAAA]\r\n... ...\r\n. process 4 scanning CTXSYS.DR$INDEX_CDI_COLUMN\r\n. process 3 scanning CTXSYS.DR$SECTION_ATTRIBUTE\r\n. process 9 scanning EXFSYS.RLM$RULESETSTCODE\r\n. process 8 scanning EXFSYS.RLM$RULESET\r\n\r\nCreating Database Scan Summary Report...\r\n\r\nCreating Individual Exception Report...\r\n\r\nScanner terminated successfully.\r\n\r\n<\/pre>\n\r\n[oracle@11grac1 ~]$ ls\r\nscan.err scan.out scan.txt\r\n[oracle@11grac1 ~]$ more scan.err\r\nDatabase Scan Individual Exception Report\r\n\r\n\r\n[Database Scan Parameters]\r\n\r\nParameter Value \r\n------------------------------ ------------------------------------------------\r\nCSSCAN Version v2.1 \r\nInstance Name racdb1 \r\nDatabase Version 11.2.0.3.0 \r\nScan type Full database \r\nScan CHAR data? YES \r\nDatabase character set ZHS16GBK \r\nFROMCHAR ZHS16GBK \r\nTOCHAR UTF8 \r\nScan NCHAR data? NO \r\nArray fetch buffer size 1024000 \r\nNumber of processes 10 \r\nCapture convertible data? NO \r\n------------------------------ ------------------------------------------------\r\n\r\n[Data Dictionary individual exceptions]\r\n\r\n\r\n[Application data individual exceptions]\r\n<\/pre>\n\u6ce8\u610f\uff1a\u8be5\u6587\u4ef6\u4e0d\u80fd\u6709\u62a5\u9519\u3002<\/p>\n
\r\n[oracle@11grac1 ~]$ more scan.txt\r\nDatabase Scan Summary Report\r\n\r\nTime Started : 2013-06-17 20:36:47\r\nTime Completed: 2013-06-17 20:41:07\r\n\r\nProcess ID Time Started Time Completed\r\n---------- -------------------- --------------------\r\n 1 2013-06-17 20:38:46 2013-06-17 20:41:04\r\n 2 2013-06-17 20:38:56 2013-06-17 20:41:04\r\n 3 2013-06-17 20:39:00 2013-06-17 20:41:04\r\n 4 2013-06-17 20:38:58 2013-06-17 20:41:04\r\n 5 2013-06-17 20:38:58 2013-06-17 20:41:04\r\n 6 2013-06-17 20:38:56 2013-06-17 20:41:04\r\n 7 2013-06-17 20:38:56 2013-06-17 20:41:04\r\n 8 2013-06-17 20:38:57 2013-06-17 20:41:04\r\n 9 2013-06-17 20:38:57 2013-06-17 20:41:04\r\n 10 2013-06-17 20:38:57 2013-06-17 20:41:04\r\n---------- -------------------- --------------------\r\n\r\n[Database Size]\r\n\r\nTablespace Used Free Total Expansion\r\n------------------------- --------------- --------------- --------------- ---------------\r\nSYSTEM 706.81M 3.19M 710.00M .00K\r\nSYSAUX 558.56M 31.44M 590.00M .00K\r\nUNDOTBS1 16.13M 58.88M 75.00M .00K\r\nTEMP .00K .00K .00K .00K\r\nUSERS 1.31M 3.69M 5.00M .00K\r\nUNDOTBS2 20.25M 4.75M 25.00M .00K\r\nOGG 2.38M 47.63M 50.00M .00K\r\nTEST 1.13M 48.88M 50.00M .00K\r\n------------------------- --------------- --------------- --------------- ---------------\r\nTotal 1,306.56M 198.44M 1,505.00M .00K\r\n\r\n[Database Scan Parameters]\r\n\r\nParameter Value \r\n------------------------------ ------------------------------------------------\r\nCSSCAN Version v2.1 \r\nInstance Name racdb1 \r\nDatabase Version 11.2.0.3.0 \r\nScan type Full database \r\nScan CHAR data? YES \r\nDatabase character set ZHS16GBK \r\nFROMCHAR ZHS16GBK \r\nTOCHAR UTF8 \r\nScan NCHAR data? NO \r\nArray fetch buffer size 1024000 \r\nNumber of processes 10 \r\nCapture convertible data? NO \r\n------------------------------ ------------------------------------------------\r\n\r\n[Scan Summary]\r\n\r\nAll character type data in the data dictionary remain the same in the new character set\r\nAll character type application data are convertible to the new character set\r\n\r\n[Data Dictionary Conversion Summary]\r\n\r\nData Dictionary Tables:\r\n\r\nDatatype Changeless Convertible Truncation Lossy\r\n--------------------- ---------------- ---------------- ---------------- ----------------\r\nVARCHAR2 4,110,195 0 0 0\r\nCHAR 3,062 0 0 0\r\nLONG 249,066 0 0 0\r\nVARRAY 49,807 0 0 0\r\n--------------------- ---------------- ---------------- ---------------- ----------------\r\nTotal 4,412,130 0 0 0\r\nTotal in percentage 100.000% 0.000% 0.000% 0.000%\r\n\r\nThe data dictionary can be safely migrated using the CSALTER script\r\n\r\nXML CSX Dictionary Tables:\r\n\r\nDatatype Changeless Convertible Truncation Lossy\r\n--------------------- ---------------- ---------------- ---------------- ----------------\r\nVARCHAR2 702 0 0 0\r\nCHAR 0 0 0 0\r\nLONG 0 0 0 0\r\nVARRAY 0 0 0 0\r\n--------------------- ---------------- ---------------- ---------------- ----------------\r\nTotal 702 0 0 0\r\nTotal in percentage 100.000% 0.000% 0.000% 0.000%\r\n\r\n[Application Data Conversion Summary]\r\n\r\nDatatype Changeless Convertible Truncation Lossy\r\n--------------------- ---------------- ---------------- ---------------- ----------------\r\nVARCHAR2 2,551,951 3 0 0\r\nCHAR 394 0 0 0\r\nLONG 0 0 0 0\r\nVARRAY 0 0 0 0\r\n--------------------- ---------------- ---------------- ---------------- ----------------\r\nTotal 2,552,345 3 0 0\r\nTotal in percentage 100.000% 0.000% 0.000% 0.000%\r\n\r\n[Distribution of Convertible, Truncated and Lossy Data by Table]\r\n\r\nData Dictionary Tables:\r\n\r\nUSER.TABLE Convertible Truncation Lossy\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n\r\nXML CSX Dictionary Tables:\r\n\r\nUSER.TABLE Convertible Truncation Lossy\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n\r\nApplication Data:\r\n\r\nUSER.TABLE Convertible Truncation Lossy\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\nTEST.YALLONKING 3 0 0\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n\r\n[Distribution of Convertible, Truncated and Lossy Data by Column]\r\n\r\nData Dictionary Tables:\r\n\r\nUSER.TABLE|COLUMN Convertible Truncation Lossy\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n\r\nXML CSX Dictionary Tables:\r\n\r\nUSER.TABLE|COLUMN Convertible Truncation Lossy\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n\r\nApplication Data:\r\n\r\nUSER.TABLE|COLUMN Convertible Truncation Lossy\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\nTEST.YALLONKING|VARCHAR_COL1 3 0 0\r\n-------------------------------------------------- ---------------- ---------------- ----------------\r\n\r\n[Indexes to be Rebuilt]\r\n\r\nUSER.INDEX on USER.TABLE(COLUMN) \r\n-----------------------------------------------------------------------------------------\r\n-----------------------------------------------------------------------------------------\r\n<\/pre>\n\u53ea\u9700\u8981\u5173\u5fc3\u8f93\u51fa\u7684\u6587\u4ef6dbcheck.txt\u4e2d[Scan Summary]\u90e8\u5206\u7684\u63cf\u8ff0\uff0c\u4ee5\u53ca[Data Dictionary Conversion Summary]\u90e8\u5206\u7684\u63a8\u8350\u65b9\u6cd5\u3002
\n\u5f53\u7136dbcheck.err\u6587\u4ef6\u4e2d\u4e0d\u80fd\u6709\u62a5\u9519\u4fe1\u606f\u3002
\n\u6b64\u5904\u53ef\u4ee5\u76f4\u63a5\u4fee\u6539\u6570\u636e\u5e93\u5b57\u7b26\u96c6<\/p>\nIn 10g and 11g the \"ALTER DATABASE CHARACTER SET\" command is NOT to be used any more but Csscan\/Csalter is the only supported way to change a database characterset.<\/strong><\/p>\n
\u5173\u95ed\u6240\u6709\u5b9e\u4f8b
\n\u542f\u52a8\u4e00\u4e2a\u5b9e\u4f8b\u5230mount<\/p>\n\r\nSQL> shutdown immediate;\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\nSQL> startup restrict\r\nORACLE instance started.\r\n\r\nTotal System Global Area 839282688 bytes\r\nFixed Size 2233000 bytes\r\nVariable Size 629149016 bytes\r\nDatabase Buffers 205520896 bytes\r\nRedo Buffers 2379776 bytes\r\nDatabase mounted.\r\nDatabase opened.\r\nSQL> @?\/rdbms\/admin\/csalter.plb\r\n\r\n0 rows created.\r\n\r\n\r\nFunction created.\r\n\r\n\r\nFunction created.\r\n\r\n\r\nProcedure created.\r\n\r\nThis script will update the content of the Oracle Data Dictionary.\r\nPlease ensure you have a full backup before initiating this procedure.\r\nWould you like to proceed (Y\/N)?Y\r\nold 6: if (UPPER('&conf') <> 'Y') then\r\nnew 6: if (UPPER('Y') <> 'Y') then\r\nChecking data validity...\r\nUnrecognized convertible data found in scanner result\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nChecking or Converting phase did not finish successfully\r\nNo database (national) character set will be altered\r\nCSALTER finished unsuccessfully.\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\n\r\n0 rows deleted.\r\n\r\n\r\nFunction dropped.\r\n\r\n\r\nFunction dropped.\r\n\r\n\r\nProcedure dropped.\r\n\r\n<\/pre>\n\u6b64\u5904\u53d1\u73b0\u62a5\u9519\uff1aUnrecognized convertible data found in scanner result<\/strong>
\n\u67e5\u4e86\u6587\u6863\u53d1\u4e0b\u5982\u4e0b\u89e3\u91ca
\nIf you run Csalter without these conditions met then you will see messages like \" Unrecognized convertible data found in scanner result \" in the Csalter output.<\/p>\nBefore you can run Csalter you need
\n* to have a 'clean' FULL=Y csscan result, a 'clean' scan means that there is no convertible (except Data Dictionary CLOB data which may be convertible and if so it will be handled by Csalter), truncation or lossy data in the database.
\n* to have that FULL=Y run been completed in the 7 days prior to running Csalter. So you can only run Csalter in the 7 days following the 'Clean' FULL=Y scan.
\n* to be sure the session running Csalter is the ONLY session connected to the database, otherwise Csalter will give this warning 'Sorry only one session is allowed to run this script'.<\/p>\nThe Csalter script itself takes no arguments, if above conditions are met then Csalter will change the characterset to the one specified in the TOCHAR (or TONCHAR) of csscan.<\/p>\n
\u53ef\u80fd\u548c\u7b2c\u4e00\u6761\u6709\u5173\uff0c\u4e0b\u8fb9\u91c7\u7528sql\u8bed\u53e5\u7684\u65b9\u6cd5\u6765\u4fee\u6539<\/p>\n
\u5173\u95ed\u5b9e\u4f8b\uff0c\u5e76\u542f\u52a8\u6240\u6709\u5b9e\u4f8b\u67e5\u770b\u4fee\u6539\u7ed3\u679c\u3002<\/p>\n
\r\nSQL> SHUTDOWN IMMEDIATE\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\nSQL> STARTUP MOUNT; \r\nORACLE instance started.\r\n\r\nTotal System Global Area 839282688 bytes\r\nFixed Size 2233000 bytes\r\nVariable Size 574623064 bytes\r\nDatabase Buffers 260046848 bytes\r\nRedo Buffers 2379776 bytes\r\nDatabase mounted.\r\nSQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; \r\n\r\nSystem altered.\r\n\r\nSQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;\r\n\r\nSystem altered.\r\n\r\nSQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; \r\n\r\nSystem altered.\r\n\r\nSQL> ALTER DATABASE OPEN; \r\n\r\nDatabase altered.\r\n\r\nSQL> alter database character set UTF8;\r\nalter database character set UTF8\r\n*\r\nERROR at line 1:\r\nORA-12712: new character set must be a superset of old character set\r\n\r\n\r\nSQL> alter database character set INTERNAL_USE UTF8;\r\nalter database character set INTERNAL_USE UTF8\r\n*\r\nERROR at line 1:\r\nORA-12720: operation requires database is in EXCLUSIVE mode\r\n\r\nSQL> alter system set CLUSTER_DATABASE=false scope=spfile;\r\n\r\nSystem altered.\r\n\r\nSQL> shutdown immediate;\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\nSQL> startup mount exclusive\r\nORACLE instance started.\r\n\r\nTotal System Global Area 839282688 bytes\r\nFixed Size 2233000 bytes\r\nVariable Size 574623064 bytes\r\nDatabase Buffers 260046848 bytes\r\nRedo Buffers 2379776 bytes\r\nDatabase mounted.\r\nSQL> alter system enable restricted session;\r\n\r\nSystem altered.\r\n\r\nSQL> alter system set job_queue_processes = 0; \r\n\r\nSystem altered.\r\n\r\nSQL> alter system set aq_tm_processes = 0;\r\n\r\nSystem altered.\r\n\r\nSQL> alter database open;\r\n\r\nDatabase altered.\r\n\r\nSQL> alter database character set UTF8;\r\nalter database character set UTF8\r\n*\r\nERROR at line 1:\r\nORA-12712: new character set must be a superset of old character set\r\n\r\n\r\nSQL> alter database character set INTERNAL_USE UTF8;\r\n\r\nDatabase altered.\r\n\r\nSQL> alter system set CLUSTER_DATABASE=TRUE scope=spfile;\r\n\r\nSystem altered.\r\n\r\nSQL> shutdown immediate;\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\n\r\n<\/pre>\n\u542f\u52a8\u6240\u6709\u5b9e\u4f8b<\/p>\n
\r\nSQL> startup\r\nORACLE instance started.\r\n\r\nTotal System Global Area 839282688 bytes\r\nFixed Size 2233000 bytes\r\nVariable Size 574623064 bytes\r\nDatabase Buffers 260046848 bytes\r\nRedo Buffers 2379776 bytes\r\nDatabase mounted.\r\nDatabase opened.\r\n<\/pre>\n\u67e5\u770b\u4fee\u6539\u540e\u6570\u636e\u5e93\u7684\u5b57\u7b26\u96c6<\/p>\n
\r\nSQL> set line 400\r\nSQL> select userenv('language') from dual;\r\n\r\nUSERENV('LANGUAGE')\r\n--------------------------------------------------------------------------------------------------------\r\nAMERICAN_AMERICA.UTF8\r\n\r\nSQL> select * from nls_database_parameters;\r\n\r\nPARAMETER VALUE\r\n------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nNLS_CSMIG_SCHEMA_VERSION 5\r\nNLS_LANGUAGE AMERICAN\r\nNLS_TERRITORY AMERICA\r\nNLS_CURRENCY $\r\nNLS_ISO_CURRENCY AMERICA\r\nNLS_NUMERIC_CHARACTERS .,\r\nNLS_CHARACTERSET UTF8\r\nNLS_CALENDAR GREGORIAN\r\nNLS_DATE_FORMAT DD-MON-RR\r\nNLS_DATE_LANGUAGE AMERICAN\r\nNLS_SORT BINARY\r\n\r\nPARAMETER VALUE\r\n------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nNLS_TIME_FORMAT HH.MI.SSXFF AM\r\nNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM\r\nNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR\r\nNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR\r\nNLS_DUAL_CURRENCY $\r\nNLS_COMP BINARY\r\nNLS_LENGTH_SEMANTICS BYTE\r\nNLS_NCHAR_CONV_EXCP FALSE\r\nNLS_NCHAR_CHARACTERSET AL16UTF16\r\nNLS_RDBMS_VERSION 11.2.0.3.0\r\n\r\n21 rows selected.\r\n\r\n[oracle@11grac1 ~]$ env | grep NLS_LANG\r\nNLS_LANG=AMERICAN_AMERICA.zhs16gbk\r\n[oracle@11grac1 ~]$ env | grep LANG\r\nNLS_LANG=AMERICAN_AMERICA.zhs16gbk\r\nLANG=zh_CN.UTF-8\r\n[oracle@11grac1 ~]$ sqlplus "\/as sysdba"\r\n\r\nSQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 22:11:35 2013\r\n\r\nCopyright (c) 1982, 2011, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n\r\nSQL> set line 400\r\nSQL> select * from test.yallonking;\r\n\r\n ID VARCHAR_COL1 NVARCHAR_COL2\r\n---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------\r\n 1 \uff1f\uff1f\uff1f\uff1f\uff1f?? \u6211\u662f\u4e2d\u6587\u5b57\u7b26\r\n 2 i am english char i am english nvarchar\r\n 3 \uff1f\uff1f\uff1f\uff1f?\uff1f\uff1f? \u6211\u662f\u4e2d\u6587\u5206\u53f7\uff1b\r\n 4 i am english char ; i am english char ;\r\n 5 \uff1f\uff1f\uff1f\uff1f\uff1f\uff1f123 \u6211\u662f\u4e2d\u6587\u6570\u5b57123\r\n 6 i am english number 123 i am english number 123\r\n\r\n6 rows selected.\r\n\r\nSQL> exit\r\nDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n[oracle@11grac1 ~]$ export NLS_LANG=AMERICAN_AMERICA.UTF8\r\n[oracle@11grac1 ~]$ sqlplus "\/as sysdba"\r\n\r\nSQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 22:12:27 2013\r\n\r\nCopyright (c) 1982, 2011, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n\r\nSQL> set line 400\r\nSQL> select * from test.yallonking;\r\n\r\n ID VARCHAR_COL1 NVARCHAR_COL2\r\n---------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------\r\n 1 \u6211\u662f\u4e2d\u6587\u5b57\u7b26 \u93b4\u621e\u69f8\u6d93\ue15f\u6783\u701b\u6943\ue0c1\r\n 2 i am english char i am english nvarchar\r\n 3 \u6211\u662f\u4e2d\u6587\u5206\u53f7\uff1b \u93b4\u621e\u69f8\u6d93\ue15f\u6783\u9352\u55d7\u5f7f\u951b?\r\n 4 i am english char ; i am english char ;\r\n 5 \u6211\u662f\u4e2d\u6587\u6570\u5b57123 \u93b4\u621e\u69f8\u6d93\ue15f\u6783\u93c1\u677f\u74e7123\r\n 6 i am english number 123 i am english number 123\r\n\r\n6 rows selected.\r\n<\/pre>\n\u6b64\u5904\u53d1\u73b0\u6570\u636e\u5e93\u56fd\u9645\u5b57\u7b26\u96c6\u5728\u5ba2\u6237\u7aef\u7684\u914d\u7f6e\u4e0a\u51fa\u73b0\u95ee\u9898
\n\u4f46\u662f\u5728\u5c06NLS_NCHAR_CHARACTERSET\u7528\u540c\u6837\u7684\u65b9\u6cd5\u4fee\u6539\u4e3aUTF8\u540e\u4f9d\u7136\u662f\u6709\u4e71\u7801\u3002
\n\u901a\u8fc7\u4fee\u6539\u73af\u5883\u53d8\u91cf \u5c31\u53ef\u4ee5\u4f7f varchar\u548cnvarchar \u5206\u522b\u663e\u793a\u6b63\u786e
\n\u4f46\u662f \u4e8c\u8005\u4e0d\u80fd\u4e00\u8d77\u663e\u793a\u6b63\u5e38<\/strong><\/p>\n\u5f88\u665a\u4e86\uff0c\u5148\u5230\u8fd9\u4ee5\u540e\u7ee7\u7eed\u3002<\/p>\n
tips\uff1a
\nFrom 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values: UTF8 or AL16UTF16.
\nUTF8\uff1a\u53ef\u53d8\u957f\u5ea6\u5355\u5b57\u8282\u7f16\u7801\u6a21\u5f0f\uff08Unicode\u5b57\u7b26\u96c6\uff09
\nALUTF8\uff1a\u53ef\u53d8\u957f\u5ea6\u591a\u5b57\u8282\u7f16\u7801\u6a21\u5f0f\uff08Unicode\u5b57\u7b26\u96c6\uff09
\nAL16UTF16:\u56fa\u5b9a\u957f\u5ea6\u591a\u5b57\u8282\u7f16\u7801\u6a21\u5f0f\uff08\u4e00\u79cd2\u5b57\u8282\u7684Unicode\u5b57\u7b26\u96c6\uff09
\n\u5176\u5b9e\u4eceZHS16GBK\u4fee\u6539\u4e3aAL32UTF8\u4e5f\u662f\u53ef\u4ee5\u7684\u3002
\n\u5982\u4e0b\uff1a<\/p>\n\r\n[Database Scan Parameters]\r\n\r\nParameter Value \r\n------------------------------ ------------------------------------------------\r\nCSSCAN Version v2.1 \r\nInstance Name racdb1 \r\nDatabase Version 11.2.0.3.0 \r\nScan type Full database \r\nScan CHAR data? YES \r\nDatabase character set ZHS16GBK \r\nFROMCHAR ZHS16GBK \r\nTOCHAR AL32UTF8 \r\nScan NCHAR data? NO \r\nArray fetch buffer size 1000000 \r\nNumber of processes 2 \r\nCapture convertible data? NO \r\n------------------------------ ------------------------------------------------\r\n\r\n[Scan Summary]\r\n\r\nAll character type data in the data dictionary remain the same in the new character set\r\nAll character type application data are convertible to the new character set\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"\u4fee\u6539\u5b57\u7b26\u96c6\uff08ZHS16GBK\u5230UTF-8\uff09 \u6570\u636e\u5e93\u7248\u672c SQL> set line 400 SQL> […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[87],"tags":[],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/902"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=902"}],"version-history":[{"count":2,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/902\/revisions"}],"predecessor-version":[{"id":904,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/902\/revisions\/904"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=902"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=902"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=902"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}