Definer's rights procedures have to be executed in a recursive session for obvious reasons. So the event was being signalled in a different session to that in which it had been set, and apparently Oracle did not like that. The invoker's rights suggestion was based on a hunch that this might be the problem.

You can turn off any event by setting the level to 0, or by using the keyword "off" in the event syntax. See the APT script trace_event_off.sql for an example.

You've found the solution. Do you know why this is the case? Also is it possible to turn off a session level event?

What about making it an invoker's rights procedure?

I have discovered that if the index rebuild occurs within a procedure, then if the event 10032 is set, the procedure will fail. Consider the following ...

SQL> alter session set events '10032 trace name context forever, level 1';

Session altered.

SQL> -- works interactively 
SQL>
SQL> alter index METPRO.ANALYSES_PF2
  2  rebuild initrans 4 storage (initial 7529242 next 627437  pctincrease 5)
  3  tablespace METPROINDEXD parallel ;

Index altered.

SQL> -- works from an anonymous PL SQL block 
SQL>
SQL> declare
  2  l_sql varchar2(500); -- hold the slq statements
  3  begin
  4  l_sql := 'alter index METPRO.ANALYSES_PF2'
  5  || ' rebuild initrans 4 storage (initial 7529242 next 627437 pctincrease 5 )'
  6  || ' tablespace METPROINDEXD parallel' ;
  7  EXECUTE IMMEDIATE l_sql;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> -- fails from a procedure
SQL> 
SQL> create or replace procedure test_rebuild is
  2
  3  l_sql varchar2(500); -- hold the slq statements
  4
  5  BEGIN
  6     l_sql := 'alter index METPRO.ANALYSES_PF2'
  7     || ' rebuild initrans 4 storage (initial 7529242 next 627437 pctincrease 5 )'
  8     || ' tablespace METPROINDEXD parallel' ;
  9     EXECUTE IMMEDIATE l_sql;
 10  END;
 11  /

Procedure created.

SQL> sho err
No errors.
SQL>
SQL> exec test_rebuild
BEGIN test_rebuild; END;
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P015
ORA-01031: insufficient privileges
ORA-06512: at "ENTERPRISE.TEST_REBUILD", line 9
ORA-06512: at line 1

SQL> -- turn the event off - only way I new to do this was to disconnect and reconnect
SQL>
SQL> disc
Disconnected from Oracle8i Enterprise Edition Release 8.1.5.1.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.1.0 - Production
SQL> conn a@b
Enter password: *
Connected.
SQL> exec test_rebuild

PL/SQL procedure successfully completed.

SQL>

Do you have an explanation for this?

Sorry for the delay on this. There is nothing wrong with your event setting. I've just tried to reproduce this, but it did not reproduce for me. Did P014 dump a trace file? If not, and if you can set sql_trace in the init.ora, that may show where (and thus why) the error happened.

I have a batch job that is run regularly that analyses indexes and rebuilds those that meet certain criteria. This is 815 on NT with indexes set to a parallel degree of default.

Using Ixora's disk_sorts.sql script, I found that this job did 53 disk sorts, average size of 12583K with a peak concurrent of 30. Based on advice in the December 2000 Ixora News, I thought I would try setting event 10032. This is where I have run into some problems - I am not very familiar with setting events and used the following syntax:

alter session set events '10032 trace name context forever, level 1';

I then ran my script - a trace file got produced and it seemed to contain the right sort of information. However, the rebuild index commands failed with an insufficient privileges error:

ORA-12801: error signaled in parallel query server P014
ORA-01031: insufficient privileges

The script works ok if I have not tried to set this event. Is this a problem in how I have tried to set the event or in something else?