[Oracle] oracle修改字符集


本文总阅读量次

0、查看数据库字符集

1)数据库服务器字符集

1
select * from nls_database_parameters;

来源于props$，是表示数据库的字符集。
2)客户端字符集环境

1
select * from nls_instance_parameters;

其来源于v$parameter，表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表
3)会话字符集环境

1
select * from nls_session_parameters;

来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。

1、字符集由AL32UTF8修改为ZHS16GBK

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
13:51:26 SYS@ boston> select * from nls_database_parameters;

PARAMETER                                                    VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE                                                 AMERICAN
NLS_TERRITORY                                                AMERICA
NLS_CURRENCY                                                 $
NLS_ISO_CURRENCY                                             AMERICA
NLS_NUMERIC_CHARACTERS                                       .,
NLS_CHARACTERSET                                             AL32UTF8
NLS_CALENDAR                                                 GREGORIAN
NLS_DATE_FORMAT                                              DD-MON-RR
NLS_DATE_LANGUAGE                                            AMERICAN
NLS_SORT                                                     BINARY
NLS_TIME_FORMAT                                              HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                         DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                           HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                      DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                            $
NLS_COMP                                                     BINARY
NLS_LENGTH_SEMANTICS                                         BYTE
NLS_NCHAR_CONV_EXCP                                          FALSE
NLS_NCHAR_CHARACTERSET                                       AL16UTF16
NLS_RDBMS_VERSION                                            11.2.0.4.0

提示要修改的新的字符集必须大于旧的字符集才可以

1
2
3
4
5
13:51:36 SYS@ boston> 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

跳过超集的检查

1
2
3
4
5
6
7
8
13:59:34 SYS@ boston> alter system enable restricted session; 
System altered.
Elapsed: 00:00:02.05
13:59:48 SYS@ boston> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
ALTER DATABASE character set INTERNAL_USE ZHS16GBK
*
ERROR at line 1:
ORA-12721: operation cannot execute when other sessions are active

查看当前活动的会话

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
14:02:48 SYS@ boston> select sid, serial#,program ,status from v$session;

       SID    SERIAL# PROGRAM                                                                                          STATUS
---------- ---------- ------------------------------------------------------------------------------------------------ ----------------
         1          1 oracle@dax-mysql-slave (VKTM)                                                                    ACTIVE
         2          1 oracle@dax-mysql-slave (DIA0)                                                                    ACTIVE
         3          1 oracle@dax-mysql-slave (CKPT)                                                                    ACTIVE
         4          3 oracle@dax-mysql-slave (MMNL)                                                                    ACTIVE
         6         11 oracle@dax-mysql-slave (SMCO)                                                                    ACTIVE
       101          1 oracle@dax-mysql-slave (GEN0)                                                                    ACTIVE
       102          1 oracle@dax-mysql-slave (MMAN)                                                                    ACTIVE
       103          1 oracle@dax-mysql-slave (SMON)                                                                    ACTIVE
       105       1603 sqlplus@dax-mysql-slave (TNS V1-V3)                                                              ACTIVE
       201          1 oracle@dax-mysql-slave (PMON)                                                                    ACTIVE
       202          1 oracle@dax-mysql-slave (DIAG)                                                                    ACTIVE
       203          1 oracle@dax-mysql-slave (DBW0)                                                                    ACTIVE
       204          1 oracle@dax-mysql-slave (RECO)                                                                    ACTIVE
       207        145 sqlplus@dax-mysql-slave (TNS V1-V3)                                                              INACTIVE
       301        291 oracle@dax-mysql-slave (W000)                                                                    ACTIVE
       302          1 oracle@dax-mysql-slave (PSP0)                                                                    ACTIVE
       303          1 oracle@dax-mysql-slave (DBRM)                                                                    ACTIVE
       304          1 oracle@dax-mysql-slave (LGWR)                                                                    ACTIVE
       305          3 oracle@dax-mysql-slave (MMON)                                                                    ACTIVE
       306        199 sqlplus@dax-mysql-slave (TNS V1-V3)                                                              INACTIVE

查看当前会话的sid

1
2
3
4
14:02:56 SYS@ boston> select userenv('sid') from dual;
USERENV('SID')
--------------
           105

