Re: alter index (Oracle Text index) rebuild on different schema => no rights

On Jan 21, 2:17 pm, Krabatz <krab..._at_gmail.com> wrote:
> Hi everyone,
>
> I found a solution for myself now. No idea if that is the easiest way.
> However, it works.
>
> I put the "alter index" statement in a stored procedure and put it in
> a package owned by the data_user. The execution of the package has to
> be granted to the app_user.
>
> Here is how it works in detail:
>
> CREATE OR REPLACE PACKAGE data_user_package AS
> PROCEDURE rebuild_index;
> END data_user_package;
> /
>
> CREATE OR REPLACE PACKAGE BODY data_user_package AS
> PROCEDURE rebuild_index IS
> BEGIN
> -- Not allowed to execute DDL statements within a
> -- procedure. Execute it dynamically as a workaround.
> EXECUTE IMMEDIATE 'ALTER INDEX data_user.my_oracle_text_index
> REBUILD';
> END;
> END data_user_package;
> /
>
> Grant to app_user as data_user:
> grant execute on data_user_package to app_user;
>
> Call procedure as app_user:
> exec data_user.data_user_package.rebuild_index;
>
> Works for me. BTW: that was the answer I expected from the outset ;-)
>
> Cheers,
> Jan Mutter.
>
> Krabatz schrieb:
>
> > Hi,
>
> > thanks for your answer.
>
> > You write:
>
> > > > Is it possible to give a user such a special right?
>
> > > Certainly it is. Is it wise or prudent to do so? Not usually, and
> > > this doesn't appear to be a special case.
>
> > I don't see another alternative than that when the customer wants to
> > check if the changes he made went into the system.
>
> > scenario:
> > - application admin user changes data (application makes insert or
> > update)
> > - application admin user wants to test it and uses the search
> > functionality (application uses Oracle Text)
> > - no search results (customer dials my number, swearing)
>
> > Every automated solution has a delay which will be suitable in most
> > cases but sometimes the customer wants a result immediatley. Thus he
> > needs a bottom to invoke the rebuild of the index manually and that's
> > why I need this right for the application user.
> > I see and understand your concerns, but I can't see a different
> > solution than that.
>
> > You say, it is possible to give that special right to the application
> > user. I looked up the grant documentation (http://download.oracle.com/
> > docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2155015) but
> > couldn't find the correct syntax for it.
>
> > Thanks,
> > Jan.

This IS NOT a correct way to synchronize a Text index (which is what you essentially try to achieve.) Every time you execute that ALTER INDEX ... REBUILD statement without proper PARAMETERS clause (which should be 'SYNC',) you LOCK the table and the index and rebuild the index completely, that is, essentially, drop and recreate it. As data will be added to the table, your users will experience longer and longer delays while the index is being rebuilt - and they will be unable to perform *any* queries against the table meanwhile (and no DML, too, since you didn't include ONLINE keyword as well.)

What you should've done is simply create the index with proper parameters:

CREATE INDEX idx$blah ON blahblah.blah INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC ON COMMIT'); This would result in the index being automatically synchronized on commit. No need for extra code or grants, minimal delays on commits (as Text will need to do its work on new/updated data before commit returns control and it takes some time,) no hassle.

I would highly recommend that you thoroughly read through the Oracle Text Reference, understand the concepts and familiarize yourself with available options and procedures before you start implementing incorrect, inefficient and potentially damaging solutions to a problem that doesn't actually exist.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Jan 22 2008 - 10:18:17 CST