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')));
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;
Output (cropped): CREATE INDEX "MYUSER"."IDX_TA" ON "MYUSER"."T" (NVL("A",TIMESTAMP' 2010-01-02 03:04:05'))
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 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"
In general, everything fails after creating idx_ua, for example insert into u values (null,null); fails with the same error message.
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'))
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.
We tried on both Oracle 11 and 12. The examples here use only SQL Developer.
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 = ',.';
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.
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.