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 -
DEVdesc 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_SEQUENC E 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_AUDIT S 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 -
DEVdesc 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?
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 -
DEVdesc 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_SEQUENC E 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_AUDIT S 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 -
DEVdesc 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?