ORA-01882 after creating functional index with TIMESTAMP expression from dbms_metadata.get_ddl
We have a large existing script that shrinks and recreates tables in our clients' database (clone). Our client may have changed the table or index definitions a bit, so our script is trying to use the output from dbms_metadata.get_ddl
to recreate the tables, but we have functional indexes issues with the timestamp expression. A minimalistic example of 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 tries to look at an 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 read this output and try to use it to recreate the table and indexes like this (I'll call the clone our script U created 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
generated with no error messages, but create index idx_ub
doesn't work:
SQL Error: ORA-01882: tidszoneregionen blev ikke fundet
01882. 00000 - "timezone region not found"
As a rule, after creation, idx_ua
everything fails, for example, it insert into u values (null,null);
exits with the same error message.
idx_ua
looks like this (trimmed output from get_ddl): CREATE INDEX "MYUSER"."IDX_UA" ON "MYUSER"."U" (NVL("A",TIMESTAMP' 2010-01-02 03:04:05,000000000'))
We tried alter session set nls_timestamp_tz_format=...
to make sure that the output from get_ddl
will use the predefined timestamp format, but it has no effect. In fact, it get_ddl
outputs different timestamp formats for different indexes, although as far as we know, all of our indexes were created the same way. We suspect this depends on the client that was used to create the index. It also means that inference from get_ddl
is essentially useless when it comes to timestamps.
We tested both Oracle 11 and 12. The examples here only use SQL Developer.
We need a (more) reliable way to automatically drop and re-create tables as above. By using an alternative to get_ddl, tweaking some parameters that affect get_ddl, doing some extra query for indexes containing timestamps - no matter what the job is doing.
source to share
As a workaround, do the following before applying your indexes.
alter session set NLS_NUMERIC_CHARACTERS = ',.';
The error is caused by Oracle error 16731148 and occurs after creating indexes based on functions related to timestamps while your NLS_NUMERIC_CHARACTERS parameter is not ",." The error causes Oracle to mistakenly generate a comma in the timestamp view (TIMESTAMP '2010-01-02 03: 04: 05,000000000) due to NLS settings, although the timestamp must have NLS-independent syntax. The bug exists in 11.2 and has been fixed in 12.2.0.3.
If your database is already corrupted, you must drop the corresponding indexes and then recreate them after setting NLS_NUMERIC_CHARACTERS as above. You can quickly determine if table T has a corrupted index if the downtime select 1 from T
results in an ORA-01882 error.
source to share
You shouldn't use TO_DATE(...)
on a column TIMESTAMP
, it requires an implicit conversion.
Better to use TO_TIMESTAMP('2010-01-02 03:04:05','YYYY-MM-DD HH24:MI:SS')
or TIMESTAMP '2010-01-02 03:04:05'
, which is the same, just written as a literal.
Are you sure the datatype is really TIMESTAMP
not TIMESTAMP WITH TIMEZONE
or TIMESTAMP WITH LOCAL TIMEZONE
?
source to share