谨慎修改Oracle数据库字符集（UTF8<->ZHS16GBK）

 
Preface
 
    Today,I'm gonna say something what is related with the character set in Oracle database,the scenarios in changing character set from UTF8 to ZHS16GBK and the contra way as well.
    It' not recommended to modify character set in Oracle database because of the risk of lost data especially in changing superset to subset.Even though you can use "internal_use" option to change.
    You need to pay more attention in understanding some limitation in doing that.Data must be export with correct character set before you to really change it.Let's see the details below.
 
Environment
 
Hostname IP Linux Version Oracle Database SID
ogg 192.168.1.20 RHEL 5.7 11.2.0.1 ogg
 
Procedure
 
1. Set database character set from ZHS16GBK to UTF8.
 
Check the character set of database.
 1 SQL> set line 120 pages 9999
 2 SQL> col value for a40
 3 SQL> select * from nls_database_parameters;
 4 
 5 PARAMETER               VALUE
 6 ------------------------------ ----------------------------------------
 7 NLS_LANGUAGE               AMERICAN
 8 NLS_TERRITORY               AMERICA
 9 NLS_CURRENCY               $
10 NLS_ISO_CURRENCY           AMERICA
11 NLS_NUMERIC_CHARACTERS           .,
12 NLS_CHARACTERSET           ZHS16GBK
13 NLS_CALENDAR               GREGORIAN
14 NLS_DATE_FORMAT            DD-MON-RR
15 NLS_DATE_LANGUAGE           AMERICAN
16 NLS_SORT               BINARY
17 NLS_TIME_FORMAT            HH.MI.SSXFF AM
18 NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
19 NLS_TIME_TZ_FORMAT           HH.MI.SSXFF AM TZR
20 NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
21 NLS_DUAL_CURRENCY           $
22 NLS_COMP               BINARY
23 NLS_LENGTH_SEMANTICS           BYTE
24 NLS_NCHAR_CONV_EXCP           FALSE
25 NLS_NCHAR_CHARACTERSET           AL16UTF16
26 NLS_RDBMS_VERSION           11.2.0.1.0
27 
28 20 rows selected.
29 
30 SQL> select userenv('language') from dual;  
31 
32 USERENV('LANGUAGE')
33 ----------------------------------------------------
34 AMERICAN_AMERICA.ZHS16GBK

 

Create test user and test table.
 1 SQL> col file_name for a60
 2 SQL> col tablespace_name for a30
 3 SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files;
 4 
 5 FILE_NAME                             TABLESPACE_NAME            BYTES/1024/1024
 6 ------------------------------------------------------------ ------------------------------ ---------------
 7 /u01/app/oracle/oradata/ogg/system01.dbf             SYSTEM                    700
 8 /u01/app/oracle/oradata/ogg/sysaux01.dbf             SYSAUX                    600
 9 /u01/app/oracle/oradata/ogg/undotbs01.dbf             UNDOTBS1                    325
10 /u01/app/oracle/oradata/ogg/zlm01.dbf                 ZLM                    100
11 
12 SQL> create user zlm identified by zlm default tablespace zlm;
13 
14 User created.
15 
16 SQL> grant dba to zlm;
17 
18 Grant succeeded.
19 SQL> conn zlm/zlm    
20 Connected.
21 SQL> create table zlm.test_charset(
22   2  id number,
23   3  name varchar2(10),  
24   4  primary key(id)
25   5  );
26 
27 Table created.
28 
29 SQL> insert into zlm.test_charset(id,name) values(1,'黎明');
30 
31 1 row created.
32 
33 SQL> commit;
34 
35 Commit complete.
36 
37 SQL> select * from test_charset;
38 
39     ID NAME
40 ---------- ----------
41      1 黎明
42 
43 SQL> select length('黎'),length('明') from dual;
44 
45 LENGTH('黎') LENGTH('明')
46 ------------ ------------
47        1        1
48        
49 SQL> select lengthb('黎'),lengthb('明') from dual;
50 
51 LENGTHB('黎') LENGTHB('明')
52 ------------- -------------
53         2          2
54 
55 //ZHS16GBK character set occupies 2 byte to store one Chinese character here.
56 //Be aware of the difference of length() function between MySQL and Oracle.
57 //In MySQL,length() returns bytes instead of characters.

 

