Oracle12c修改字符集的方法(解决数据导入后中文乱码及ORA-12899错误)

之前在Windows上安装的Oracle，现在迁移到Linux上，把dmp文件导入Linux的时候发现字段的注释和存储过程中的中文是问号？，而且导入的时候还会报ORA-12899错误。其实这些都是字符集问题。

1、查询当前字符集

select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

如果不是ZHS16GBK说明确实是字符集问题。

2、关闭数据库

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

3、启动数据库到mount状态

SQL> startup mount ORACLE instance started. Total System Global Area 205520896 bytes Fixed Size 1266608 bytes Variable Size 100666448 bytes Database Buffers 100663296 bytes Redo Buffers 2924544 bytes Database mounted.

4、限制session

SQL> alter system enable restricted session; System altered.

5、禁用作业调度进程，确保无进程调用数据库

先查询之前的值

SQL> show parameter job_queue_processes;

把参数置为0

SQL> alter system set job_queue_processes=0; System altered.

6、打开数据库

alter database open;

7、修改字符集

SQL> alter database character set internal_use ZHS16GBK; Database altered.

8、查询当前字符集

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; PARAMETER VALUE ---------------------------------------- ---------------------------------------- NLS_CHARACTERSET ZHS16GBK

9、重复2关闭数据库和3启动数据库到mount状态并将作业调度进程参数调回之前的值

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 205520896 bytes Fixed Size 1266608 bytes Variable Size 100666448 bytes Database Buffers 100663296 bytes Redo Buffers 2924544 bytes Database mounted. SQL> alter system set job_queue_processes=110; System altered.

10、恢复session

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION; System altered.

11、打开数据库

SQL> alter database open; Database altered.

到此这篇关于Oracle 12c修改字符集的方法(解决数据导入后中文乱码及ORA-12899错误)的文章就介绍到这了,