database - 使用来自 dbms_metadata.get_ddl 的 TIMESTAMP 表达式创建基于函数的索引后出现 ORA-01882

Question

我们有一个很大的现有脚本，可以在我们的客户数据库(的克隆)中删除和重新创建表。我们的客户可能稍微更改了表或索引定义，因此我们的脚本尝试使用 dbms_metadata.get_ddl 的输出来重新创建表，但我们在使用基于函数的索引和时间戳表达式时遇到了问题。模拟客户表的简约示例:

create table t(a timestamp, b timestamp);
create index idx_ta on t (nvl(a, TO_DATE('2010-01-02 03:04:05','YYYY-MM-DD HH24:MI:SS')));
create index idx_tb on t (nvl(b, TO_DATE('2010-01-02 03:04:05','YYYY-MM-DD HH24:MI:SS')));

我们的脚本试图通过处理 dbms_metadata.get_ddl 的输出来查看现有数据库。例如:

select dbms_metadata.get_ddl('INDEX','IDX_TA') from dual;

输出(裁剪):CREATE INDEX "MYUSER"."IDX_TA"ON "MYUSER"."T"(NVL("A",TIMESTAMP' 2010-01-02 03:04:05'))

我们的脚本读取此输出并尝试使用它来重新创建表和索引，如下所示(我将在此处将我们的脚本创建的克隆称为 U 以区分重新创建的版本与原始版本):

create table u(a timestamp, b timestamp);
create index idx_ua on u (nvl(a, TIMESTAMP' 2010-01-02 03:04:05'));
create index idx_ub on u (nvl(b, TIMESTAMP' 2010-01-02 03:04:05'));

idx_ua 创建时没有错误消息，但是 create index idx_ub 失败并显示:

SQL Error: ORA-01882: tidszoneregionen  blev ikke fundet
01882. 00000 -  "timezone region not found"

一般来说，创建 idx_ua 后一切都会失败，例如 insert into u values (null,null); 失败并显示相同的错误消息。

idx_ua 看起来像这样(来自 get_ddl 的裁剪输出):CREATE INDEX "MYUSER"."IDX_UA"ON "MYUSER"."U"(NVL("A",TIMESTAMP' 2010-01-02 03:04:05,000000000'))

我们尝试执行 alter session set nls_timestamp_tz_format=... 以确保 get_ddl 的输出将使用预定的时间戳格式，但它没有效果。事实上，get_ddl 为不同的索引输出不同的时间戳格式，尽管据我们所知，我们所有的索引都是以相同的方式创建的。我们怀疑这取决于用于创建索引的客户端。这也意味着 get_ddl 的输出在涉及时间戳时基本上是无用的。

我们在 Oracle 11 和 12 上都进行了尝试。此处的示例仅使用 SQL Developer。

我们需要一种(更)可靠的方法来自动删除和重新创建上述表格。使用 get_ddl 的替代方法，调整一些影响 get_ddl 的参数，对包含时间戳的索引运行一些额外的查询 - 完成工作的一切。

Answer 1

作为解决方法，请在应用索引之前执行以下操作。

alter session set NLS_NUMERIC_CHARACTERS = ',.';

该错误由 Oracle 错误 16731148 引起，发生在您创建基于函数的索引(涉及时间戳)而您的 NLS_NUMERIC_CHARACTERS 设置不是“,.”之后。由于 NLS 设置，该错误导致 Oracle 在时间戳表示形式 (TIMESTAMP' 2010-01-02 03:04:05,000000000') 中错误地生成逗号，即使时间戳应该具有独立于 NLS 的语法。 11.2存在该错误，12.2.0.3修复。

如果您的数据库已经损坏，您必须删除相关索引，然后在如上所述设置 NLS_NUMERIC_CHARACTERS 后重新创建它们。如果简单的 select 1 from T 导致 ORA-01882 错误，您可以快速确定表 T 是否有损坏的索引。