Export the test table with expdp.
 1 SQL> select *from dba_directories;
 2 
 3 OWNER                   DIRECTORY_NAME
 4 ------------------------------ ------------------------------
 5 DIRECTORY_PATH
 6 --------------------------------------------------------------------------------
 7 SYS                   ORACLE_OCM_CONFIG_DIR
 8 /u01/app/oracle/product/11.2.0/db_1/ccr/state
 9 
10 SYS                   DATA_PUMP_DIR
11 /u01/app/oracle/admin/ogg/dpdump/
12 
13 SYS                   XMLDIR
14 /u01/app/oracle/product/11.2.0/db_1/rdbms/xml
15 
16 
17 SQL> create directory datapump as '/u01/';
18 
19 Directory created.
20 
21 SQL> grant read,write on directory datapump to zlm;  
22 grant read,write on directory datapump to zlm
23                                           *
24 ERROR at line 1:
25 ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
26 
27 
28 SQL> conn / as sysdba
29 Connected.
30 SQL> grant read,write on directory datapump to zlm;
31 
32 Grant succeeded.
33 
34 SQL> exit
35 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
36 With the Partitioning option
37 [oracle@ogg ~]$ expdp zlm/zlm directory=datapump dumpfile=zlm_datapump_`date +%Y%m%d`.dmp logfile=zlm_datapump_`date +%Y%m%d`.log schemas=zlm
38 
39 Export: Release 11.2.0.1.0 - Production on Wed Jul 11 12:51:06 2018
40 
41 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
42 
43 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
44 With the Partitioning option
45 Starting "ZLM"."SYS_EXPORT_SCHEMA_01":  zlm/******** directory=datapump dumpfile=zlm_datapump_20180711.dmp logfile=zlm_datapump_20180711.log schemas=zlm 
46 Estimate in progress using BLOCKS method...
47 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
48 Total estimation using BLOCKS method: 64 KB
49 Processing object type SCHEMA_EXPORT/USER
50 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
51 Processing object type SCHEMA_EXPORT/ROLE_GRANT
52 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
53 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
54 Processing object type SCHEMA_EXPORT/TABLE/TABLE
55 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
56 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
57 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
58 Processing object type SCHEMA_EXPORT/TABLE/COMMENT
59 . . exported "ZLM"."TEST_CHARSET"                        5.414 KB       1 rows
60 Master table "ZLM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
61 ******************************************************************************
62 Dump file set for ZLM.SYS_EXPORT_SCHEMA_01 is:
63   /u01/zlm_datapump_20180711.dmp
64 Job "ZLM"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:51:31
65 
66 [oracle@ogg ~]$ cd /u01
67 [oracle@ogg u01]$ ls -l
68 total 200
69 drwxrwxr-x  4 oracle oinstall   4096 May 19  2017 app
70 drwxrwxr-x  3 oracle oinstall   4096 Mar 29 15:40 arch
71 drwxrwxr-x 14 oracle oinstall   4096 Feb  7 17:01 ogg
72 -rw-r-----  1 oracle oinstall 184320 Jul 11 12:51 zlm_datapump_20180711.dmp
73 -rw-r--r--  1 oracle oinstall   1500 Jul 11 12:51 zlm_datapump_20180711.log
74 [oracle@ogg u01]$

 

Modify the character set into "UTF8".
 1 SQL> alter database character set utf8;
 2 alter database character set utf8
 3 *
 4 ERROR at line 1:
 5 ORA-12712: new character set must be a superset of old character set
 6 
 7 //Notice!This means that UTF8 is not the superset of ZHS16GBK.
 8 
 9 SQL> alter database character set internal_use utf8;
10 alter database character set internal_use utf8
11 *
12 ERROR at line 1:
13 ORA-12719: operation requires database is in RESTRICTED mode
14 
15 
16 SQL> shutdown immediate
17 ORA-01031: insufficient privileges
18 SQL> conn / as sysdba
19 Connected.
20 SQL> shutdown immediate   
21 Database closed.
22 Database dismounted.
23 ORACLE instance shut down.
24 SQL> startup mount exclusive;      
25 ORACLE instance started.
26 
27 Total System Global Area  835104768 bytes
28 Fixed Size            2217952 bytes
29 Variable Size          562038816 bytes
30 Database Buffers      268435456 bytes
31 Redo Buffers            2412544 bytes
32 Database mounted.
33 SQL> alter system enable restricted session;
34 
35 System altered.
36 
37 SQL> alter database open;                 
38 
39 Database altered.
40 
41 SQL> alter database character set internal_use utf8;
42 alter database character set internal_use utf8
43 *
44 ERROR at line 1:
45 ORA-12721: operation cannot execute when other sessions are active
46 
47 //Exit the other sessions who are active.
48 
49 SQL> alter database character set internal_use utf8;
50 
51 Database altered.
52 
53 SQL>

 

Check the new database character set.
 1 SQL> set line 120 pages 9999
 2 SQL> col value for a40
 3 SQL> select * from nls_database_parameters;
 4 
 5 PARAMETER                             VALUE
 6 ------------------------------------------------------------ ----------------------------------------
 7 NLS_LANGUAGE                             AMERICAN
 8 NLS_TERRITORY                             AMERICA
 9 NLS_CURRENCY                             $
10 NLS_ISO_CURRENCY                         AMERICA
11 NLS_NUMERIC_CHARACTERS                         .,
12 NLS_CHARACTERSET                         UTF8
13 NLS_CALENDAR                             GREGORIAN
14 NLS_DATE_FORMAT                          DD-MON-RR
15 NLS_DATE_LANGUAGE                         AMERICAN
16 NLS_SORT                             BINARY
17 NLS_TIME_FORMAT                          HH.MI.SSXFF AM
18 NLS_TIMESTAMP_FORMAT                         DD-MON-RR HH.MI.SSXFF AM
19 NLS_TIME_TZ_FORMAT                         HH.MI.SSXFF AM TZR
20 NLS_TIMESTAMP_TZ_FORMAT                      DD-MON-RR HH.MI.SSXFF AM TZR
21 NLS_DUAL_CURRENCY                         $
22 NLS_COMP                             BINARY
23 NLS_LENGTH_SEMANTICS                         BYTE
24 NLS_NCHAR_CONV_EXCP                         FALSE
25 NLS_NCHAR_CHARACTERSET                         AL16UTF16
26 NLS_RDBMS_VERSION                         11.2.0.1.0
27 
28 20 rows selected.
29 
30 SQL> select userenv('language') from dual; 
31 
32 USERENV('LANGUAGE')
33 --------------------------------------------------------------------------------------------------------
34 AMERICAN_AMERICA.UTF8
35 
36 SQL>

 

Check the original data in test table.
 1 SQL> conn zlm/zlm 
 2 Connected.
 3 SQL> select * from test_charset;
 4 
 5     ID NAME
 6 ---------- --------------------
 7      1 ?？
 8 
 9 //My client character set of Xshell is still GBK now,change it to utf8 and check again.
10 
11 SQL> select * from test_charset;
12 
13     ID NAME
14 ---------- --------------------
15      1 ?£¿
16 
17 //The NLS_LANG variable is still GBK，modify the oracle profile to correct the character set to UTF8.
18 
19 SQL> !       
20 [oracle@ogg u01]$ cat ~/.bash_profile|grep NLS_LANG
21 export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
22 [oracle@ogg u01]$ vi ~/.bash_profile
23 [oracle@ogg u01]$ cat ~/.bash_profile|grep NLS_LANG
24 export NLS_LANG=AMERICAN_AMERICA.UTF8
25 [oracle@ogg u01]$ . ~/.bash_profile
26 [oracle@ogg u01]$ echo $NLS_LANG
27 AMERICAN_AMERICA.UTF8
28 [oracle@ogg u01]$ sqlplus zlm/zlm
29 
30 SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 11 15:11:55 2018
31 
32 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
33 
34 
35 Connected to:
36 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
37 With the Partitioning option
38 
39 SQL> select * from test_charset;                               
40 
41     ID NAME
42 ---------- ----------
43          1 hķ
44 
45 //It's still messy code here after i've unified the character set with server,client,NLS_LANG.

 

Drop test user and import it into database with the previous dump file.
 1 SQL> show user;
 2 USER is "ZLM"
 3 SQL> conn / as sysdba
 4 Connected.
 5 SQL> drop user zlm cascade;
 6 drop user zlm cascade
 7 *
 8 ERROR at line 1:
 9 ORA-01940: cannot drop a user that is currently connected
10 
11 
12 SQL> select sid,serial#,status from v$session where username='ZLM';
13 
14        SID    SERIAL# STATUS
15 ---------- ---------- --------
16      1        7 INACTIVE
17 
18 SQL> alter system kill session '1,7';
19 
20 System altered.
21 
22 SQL> select sid,serial#,status from v$session where username='ZLM';
23 
24        SID    SERIAL# STATUS
25 ---------- ---------- --------
26      1        7 KILLED
27 
28 SQL> drop user zlm cascade;
29 
30 User dropped.
31 
32 SQL> alter user system identified by 'Passw0rd';
33 alter user system identified by 'Passw0rd'
34                                 *
35 ERROR at line 1:
36 ORA-00988: missing or invalid password(s)
37 
38 
39 SQL> alter user system identified by Passw0rd;
40 
41 User altered.
42 
43 SQL> !
44 [oracle@ogg u01]$ ls -l
45 total 200
46 drwxrwxr-x  4 oracle oinstall   4096 May 19  2017 app
47 drwxrwxr-x  3 oracle oinstall   4096 Jul 11 13:00 arch
48 drwxrwxr-x 14 oracle oinstall   4096 Feb  7 17:01 ogg
49 -rw-r-----  1 oracle oinstall 184320 Jul 11 12:51 zlm_datapump_20180711.dmp
50 -rw-r--r--  1 oracle oinstall   1500 Jul 11 12:51 zlm_datapump_20180711.log
51 [oracle@ogg u01]$ impdp system/Passw0rd directory=datapump dumpfile=zlm_datapump_20180711.dmp
52 
53 Import: Release 11.2.0.1.0 - Production on Wed Jul 11 15:32:53 2018
54 
55 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
56 
57 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
58 With the Partitioning option
59 Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
60 Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=datapump dumpfile=zlm_datapump_20180711.dmp 
61 Processing object type SCHEMA_EXPORT/USER
62 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
63 Processing object type SCHEMA_EXPORT/ROLE_GRANT
64 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
65 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
66 Processing object type SCHEMA_EXPORT/TABLE/TABLE
67 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
68 . . imported "ZLM"."TEST_CHARSET"                        5.414 KB       1 rows
69 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
70 Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 15:32:56
71 
72 [oracle@ogg u01]$

 

Check the data in user zlm again.
 1 [oracle@ogg u01]$ exit
 2 exit
 3 
 4 SQL> conn zlm/zlm
 5 ERROR:
 6 ORA-00028: your session has been killed
 7 
 8 
 9 Connected.
10 SQL> exit
11 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
12 With the Partitioning option
13 [oracle@ogg u01]$ sqlplus zlm/zlm
14 
15 SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 11 15:33:48 2018
16 
17 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
18 
19 
20 Connected to:
21 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
22 With the Partitioning option
23 
24 SQL> select * from test_charset;
25 
26     ID NAME
27 ---------- --------------------
28      1 hķ
29 
30 SQL> select userenv('language') from dual; 
31 
32 USERENV('LANGUAGE')
33 --------------------------------------------------------------------------------
34 AMERICAN_AMERICA.UTF8
35 
36 SQL> exit
37 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
38 With the Partitioning option
39 [oracle@ogg u01]$ echo $NLS_LANG
40 AMERICAN_AMERICA.ZHS16GBK //The NLS_LANG is not equal with the character set in database.
41 [oracle@ogg u01]$ sed -i 's/ZHS16GBK/UTF8/g' ~/.bash_profile
42 [oracle@ogg u01]$ cat ~/.bash_profile|grep NLS_LANG
43 export NLS_LANG=AMERICAN_AMERICA.UTF8
44 [oracle@ogg u01]$ . ~/.bash_profile
45 [oracle@ogg u01]$ echo $NLS_LANG
46 AMERICAN_AMERICA.UTF8
47 [oracle@ogg u01]$ sqlplus  zlm/zlm
48 
49 SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 11 15:53:12 2018
50 
51 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
52 
53 
54 Connected to:
55 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
56 With the Partitioning option
57 
58 SQL> select * from test_charset;
59 
60     ID NAME
61 ---------- ----------
62          1 黎明
63 
64 SQL> select length('黎'),length('明') from dual;
65 
66 LENGTH('黎') LENGTH('明')
67 ------------ ------------
68        1        1
69 
70 SQL> select lengthb('黎'),lengthb('明') from dual;
71 
72 LENGTHB('黎') LENGTHB('明')
73 ------------- -------------
74         3          3
75 
76 SQL> 
77 
78 //The character set of database has been changed to UTF8 now and the data is either equal with the orgiginal data.

 

2. Set database character set from UTF8 to ZHS16GBK.
 
Insert a test record into test_charset table and shutdown instance to change character set again.
 1 SQL> insert into test_charset values(2,'上海');
 2 
 3 1 row created.
 4 
 5 SQL> commit;
 6 
 7 Commit complete.
 8 
 9 SQL> select * from test_charset;
10 
11     ID NAME
12 ---------- ----------
13          1 黎明
14          2 上海
15 
16 SQL> select length('上'),length('海') from dual;
17 
18 LENGTH('上') LENGTH('海')
19 ------------ ------------
20        1        1
21 
22 SQL> select lengthb('上'),lengthb('海') from dual;
23 
24 LENGTHB('上') LENGTHB('海')
25 ------------- -------------
26         3          3
27 
28 SQL> alter database character set ZHS16GBK;
29 alter database character set ZHS16GBK
30 *
31 ERROR at line 1:
32 ORA-12712: new character set must be a superset of old character set
33 
34 
35 SQL> alter database character set internal_use ZHS16GBK;
36 alter database character set internal_use ZHS16GBK
37 *
38 ERROR at line 1:
39 ORA-12719: operation requires database is in RESTRICTED mode
40 
41 
42 SQL> shutdown immediate
43 Database closed.
44 Database dismounted.
45 ORACLE instance shut down.
46 SQL> startup restrict
47 ORACLE instance started.
48 
49 Total System Global Area  835104768 bytes
50 Fixed Size            2217952 bytes
51 Variable Size          562038816 bytes
52 Database Buffers      268435456 bytes
53 Redo Buffers            2412544 bytes
54 Database mounted.
55 Database opened.
56 SQL> alter database character set internal_use ZHS16GBK;
57 
58 Database altered.
59 
60 SQL> select userenv('language') from dual;
61 
62 USERENV('LANGUAGE')
63 --------------------------------------------------------------------------------
64 AMERICAN_AMERICA.ZHS16GBK
65 
66 SQL>

 

Modify the character set of NLS_LANG togeter with that in client tool such as Xshell in my environment.
 1 SQL> !
 2 [oracle@ogg u01]$ echo $NLS_LANG
 3 AMERICAN_AMERICA.UTF8
 4 [oracle@ogg u01]$ sed -i 's/UTF8/ZHS16GBK/g' ~/.bash_profile
 5 [oracle@ogg u01]$ echo $NLS_LANG
 6 AMERICAN_AMERICA.UTF8
 7 [oracle@ogg u01]$ . ~/.bash_profile
 8 [oracle@ogg u01]$ echo $NLS_LANG
 9 AMERICAN_AMERICA.ZHS16GBK
10 [oracle@ogg u01]$ exit
11 exit
12 
13 SQL> select * from test_charset;
14 select * from test_charset
15               *
16 ERROR at line 1:
17 ORA-00942: table or view does not exist
18 
19 
20 SQL> conn zlm/zlm
21 Connected.
22 SQL> select * from test_charset;
23 
24     ID NAME
25 ---------- ------------------------------
26          1 姒涘孩妲
27          2 娑撳﹥鎹
28 
29 SQL> 
30 
31 //It's messy code now only if you've exported the data with datapump before you change the character set.Unfortunetely,I didn't to that this time.The characters in the test table test_charset has been destroyed.Can it come back again then?

 

Change it back to utf8 and check what will happen then?
 1 SQL> alter database character set utf8;
 2 alter database character set utf8
 3 *
 4 ERROR at line 1:
 5 ORA-12712: new character set must be a superset of old character set
 6 
 7 
 8 SQL> alter database character set internal_use utf8;
 9 alter database character set internal_use utf8
10 *
11 ERROR at line 1:
12 ORA-12718: operation requires connection as SYS //It needs sys role privilege to finish the work.
13 
14 
15 SQL> show user;
16 USER is "ZLM"
17 SQL> conn / as sysdba
18 Connected.
19 SQL> alter database character set internal_use utf8;
20 
21 Database altered.
22 
23 SQL> select * from zlm.test_charset;
24 
25     ID NAME
26 ---------- ----------
27          1 榛庢槑
28          2 涓婃捣
29 
30 SQL> select userenv('language') from dual;
31 
32 USERENV('LANGUAGE')
33 ----------------------------------------------------
34 AMERICAN_AMERICA.UTF8 //Character set of database server is UTF8 now.
35 
36 SQL> !
37 [oracle@ogg u01]$ echo $NLS_LANG
38 AMERICAN_AMERICA.UTF8 //NLS_LANG is UTF8,too.
39 [oracle@ogg u01]$ 
40 
41 //Character set of client tool has been changed to UTF8 as well.But the data cannot show the correct characters anymore.Have they been really destroyed?

 

Notice,only the user who has sys role privelge  can login instance after using "restrict" mode.See below.
 1 [oracle@ogg ~]$ sqlplus / as sysdba
 2 
 3 SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 11 17:02:30 2018
 4 
 5 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 6 
 7 
 8 Connected to:
 9 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
10 With the Partitioning option
11 
12 SQL> create user aaron8219 identified by aaron8219;  
13 
14 User created.
15 
16 SQL> grant session,connect to aaron8219;
17 grant session,connect to aaron8219
18       *
19 ERROR at line 1:
20 ORA-01919: role 'SESSION' does not exist
21 
22 
23 SQL> grant connect,resource to aaron8219;
24 
25 Grant succeeded.
26 
27 SQL> conn aaron8219/aaron8219
28 ERROR:
29 ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
30 
31 
32 Warning: You are no longer connected to ORACLE.
33 SQL>

 

Supplemented on July 12

 1 SQL> select userenv('language') from dual;
 2 
 3 USERENV('LANGUAGE')
 4 --------------------------------------------------------------------------------------------------------
 5 AMERICAN_AMERICA.UTF8
 6 
 7 SQL> select * from zlm.test_charset;
 8 
 9     ID NAME
10 ---------- --------------------
11      1 hķ
12      2 ʏº£
13 
14 SQL> !           
15 [oracle@ogg ~]$ echo $NLS_LANG
16 AMERICAN_AMERICA.ZHS16GBK
17 [oracle@ogg ~]$ sed -i 's/ZHS16GBK/UTF8/g' .bash_profile
18 [oracle@ogg ~]$ cat .bash_profile|grep NLS
19 export NLS_LANG=AMERICAN_AMERICA.UTF8
20 [oracle@ogg ~]$ . .bash_profile
21 [oracle@ogg ~]$ sqlplus / as sysdba
22 
23 SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 12 13:48:06 2018
24 
25 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
26 
27 
28 Connected to:
29 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
30 With the Partitioning option
31 
32 SQL> select * from zlm.test_charset;
33 
34     ID NAME
35 ---------- ----------
36          1 黎明
37          2 上海
38 
39 SQL> select userenv('language') from dual;
40 
41 USERENV('LANGUAGE')
42 ----------------------------------------------------
43 AMERICAN_AMERICA.UTF8
44 
45 SQL> select lengthb('上'),lengthb('海') from dual;
46 
47 LENGTHB('上') LENGTHB('海')
48 ------------- -------------
49         3          3
50 
51 //Change the character set of client tool to utf8,then the messy code came back again.
52 
53 SQL> select * from zlm.test_charset;  
54 
55     ID NAME
56 ---------- ----------
57          1 榛庢槑
58          2 涓婃捣
59 
60 SQL>
61 
62 //Actually,the reason why messy code occurs yesterday is due to the discordance between the character set of database,NLS_LANG,client tool.
63 //Although it proves that the data haven't been destroyed but there's a risk of mismatch of data if you change character set without backing up your whole data.

 

Summary
  • "internal_use" can help you to change character set without the constraint of subset to superset.
  • Only sys privilege users can login instance while database is running in restrict mode.
  • Be aware of ZHS16GBK is not subset of UTF8.It may baffle us most people.
  • It's not recommeded to change character set in oracle database which contains Chinese characters.
  • Don't forget to backup your data(logically) with datapump before you change the character set.
  • Make your  character set equal in client tool,NLS_LANG,db server anytime.This is the most important thing to keep in mind.
 
posted @ 2018-07-11 17:52  aaron8219  阅读(2686)  评论(0)  编辑  收藏  举报