尝试杀掉sqlplus连接进来的两个inactive的会话

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
14:06:46 SYS@ boston> alter system kill session '207,145';
System altered.
Elapsed: 00:00:00.00
14:07:11 SYS@ boston> alter system kill session '306,199';
System altered.

14:07:27 SYS@ boston> select sid, serial#,program ,status from v$session;

       SID    SERIAL# PROGRAM                                                                                          STATUS
---------- ---------- ------------------------------------------------------------------------------------------------ ----------------
         1          1 oracle@dax-mysql-slave (VKTM)                                                                    ACTIVE
         2          1 oracle@dax-mysql-slave (DIA0)                                                                    ACTIVE
         3          1 oracle@dax-mysql-slave (CKPT)                                                                    ACTIVE
         4          3 oracle@dax-mysql-slave (MMNL)                                                                    ACTIVE
         6         11 oracle@dax-mysql-slave (SMCO)                                                                    ACTIVE
       101          1 oracle@dax-mysql-slave (GEN0)                                                                    ACTIVE
       102          1 oracle@dax-mysql-slave (MMAN)                                                                    ACTIVE
       103          1 oracle@dax-mysql-slave (SMON)                                                                    ACTIVE
       105       1603 sqlplus@dax-mysql-slave (TNS V1-V3)                                                              ACTIVE
       201          1 oracle@dax-mysql-slave (PMON)                                                                    ACTIVE
       202          1 oracle@dax-mysql-slave (DIAG)                                                                    ACTIVE
       203          1 oracle@dax-mysql-slave (DBW0)                                                                    ACTIVE
       204          1 oracle@dax-mysql-slave (RECO)                                                                    ACTIVE
       207        145 sqlplus@dax-mysql-slave (TNS V1-V3)                                                              KILLED
       302          1 oracle@dax-mysql-slave (PSP0)                                                                    ACTIVE
       303          1 oracle@dax-mysql-slave (DBRM)                                                                    ACTIVE
       304          1 oracle@dax-mysql-slave (LGWR)                                                                    ACTIVE
       305          3 oracle@dax-mysql-slave (MMON)                                                                    ACTIVE
       306        199 sqlplus@dax-mysql-slave (TNS V1-V3)                                                              KILLED

再次执行仍然提示有活动会话

1
2
3
4
5
14:07:29 SYS@ boston>  ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
 ALTER DATABASE character set INTERNAL_USE ZHS16GBK
*
ERROR at line 1:
ORA-12721: operation cannot execute when other sessions are active

重启数据库再次修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
14:10:54 SYS@ boston> shutdown immeidate
SP2-0717: illegal SHUTDOWN option
14:11:02 SYS@ boston> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
14:11:16 SYS@ boston> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             503319712 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7319552 bytes
Database mounted.
Database opened.
14:11:46 SYS@ boston> select sid, serial#,program ,status from v$session;
       SID    SERIAL# PROGRAM                                                                                          STATUS
---------- ---------- ------------------------------------------------------------------------------------------------ ----------------
         1          1 oracle@dax-mysql-slave (VKTM)                                                                    ACTIVE
         2          1 oracle@dax-mysql-slave (DIA0)                                                                    ACTIVE
         3          1 oracle@dax-mysql-slave (CKPT)                                                                    ACTIVE
         4          1 oracle@dax-mysql-slave (MMNL)                                                                    ACTIVE
       101          1 oracle@dax-mysql-slave (GEN0)                                                                    ACTIVE
       102          1 oracle@dax-mysql-slave (MMAN)                                                                    ACTIVE
       103          1 oracle@dax-mysql-slave (SMON)                                                                    ACTIVE
       201          1 oracle@dax-mysql-slave (PMON)                                                                    ACTIVE
       202          1 oracle@dax-mysql-slave (DIAG)                                                                    ACTIVE
       203          1 oracle@dax-mysql-slave (DBW0)                                                                    ACTIVE
       204          1 oracle@dax-mysql-slave (RECO)                                                                    ACTIVE
       301          5 sqlplus@dax-mysql-slave (TNS V1-V3)                                                              ACTIVE
       302          1 oracle@dax-mysql-slave (PSP0)                                                                    ACTIVE
       303          1 oracle@dax-mysql-slave (DBRM)                                                                    ACTIVE
       304          1 oracle@dax-mysql-slave (LGWR)                                                                    ACTIVE
       305          1 oracle@dax-mysql-slave (MMON)                                                                    ACTIVE
