CODEWITHSUNDEEP
oracle-databaserebuild
Heena Shaikh
Heena Shaikh

Reputation: 1

getting error while executing the rebuild index queries (ORA-29874 and ORA-29960)

Code:

create or replace procedure REBUILD_X_TEXT
IS

 err_code NUMBER;
 err_msg  VARCHAR2(100);
 
Begin
 
EXECUTE IMMEDIATE 'ALTER INDEX TWPROD.X_TEXT_ENTITY rebuild parameters(''REPLACE metadata sync(every "SYSDATE+15/1440")'')'; 
EXECUTE IMMEDIATE 'ALTER INDEX TWPROD.X_TEXT_ENTITY NOPARALLEL'; 

exception
 WHEN OTHERS THEN
     
      err_code := SQLCODE;
      err_msg  := SUBSTR(SQLERRM, 1 , 100);
      DBMS_OUTPUT.put_line('Rebuild index failed : Error code ' || err_code || ': ' || err_msg);
  

END REBUILD_X_TEXT;

Error:

Connecting to the database PROD
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX X_TEXT_ENTITY failed
DRG-50857: oracle error in drvddl.Resubmit_job
ORA-27486: insufficient privileges
ORA-06512: at "SYS.REBUILD_X_TEXT", line 7
ORA-06512: at line 2
Process exited.
Disconnecting from the database PROD.

Upvotes: 0

Views: 1338

Answers (1)

ekochergin
ekochergin

Reputation: 4129

I assume this is about a text index (since it's name is X_TEXT_ENTITY) and the procedure is being executed as a "normal" user other than sys.

In this case please check whether user has create table privilege and, if not, issue it. I see you know how to grant a privilege to a user, but I'll still posting the statement in case of someone will find the answer later.

grant create table on USERNAME;

The reason is for each TEXT index Oracle creates a set of tables and this is the most often reason why people see the "ORA-27486: insufficient privileges" when dealing with text indexes.

Since the problem occurs on altering index, you may need to play with "alter any table" privilege, which violates the database security. If the security aspect is a concern, you might wanted to check tables created for that index (their names start with "DR$INDEX_NAME%") and issue "alter table" grants to a user that executes REBUILD_X_TEXT

Upvotes: 1

Related Questions