Errors after creating FBI

From: Alan Cobban <alancobban_at_yahoo.co.uk>
Date: 30 Oct 2003 07:09:25 -0800
Message-ID: <a2b728db.0310300709.500040ba_at_posting.google.com>

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? Received on Thu Oct 30 2003 - 16:09:25 CET