从dbms_metadata.get_ddl使用TIMESTAMP表达式创建基于函数的索引后，ORA-01882 [英] ORA-01882 after creating function based index with TIMESTAMP expression from dbms_metadata.get_ddl

问题描述

我们有一个很大的现有脚本，该脚本可以在客户数据库（的副本）中删除并重新创建表。我们的客户可能已经稍微更改了表或索引的定义，因此我们的脚本试图使用 dbms_metadata.get_ddl 的输出来重新创建表，但是基于函数的索引存在问题时间戳表达式。模仿客户表的简约示例：

We have a big existing script that drops and recreates tables in (a clone of) our customers database. Our customer might have changed table or index definitions slightly, so our script tries to use the output from dbms_metadata.get_ddl to recreate the tables, but we have problems with function based indices with timestamp expressions. Minimalistic example emulating a customer table:

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 。例如：

Our script attempts to look at the existing database by processing the output from dbms_metadata.get_ddl. For example:

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

输出（已裁剪）：创建索引 MYUSER。 IDX_TA打开 MYUSER。 T（NVL（ A，TIMESTAMP'2010-01-02 03:04:05'））

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

Our scripts reads this output and attempts to use it to recreate the table and indices like this (I will call the clone created by our script U here to distinguish the recreated version from the original):

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 创建时没有错误消息，但是创建索引idx_ub 失败，原因是：

idx_ua is created without error messages, but create index idx_ub fails with:

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

创建 idx_ua 后，所有操作都会失败，例如插入u值（null，null）； 会失败，并显示相同的错误消息。

In general, everything fails after creating idx_ua, for example insert into u values (null,null); fails with the same error message.

idx_ua 看起来像这样（从get_ddl裁剪输出）： CREATE INDEX MYUSER 。 IDX_UA ON MYUSER。 U（NVL（ A，TIMESTAMP'2010-01-02 03：04：05,000000000'））

idx_ua looks like this (cropped output from 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 的输出在时间戳上基本上是无用的。

We tried doing alter session set nls_timestamp_tz_format=... to make sure the output from get_ddl will use a predetermined timestamp format, but it has no effect. In fact, get_ddl outputs different timestamp formats for different indices, even though as far as we know, all our indices were created in the same way. We suspect it depends on the client that was used to create the index. This also means the output from get_ddl is essentially useless when it comes to timestamps.

我们在Oracle 11上都尝试过和12.这里的示例仅使用SQL Developer。

We tried on both Oracle 11 and 12. The examples here use only SQL Developer.

我们需要的是（一种）更可靠的方式来自动删除和重新创建上面的表。使用get_ddl的替代方法，调整一些会影响get_ddl的参数，对包含时间戳的索引运行一些附加查询-完成任务的一切。

What we need is a (more) reliable way to drop and recreate tables like the above in an automated way. Using an alternative to get_ddl, tweaking some parameters that affect get_ddl, running some additional query for indices containing timestamps - whatever gets the job done.

推荐答案

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

As a workaround, execute the following before applying your indexes.

alter session set NLS_NUMERIC_CHARACTERS = ',.';

该错误是由Oracle错误16731148引起的，并且在您创建包含时间戳的基于函数的索引而您的NLS_NUMERIC_CHARACTERS后发生设置不是，。。该错误会导致Oracle由于NLS设置而错误地在时间戳记表示形式（TIMESTAMP'2010-01-02 03：04：05,000000000'）中生成逗号，即使时间戳记应具有与NLS无关的语法。该错误存在于11.2中，并且已在12.2.0.3。中修复。

The error is caused by Oracle bug 16731148 and occurs after you create function based indices involving timestamps while your NLS_NUMERIC_CHARACTERS setting is not ',.'. The bug causes Oracle to erroneously generate a comma in the timestamp representation (TIMESTAMP' 2010-01-02 03:04:05,000000000') due to NLS settings even though the timestamp should have an NLS-independent syntax. The error exists in 11.2, and is fixed in 12.2.0.3.

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

If your database is already corrupted, you must drop the relevant indices, and then recreate them after setting NLS_NUMERIC_CHARACTERS as indicated above. You can quickly determine if a table T has a corrupted index if a simple select 1 from T results in the ORA-01882 error.

这篇关于从dbms_metadata.get_ddl使用TIMESTAMP表达式创建基于函数的索引后，ORA-01882的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！