|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Error in updating CONTEXT Index
hi,
we need to update the index created on a CLOB field after every insert,delete or update of data. Code we used to create trigger is shown below: CREATE TRIGGER scott.S_SEARCH_TRIGGER AFTER INSERT OR DELETE OR UPDATE ON scott.S_SEARCH BEGIN dbms_utility.exec_ddl_statement('alter index scott.s_search.index rebuild online parameters(''sync memory 45M'')'); END; Trigger creation is successful..but whenever we do an insert,update or delete the following error is showing up: for ex: if we issue a query like insert into scott.s_search values(6,'ertet') ERROR at line 1: ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option ORA-06512: at "SYS.DBMS_SYS_SQL", line 826 ORA-06512: at "SYS.DBMS_SQL", line 32 ORA-06512: at "SYS.DBMS_UTILITY", line 550 ORA-06512: at "SCOTT.S_SEARCH_TRIGGER", line 2 ORA-04088: error during execution of trigger 'SCOTT.S_SEARCH_TRIGGER' Can anybody tell y is this so? or does nebody have a better code to rebuild CONTEXT index? any help would be highly appreciated.. Regards. Last edited by tomkliff : October 31st, 2004 at 09:49 PM. |
|
#2
|
|||
|
|||
|
Got it...(this may be useful for beginners
)Instead of trigger, we created a procedure to update index and submitted that procedure via dbms_jobs.submit to be run in regular intervals. Code we used to create procedure: create or replace procedure scott.index_rebuild_procedure is begin dbms_utility.exec_ddl_statement('alter index scott.pobox_index rebuild online parameters(''sync memory 45M'')'); end index_rebuild_procedure; Submitted the above procedure using PL/SQL with an interval of ONE Minute as: DECLARE v_jobno number; BEGIN dbms_job.submit(v_jobno, scott.index_rebuild_procedure ;',sysdate,'sysdate + 1/1440'); commit; END; |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > Error in updating CONTEXT Index |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|