Oracle 記一次ORA-00001問題處理

問題現象：

insert all方式插入資料提示如下：
ORA-00001: unique constraint (C##CJC.SYS_C008089) violated
經檢查插入的列並沒有重複的值。

問題原因：

中文欄位有主鍵約束，由於插入資料庫時中文亂碼，中文字元全部變成"?"，導致相同長度的中文欄位被識別為重複資料，導致無法插入。

解決方案：

正確配置NLS_LANG環境變數。

問題重現過程：

1.建立表t1

SQL> show user
USER is "C##CJC"
create table t1(t_name varchar2(300),t_status varchar2(5),primary key(t_name));

2.插入資料

insert all
into t1 values('人生七十古稀，','1')
into t1 values('我年七十為奇。','1')
into t1 values('前十年幼小，','1')
into t1 values('後十年衰老；','1')
into t1 values('中間只有五十年，','1')
into t1 values('一半又在夜裡過了。','1')
into t1 values('算來只有二十五年在世','1')
into t1 values('受盡多少奔波煩惱。','1')
select 1 from dual;

報錯如下：

ERROR at line 1:
ORA-00001: unique constraint (C##CJC.SYS_C008089) violated

報錯資訊：

[oracle@cjcos02 ~]$ oerr ora 00001
00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
//         For Trusted Oracle configured in DBMS MAC mode, you may see
//         this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.

為什麼插入的t_name沒有重複字，但是報錯ORA-00001呢？

沒有插入成功

SQL> col t1_name for a30
SQL> select * from t1;
no rows selected

嘗試逐條插入

第一條可以正常插入

SQL> insert into t1 values('人生七十古稀，','1');
1 row created.

第二條插入報錯ORA-00001

SQL> insert into t1 values('我年七十為奇。','1');
insert into t1 values('我年七十為奇。','1')
*
ERROR at line 1:
ORA-00001: unique constraint (C##CJC.SYS_C008089) violated

第三條可以正常插入

SQL> insert into t1 values('前十年幼小，','1');
1 row created.

第四條插入報錯ORA-00001

SQL> insert into t1 values('後十年衰老；','1');
insert into t1 values('後十年衰老；','1')
*
ERROR at line 1:
ORA-00001: unique constraint (C##CJC.SYS_C008089) violated

檢視插入結果

SQL> col t_name for a30
SQL> select * from t1
T_NAME        T_STA
------------------------------ -----
?????????????????????        1
??????????????????        1

檢查NLS_LANG配置

空，當前沒有設定。

[oracle@cjcos02 dbs]$ echo $NLS_LANG
[oracle@cjcos02 dbs]$ echo $LANG
en_US.UTF-8

檢視xshell字符集

檔案---屬性---終端---編碼

當前是:Unicode(UTF-8)

檢查Oracle資料庫伺服器字符集(伺服器端)：

即Oracle以哪種字元編碼儲存字元

NLS_LANGUAGE為:AMERICAN

NLS_CHARACTERSET為:AL32UTF8 

col PARAMETER for a30
col VALUE for a30
set pagesize 100
select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
PARAMETER        VALUE   CON_ID
------------------------------ ------------------------------ ----------
NLS_CHARACTERSET        AL32UTF8         1

col PARAMETER for a30
col VALUE for a30
set pagesize 100
select PARAMETER,VALUE from nls_database_parameters;
PARAMETER        VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION        19.0.0.0.0
NLS_NCHAR_CONV_EXCP        FALSE
NLS_LENGTH_SEMANTICS        BYTE
NLS_COMP        BINARY
NLS_DUAL_CURRENCY        $
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT        HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT        DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT         HH.MI.SSXFF AM
NLS_SORT        BINARY
NLS_DATE_LANGUAGE        AMERICAN
NLS_DATE_FORMAT         DD-MON-RR
NLS_CALENDAR        GREGORIAN
NLS_NUMERIC_CHARACTERS        .,
NLS_NCHAR_CHARACTERSET        AL16UTF16
NLS_CHARACTERSET        AL32UTF8
NLS_ISO_CURRENCY        AMERICA
NLS_CURRENCY        $
NLS_TERRITORY        AMERICA
NLS_LANGUAGE        AMERICAN
20 rows selected.

檢查客戶端作業系統字符集：

即客戶端作業系統以哪種字元編碼儲存字元。

：為AMERICAN_AMERICA.AL32UTF8

userenv函式返回當前會話（session）的相關資訊。

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

返回格式language_territory.characterset

配置NLS_LANG

格式：

NLS_LANG=<Language>_<Territory>.<Clients Characterset> 
LANGUAGE指定: 
-Oracle訊息使用的語言 
-日期中月份和日顯示 
TERRITORY指定 
-貨幣和數字格式 
-地區和計算星期及日期的習慣 
CHARACTERSET: 
-控制客戶端應用程式使用的字符集

配置：

[oracle@cjcos02 dbs]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@cjcos02 dbs]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8

再次插入資料

sqlplus c##cjc/******
SQL>
insert all
into t1 values('人生七十古稀，','1')
into t1 values('我年七十為奇。','1')
into t1 values('前十年幼小，','1')
into t1 values('後十年衰老；','1')
into t1 values('中間只有五十年，','1')
into t1 values('一半又在夜裡過了。','1')
into t1 values('算來只有二十五年在世','1')
into t1 values('受盡多少奔波煩惱。','1')
select 1 from dual;
8 rows created.

SQL> col t_name for a30
SQL> Select * from t1;
T_NAME        T_STA
------------------------------ -----
人生七十古稀，                 1
我年七十為奇。                 1
前十年幼小，                   1
後十年衰老；                   1
中間只有五十年，               1
一半又在夜裡過了。             1
算來只有二十五年在世           1
受盡多少奔波煩惱。             1
8 rows selected.
SQL> commit;
Commit complete.

插入成功

#####chenjuchao 20211016 14:35#####