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

On Jan 21, 2:17 pm, Krabatz <krab...@xxxxxxxxx> 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
.

Relevant Pages

  • Nested packages not firing events when executed via .NET interop
    ... This works fine on individual packages, but when I execute a package ... void PackageEvents.OnError(string EventSource, int ErrorCode, ...
    (microsoft.public.sqlserver.dts)
  • Re: Using Python in ActiveX task - works when run as separate step
    ... when executing the package. ... Peter Yang, if you can detail some of what you did to get Perl working, I'd ... >> was trying to use Python in this case because I know it a little better than ... >>>You could execute the step in DTS package window. ...
    (microsoft.public.sqlserver.dts)
  • Not all steps in DTS package execute
    ... When run through DTS Designer, all steps execute except the last one (Step ... DTSRun OnFinish: DTSStep_DTSActiveScriptTask_2 ... Microsoft Data Transformation Services Package ...
    (microsoft.public.sqlserver.dts)
  • Re: DTSStepExecResult, DTSStepExecStatus, and execute package task
    ... It's a step error within a package and not a package error. ... do you know of a way to execute a specific function within ... Dim ErrorSource ... > The exec package task worked, it executed the child package, so is a child ...
    (microsoft.public.sqlserver.dts)
  • Re: Package: symbol reading woes
    ... package hasn't been loaded at read time." ... Maybe I'll need to settle for eval? ... it looks to me (I didn't execute your code and don't ... maybe you can just load the library. ...
    (comp.lang.lisp)