oracle 字符集修改

 操作如下：

[oracle@master2 ~]$ sqlplus sys/ as sysdba

SQL> shutdown immediate
;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SP2-0223: No lines in SQL buffer.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size		    2213776 bytes
Variable Size		  503318640 bytes
Database Buffers	 1073741824 bytes
Redo Buffers		   24137728 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 internal_use utf8;
alter database character set internal_use utf8
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 164020
Session ID: 177 Serial number: 3

报错 ORA-03113: end-of-file on communication channel 的处理办法是，重新开一个shell， 

[oracle@master2 ~]$ sqlplus sys/Ideal123 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 25 18:20:46 2021

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL> show parameter background_dump;
ORA-01034: ORACLE not available
Process ID: 169678
Session ID: 233 Serial number: 2799

 查看oracle的日志地址

SQL> show parameter background_dump;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest		     string	 /data1/app/oracle/diag/rdbms/orcl/orcl/trace

 

主要修改逻辑

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size		    2215064 bytes
Variable Size		 1325400936 bytes
Database Buffers	  788529152 bytes
Redo Buffers		   21741568 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 internal_use utf8;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size		    2215064 bytes
Variable Size		 1325400936 bytes
Database Buffers	  788529152 bytes
Redo Buffers		   21741568 bytes
Database mounted.
Database opened.
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.UTF8

SQL> 

限制用户操作的错误：operation requires database is in RESTRICTED mode

SQL> alter database character set internal_use utf8;
alter database character set internal_use utf8
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode   # 限制用户操作
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 

System altered.

SQL> alter database character set internal_use utf8;

Database altered.

限制用户操作时重新登录会报错：ORA-12526: TNS:listener: all appropriate instances are in restricted mode

解决办法：

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.