Oracle 10g ugly index/trigger bug

today, I found a ugly bug in oracle 10g:

CREATE TABLE TABLE_TESTBUG (
ID_REG NUMBER NOT NULL PRIMARY KEY,
DT_FIELD1 TIMESTAMP(6),
STATUS NUMBER(10)
);

CREATE OR REPLACE TRIGGER TG_TESTBUG
after INSERT OR UPDATE ON TABLE_TESTBUG
FOR EACH ROW
DECLARE
V_VAR NUMBER;
BEGIN
SELECT COUNT(*) INTO V_VAR
FROM DUAL
WHERE TRUNC(SYSDATE)=TRUNC(:new.DT_FIELD1);
END;
/

SQL> INSERT INTO TABLE_TESTBUG (ID_REG, DT_FIELD1, STATUS) VALUES (1, SYSDATE, 1);
1 row created.
SQL> INSERT INTO TABLE_TESTBUG (ID_REG, DT_FIELD1, STATUS) VALUES (2, SYSDATE, 1);
1 row created.
SQL> INSERT INTO TABLE_TESTBUG (ID_REG, DT_FIELD1, STATUS) VALUES (3, SYSDATE-1, 1);
1 row created.

SQL> CREATE INDEX IDX_TESTBUG_1 ON TABLE_TESTBUG (TRUNC(DT_FIELD1),STATUS);
Index created.

SQL> UPDATE TABLE_TESTBUG SET STATUS=3 WHERE TRUNC(DT_FIELD1)=TRUNC(SYSDATE);
UPDATE TABLE_TESTBUG SET STATUS=3 WHERE TRUNC(DT_FIELD1)=TRUNC(SYSDATE)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 3284 Serial number: 2922

In database log:

Errors in file /opt/oracle/admin/*****/udump/*****_ora_24645.trc:

ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] [Invalid permissions for mapped object] [0x2AE125E59000] [] []

If you drop the index or disable the trigger or do not use the timestamp field in update, everythings is alright...

I has received it in 5 different oracle databases but all in version 10.2.0.4


The way we founded was create a index by the full field timestamp, not using trunc();

SQL>drop index idx_testbug_1;
Index dropped.

SQL>create index idx_textbug_1 on table_testbug (dt_field1);
Index created.

All things works ok... I know... it's not the best option, but a workaround...
Remember:

To use index you need to use "between", not "trunc"

no more for now...