Note that you're using unusual syntax in the set events example above,
normally you'd run it without the "=" sign:
Use this:
alter session set events '14529 trace name context forever';
Instead of this:
alter session set events='14529 trace name context forever';
I wonder if you're hitting any glitch due to the syntax ...
On Sat, Oct 16, 2021 at 3:09 PM Lok P <loknath.73@xxxxxxxxx> wrote:
I don't see the entry in dba_sys_privs exactly for the user/schema in
which the package exists. However I am wondering if I set the
current_schema as the same user/schema(say USER1) which holds the package.
And execute below commands, then why the 'parallel_force_local' succeeds
while setting that event fails. So does it mean that it will need some
different privilege , not the 'alter session'?
SQL> alter session set current_schema=USER1;
Session altered.
Elapsed: 00:00:00.04
SQL> alter session set parallel_force_local=true;
Session altered.
Elapsed: 00:00:00.04
SQL> alter session set events='14529 trace name context forever';
ERROR:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.08
SQL>
On Sun, Oct 17, 2021 at 12:24 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
Do you see the entry in DBA_SYS_PRIVS for that user with privilege='ALTER
SESSION'?
On Sat, Oct 16, 2021 at 9:13 PM Lok P <loknath.73@xxxxxxxxx> wrote:
We are hitting a buggy scenario while doing partition exchange. And it
mainly appears when the base table has gone through an ALTER and added a
new not null column to it using the first column add method. The property
of the column changed and it doesn't match with the exchange table which is
created using the CREATE table statement. To avoid the error we are setting
the event below before creating the exchange table like it's explained in
the blog below. But we are getting "ORA-01031: insufficient privileges" at
run time , while setting this event in plsql procedure using execute
immediate like below which is part of a package. The same procedure does
have other ALTER statements like setting 'parallel_force_local' true,
setting 'current_schema' etc. But all of those work fine. So I want to
understand , what special permission is required to be able to set the
14529 event without error?
EXECUTE IMMEDIATE 'alter session set events ''14529 trace name context
forever, level 2''';
https://connor-mcdonald.com/2016/02/16/exchange-partition-revisited/
Regards
Lok
