ORA-8102 On Select / Update with Function Based Index using Timestamp Column

(Doc ID 568005.1)

Last updated on JULY 11, 2012

Applies to:

Oracle Server - Enterprise Edition - Version and later
Information in this document applies to any platform.


ORA-8102 can reported on tables with Function Based Index if NLS_TIMESTAMP_FORMAT 
is different from the session that created the INDEX.
alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

"C1" VARCHAR2(23),
"C2" TIMESTAMP (0) DEFAULT '1970-01-01 00:00:00');

CREATE INDEX "I1" ON T1(C2 || RPAD(NVL(C1, ' '), 23));
INSERT INTO T1 values ( 1, systimestamp-1 );
analyze table T1 validate structure cascade;
alter session set NLS_TIMESTAMP_FORMAT = 'HH24:MI:SS';
UPDATE T1 set c2 = systimestamp ;
analyze table T1 validate structure cascade;


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms