Oracle Database Discussions

Rebuild is possible. But,You can't specify NOLOGGING.

Try with,

ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD PARAMETERS ('TABLESPACE=NEW_TBS');


HTH
-Anantha

I tried without the NOLOGGING option, with the same results.
I tried your method, the following resulted:

ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD PARAMETERS ('TABLESPACE=INFOWH')
Error at line 3
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX IDXCLAIMSBINARY2 failed
DRG-11000: invalid keyword TABLESPACE=INFOWH

you need to create a preference first:

begin
ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE', 'tablespace ...'); 
ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE','tablespace ...'); 
ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE','tablespace ...'); 
ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE','tablespace ...'); 
ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE','tablespace ...'); 
end;
/

alter index ... rebuild parameters ('storage mystore');

I guess this was too simple. I just issued the following: ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD
This is without any options.

You could do that ;) - I assumed you wanted to change the tablespace - my mistake...

Is there a way to do this in a tirgger?

Here is the trigger that is design to load the document text into a blob.
I need to then rebuild index the domain index on the blob (TEXT). The following results in a error message during
the ALTER INDEX ... REBUILD :

- TRIGGER CLAIM SOURCE REF [LOG] AFTER INSERT FOR EACH ROW
CREATE OR REPLACE TRIGGER TRG_CLAIM_SOURCE_REF_LOG_AI
AFTER INSERT ON CLAIM_SOURCE_REF_LOG FOR EACH ROW
DECLARE
vStr VARCHAR2(1000) := '';
DEST_LOC BLOB;
SRC_LOC BFILE := BFILENAME('CLAIMS_DIR', 'FAQ- NOROVIRUS.doc');
vClaimKey_ID CLAIM_SOURCE_REF.CLAIM_SOURCE_REF_ID%TYPE;
vFileName CLAIM_SOURCE_REF.FILE_NAME_UNC%TYPE;


BEGIN
vClaimKey_ID := :NEW.CLAIM_SOURCE_REF_ID_FK;
-- UPDATE FILE NAME
UPDATE IW.CLAIM_SOURCE_REF SET CLAIM_SOURCE_REF.FILE_NAME_UNC = REPLACE(CLAIM_SOURCE_REF.FILE_NAME_UNC,'\', '/')
WHERE CLAIM_SOURCE_REF_ID = vClaimKey_ID;

SELECT FILE_NAME_UNC INTO vFileName FROM IW.CLAIM_SOURCE_REF WHERE CLAIM_SOURCE_REF_ID = :NEW.CLAIM_SOURCE_REF_ID_FK;
UPDATE IW.CLAIM_SOURCE_REF SET text = EMPTY_BLOB() WHERE CLAIM_SOURCE_REF_ID = :NEW.CLAIM_SOURCE_REF_ID_FK;
DBMS_OUTPUT.PUT_LINE('- > ' || vClaimKey_ID || ' ->' || vFileName);

-- LOAD TEXT
SRC_LOC := BFILENAME('CLAIMS_DIR', vFileName);

IF DBMS_LOB.FILEEXISTS (SRC_LOC) = 1 THEN
SELECT text INTO DEST_LOC FROM CLAIM_SOURCE_REF
WHERE CLAIM_SOURCE_REF_ID = vClaimKey_ID FOR UPDATE;
DBMS_LOB.OPEN(SRC_LOC, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LOADFROMFILE(DEST_LOC, SRC_LOC, dbms_lob.getlength(SRC_LOC));
DBMS_LOB.CLOSE(SRC_LOC);
UPDATE IW.CLAIM_SOURCE_REF SET TEXT_LOADED = 'Y' WHERE CLAIM_SOURCE_REF_ID = vClaimKey_ID;
END IF;

-- REBUILD THE INDEX
vStr := 'ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD ';
EXECUTE IMMEDIATE (vStr);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.PUT_LINE('Possible error while trying to load: ' || vFileName);
RAISE_APPLICATION_ERROR (-20001, 'ERROR OCCURED');
END TRG_CLAIM_SOURCE_REF_LOG_AI;
/

This is absolutely the wrong way of doing it. If you want to synchronize an index on commit then you can specifiy it at the time you create the index.

create index
...
...
parameters ('lexer my_lexer sync (on commit)');

Note this option is only available from 10g and above.

Or else you could opt to synhronizes the index periodically (again in 10g):

create index
...
...
parameters ('lexer my_lexer sync (every ...)');

In versions older than 10g you could opt to synhronizes the index periodically via a dbms_job by calling the ctx_ddl.sync_index.

Thanks!!
Setting the parameter works greatly.

Thanks.