Re: Privilege Error while setting event

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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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
>>>
>>

--
http://www.freelists.org/webpage/oracle-l