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

Thanks, that helps. Just for readers of this thread with similar problems. The correct syntax is:
... PARAMETERS ('SYNC(ON COMMIT)'); Cheers,
Jan

On 22 Jan., 17:18, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -
Received on Wed Jan 23 2008 - 07:01:28 CST