alter index across schemas

Question

I am synchronizing tables using a stored PL/SQL procedure as part of a web application - when they hit a button it does the sync. The PL/SQL is executing as user A, but needs to disable indexes and sync a couple of tables in schema B. I can't get user A to 'alter index B.indexName unusable' despite granting it 'alter any index', and in desperation even DBA. The error is ORA-01418: specified index does not exist. This is working on another developer's sandbox but I can't figure out why. Any suggestions will be appreciated.

Answer 1

Are you granting the ALTER ANY INDEX privilege via a role? Or is the privilege being granted directly to user A?

Privileges granted through a role (such as DBA) are not available to definer's rights stored procedures (the default). Only privileges that are granted directly to the user are available in a definer's rights stored procedure.

Answer 2

Does the index exist? More than once, I've lost time by not taking an error message at face value.

Answer 3

It is granted directly to user A as sys.

Answer 4

Yes, per this statement run as user A:

select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS from DBA_CONSTRAINTS
WHERE UPPER(TABLE_NAME) = 'TABLE';

OWNER    CONSTRAINT NAME   CONSTRAINT TYPE    TABLE_NAME    STATUS
B         BTABLE_FKEY             R             TABLE        ENABLED

Answer 5

Oops, I got a little ahead of myself with the last post. The alter index is failing, and the SQL is exiting out before it deletes all from the table. Then it tries to do the inserts and fails on the integrity constraint.

Answer 6

I dropped the index and rebuilt it, which seems to have resolved the issue above. Now it lets me disable the index, but tells me the user has insufficient privs to alter index...rebuild online when I'm done populating the table. What privilege does user A need for that operation, if you would be so kind?