Re: Privilege Error while setting event

I have written some notes about this change in my fulltrace.sql demo file:

https://github.com/tanelpoder/tpt-oracle/blob/master/aot/fulltrace.sql

It was possible to set 10079 event (and parameters like _oradbg_pathname) with just ALTER SESSION in past, but they've locked this specific event down for security reasons apparently. So for that event you need to either use either DBMS_SYSTEM.SET_EV or ORADEBUG EVENT 10079... or ORADEBUG DUMP SQLNET_SERVER_TRACE 16 syntax, as I have mentioned in my script.

--
Tanel Poder
https://tanelpoder.com



On Sat, Oct 16, 2021 at 3:48 PM Lok P <loknath.73_at_gmail.com> wrote:



>  Apology if I understood it wrong. But it seems still missing something.


> The same alter session event 14529 works fine in another database from a

> different user though,  even with execute immediate. So it may not be that

> exact restrictions applied to this event which is mentioned in the oracle

> doc for event 10079. Please correct me if wrong.

>

> On Sun, 17 Oct 2021, 12:56 am Rajeev Prabhakar, <rprabha01_at_gmail.com>

> wrote:

>

>> Lok,

>>

>> Did you already check oracle support portal ?

>> Doc ID 2199860.1

>> Rajeev

>>

>> On 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