oracle - 为什么调度器链步骤名的最大长度是 24 个字节？

Question

我正在尝试创建一个 30 字节长的链步。它正在失败。

设置:

SQL> begin
  2      dbms_scheduler.create_program(
  3         program_name => lpad('A', 30, 'A')
  4       , program_type => 'PLSQL_BLOCK'
  5       , program_action => 'begin null; end;'
  6         );
  7      dbms_scheduler.create_chain('CHAIN_NAME');
  8  end;
  9  /

PL/SQL procedure successfully completed.

创建链步骤:

SQL> begin
  2      dbms_scheduler.define_chain_step(
  3          chain_name => 'CHAIN_NAME'
  4        , step_name => lpad('A', 30, 'B')
  5        , program_name => lpad('A', 30, 'A')
  6          );
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-27465: invalid value BBBBBBBBBBBBBBBBBBBBBBBBBBBBBA for attribute step_name
ORA-06512: at "SYS.DBMS_ISCHED", line 5057
ORA-06512: at "SYS.DBMS_ISCHED", line 1760
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1786
ORA-06512: at line 2

我有一个字节语义数据库。

描述 DBA_SCHEDULER_CHAIN_STEPS STEP_NAME 的最大长度为 128 个字节，即 32 个 4 字节 unicode 字符:

SQL> desc dba_scheduler_chain_steps;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 CHAIN_NAME                                NOT NULL VARCHAR2(128)
 STEP_NAME                                 NOT NULL VARCHAR2(128)
 ...

如果我查看 DBA_SCHEDULER_CHAIN_STEPS 的定义，那么 STEP_NAME 列来自 SYS.SCHEDULER$_STEP.VAR_NAME。该列的定义是:

SQL> select data_type, data_length, char_length, char_used
  2    from dba_tab_columns
  3   where owner = 'SYS'
  4     and table_name = 'SCHEDULER$_STEP'
  5     and column_name = 'VAR_NAME';

DATA_TYPE DATA_LENGTH CHAR_LENGTH C
--------- ----------- ----------- -
VARCHAR2          128         128 B

换句话说，对象名称的最大大小为 128 字节(32 个 Unicode 字符)。

Section 29.2 "About Scheduler Objects and Their Naming" Oracle 数据库管理员指南说

Scheduler objects follow the naming rules for database objects exactly and share the SQL namespace with other database objects.

In other words

Names must be from 1 to 30 bytes long...

但是，如果我创建一个名称为 24 字节长和名称为 25 字节长的链步骤，则 24 字节名称将成功，而 25 字节将失败

SQL> begin
  2      dbms_scheduler.define_chain_step(
  3          chain_name => 'CHAIN_NAME'
  4        , step_name => lpad('A', 24, 'B')
  5        , program_name => lpad('A', 30, 'A')
  6          );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> begin
  2      dbms_scheduler.define_chain_step(
  3          chain_name => 'CHAIN_NAME'
  4        , step_name => lpad('A', 25, 'B')
  5        , program_name => lpad('A', 30, 'A')
  6          );
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-27465: invalid value BBBBBBBBBBBBBBBBBBBBBBBBA for attribute step_name
ORA-06512: at "SYS.DBMS_ISCHED", line 5057
ORA-06512: at "SYS.DBMS_ISCHED", line 1760
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1786
ORA-06512: at line 2

:-(

为什么？这在任何地方都有记录吗？

Answer 1

根据 MoS Doc ID 2246248.1 :

This restriction of DBMS_SCHEDULER.define_chain_step attribute “step_name” is explicitly coded and confirmed by development.

-- Attribute 'step_name' cannot be longer than 24 characters or contain '.'

该文档仅引用 11.2.0.3，但它仍然在 12.1 和 12.2 中抛出错误。

这并不能真正解释原因，但它有一定的记录...只是不在文档中。