[[toc]]

适用范围

11G

问题概述

通过sqlplus 创建job，报错ORA-01756

问题原因

系统字符集

问题复现

因为数据泵导入job时job号冲突，需要人工导入job。

ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
Failing sql is:
 BEGIN SYS.DBMS_IJOB.SUBMIT( JOB=> 144, LUSER=> 'DESTU', PUSER=> 'DESTU', CUSER=> 'DESTU', NEXT_DATE=> TO_DATE('2022-09-03 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(sysdate+ 1)  +0/ (24)', BROKEN=>  FALSE, WHAT=> 'repeat_apply_no;', NLSENV=> 'NLS_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_TERRITORY=''CHINA'' NLS_CURRENCY=''￥'' NLS_ISO_CURRENCY=''CHINA'
Job "SYS"."SYS_IMPORT_FULL_01" completed with 11 error(s) at 16:28:47

通过数据泵生成sqlfile获取创建job代码：

-- new object type path: SCHEMA_EXPORT/JOB
 BEGIN SYS.DBMS_IJOB.SUBMIT(
      JOB=> 144,   >>>>>> 144在目标库里已有相同job号
      LUSER=> 'SOURCEU',   >>>>>> 源库的schema_name
      PUSER=> 'SOURCEU',
      CUSER=> 'SOURCEU',
      NEXT_DATE=> TO_DATE('2022-09-03 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'),
      INTERVAL=> 'TRUNC(sysdate+ 1)  +0/ (24)',
      BROKEN=>  FALSE,
      WHAT=> 'repeat_apply_no;',
      NLSENV=> 'NLS_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_TERRITORY=''CHINA'' NLS_CURRENCY=''￥'' NLS_ISO_CURRENCY=''CHINA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_SORT=''BINARY''',
      ENV=> '0102000200000000');
      END;   
 /

简单人工修改后并创建job的结果：
报错：
SQL> BEGIN SYS.DBMS_IJOB.SUBMIT(
2 JOB=> 145,
3 LUSER=> ‘DESTU’,
4 PUSER=> ‘DESTU’,
5 CUSER=> ‘DESTU’,
6 NEXT_DATE=> TO_DATE(‘2022-09-03 00:00:00’, ‘YYYY-MM-DD:HH24:MI:SS’),
7 INTERVAL=> ‘TRUNC(sysdate+ 1) +0/ (24)’,
BROKEN=> FALSE,
8 9 WHAT=> ‘repeat_apply_no;’,
10 NLSENV=> ‘NLS_LANGUAGE=’‘SIMPLIFIED CHINESE’’ NLS_TERRITORY=’‘CHINA’’ NLS_CURRENCY=’‘￥’’ NLS_ISO_CURRENCY=’‘CHINA’’ NLS_NUMERIC_CHARACTERS=’’.,’’ NLS_DATE_FORMAT=’‘DD-MON-RR’’ NLS_DATE_LANGUAGE=’‘SIMPLIFIED CHINESE’’ NLS_SORT=’‘BINARY’’’,
ENV=> ‘0102000200000000’);
11 12 END;
13 /
ERROR:
ORA-01756: quoted string not properly terminated

解决方案

当前字符集：
NLS_LANG=american_america.ZHS16GBK
LANG=en_US.UTF-8

配置系统字符集：
export LANG="zh_CN.UTF-8"
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

重新创建job：
yibtdbtest:/dumpdir$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 2 17:50:23 2022

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


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

SQL>  BEGIN SYS.DBMS_IJOB.SUBMIT(
  2        JOB=> 145,
  3        LUSER=> 'DESTU',
  4        PUSER=> 'DESTU',
  5        CUSER=> 'DESTU',
  6        NEXT_DATE=> TO_DATE('2022-09-03 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'),
  7        INTERVAL=> 'TRUNC(sysdate+ 1)  +0/(24)',
  8        BROKEN=>  FALSE,
  9        WHAT=> 'repeat_apply_no;',
 10        NLSENV=> 'NLS_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_TERRITORY=''CHINA'' NLS_CURRENCY=''￥'' NLS_ISO_CURRENCY=''CHINA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_SORT=''BINARY''',
 11        ENV=> '0102000200000000');
 12        END;
 13   /

PL/SQL procedure successfully completed.

参考文档