Errors after creating FBI

[Alan Cobban]

Hello,

I'm in the process of tuning our auditing procedures. At present we
have one table, mp_event_audits which holds username, date/time,
change type, data changed etc columns -

DEV> desc mp_event_audits
Name Null? Type
------------------------------- -------- ----
EA_ID NOT NULL NUMBER(8)
EA_USER NOT NULL VARCHAR2(30)
EA_DATETIME NOT NULL DATE
EA_ACCESS NOT NULL VARCHAR2(1)
EA_EVENT NOT NULL VARCHAR2(500)
EA_TYPE VARCHAR2(4)
EA_FILE_SEQUENCE NUMBER(12)
EA_SEQUENCE NUMBER(12)

DEV>

We now have over 60000000 rows in this table and currently requests
for audit information generally require returning rows whose EA_EVENT
column contains an ID string. The current procedure is to SELECT *
WHERE ea_event LIKE '%<id_string>%'.

EA_EVENTS is a pipe (|) delimited list of data items changed and the
ID string's position can be determined using a combination of the
EA_ACCESS column and the first item in the EA_EVENTS string. I have
written a function, f_get_event_mp_seq, to retrieve this data. This
function takes as input two VARCHAR2 fields and returns a VARCHAR2.

I created an index on this function to allow a quick return of audit
data -

CREATE INDEX FBI_EVENT_AUDITS ON mp_event_audits
(f_get_event_mp_seq(EA_EVENT, EA_ACCESS))
TABLESPACE BIGDYNI PCTFREE 10
STORAGE (INITIAL 1280M NEXT 256M PCTINCREASE 0)
/

After creating this index any action on the mp_event_audits table
results in an ORA-00911 error -

DEV> desc mp_event_audits
ERROR:
ORA-00911: invalid character
DEV>

I have tried creating this index in another database environment where
mp_event_audits has 2000000 rows and the table and index work as
expected after creation.

Does anyone know why I receive an ORA-00911 error when I use a larger
set of data?