16 rows selected.

Elapsed: 00:00:00.03
14:11:47 SYS@ boston>  alter system enable restricted session; 
System altered.
Elapsed: 00:00:02.04
14:12:00 SYS@ boston>  ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
Database altered.

修改完成再次重启数据库，（修改完字符集之后关闭数据库会花费很长时间）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
14:12:13 SYS@ boston> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
14:18:21 SYS@ boston> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             503319712 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7319552 bytes
Database mounted.
Database opened.

查看字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
14:19:40 SYS@ boston> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.4.0
20 rows selected.

字符集修改完成,数据库的字符集更改会出现乱码，操作要慎重。

2、数据库字符集由ZHS16GBK改为AL32UTF8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
15:01:20 SYS@ boston> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.4.0
20 rows selected.
15:05:51 SYS@ boston> alter system enable restricted session; 
System altered.
Elapsed: 00:00:02.06
15:07:05 SYS@ boston> ALTER DATABASE character set  AL32UTF8; 
ALTER DATABASE character set  AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
Elapsed: 00:00:00.00

这个报错证明AL32UTF8字符集并不是ZHS16GBK超集。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
15:07:12 SYS@ boston> ALTER DATABASE character set INTERNAL_USE AL32UTF8; 
ALTER DATABASE character set INTERNAL_USE AL32UTF8
*
ERROR at line 1:
ORA-12721: operation cannot execute when other sessions are active

Elapsed: 00:00:00.01
15:07:23 SYS@ boston> select sid, serial#,program ,status from v$session;

       SID    SERIAL# PROGRAM                                          STATUS
---------- ---------- ------------------------------------------------ --------
         1          1 oracle@dax-mysql-slave (VKTM)                    ACTIVE
         2          1 oracle@dax-mysql-slave (DIA0)                    ACTIVE
         3          1 oracle@dax-mysql-slave (CKPT)                    ACTIVE
         4          3 oracle@dax-mysql-slave (MMNL)                    ACTIVE
         7          5 oracle@dax-mysql-slave (SMCO)                    ACTIVE
       101          1 oracle@dax-mysql-slave (GEN0)                    ACTIVE
       102          1 oracle@dax-mysql-slave (MMAN)                    ACTIVE
       103          1 oracle@dax-mysql-slave (SMON)                    ACTIVE
       104         27 oracle@dax-mysql-slave (W000)                    ACTIVE
       201          1 oracle@dax-mysql-slave (PMON)                    ACTIVE
       202          1 oracle@dax-mysql-slave (DIAG)                    ACTIVE
       203          1 oracle@dax-mysql-slave (DBW0)                    ACTIVE
       204          1 oracle@dax-mysql-slave (RECO)                    ACTIVE
       205         15 sqlplus@dax-mysql-slave (TNS V1-V3)              INACTIVE
       301         15 sqlplus@dax-mysql-slave (TNS V1-V3)              INACTIVE
       302          1 oracle@dax-mysql-slave (PSP0)                    ACTIVE
       303          1 oracle@dax-mysql-slave (DBRM)                    ACTIVE
       304          1 oracle@dax-mysql-slave (LGWR)                    ACTIVE
       305          3 oracle@dax-mysql-slave (MMON)                    ACTIVE
       306        139 sqlplus@dax-mysql-slave (TNS V1-V3)              ACTIVE

20 rows selected.
Elapsed: 00:00:00.01
15:07:36 SYS@ boston> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
15:07:55 SYS@ boston> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             503319712 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7319552 bytes
Database mounted.
Database opened.
15:08:38 SYS@ boston> 
15:13:24 SYS@ boston> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AL32UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.4.0

字符集修改完成

oracle
目录
  1. 1. 0、查看数据库字符集
  2. 2. 1、字符集由AL32UTF8修改为ZHS16GBK
  3. 3. 2、数据库字符集由ZHS16GBK改为AL32UTF8

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog