This tip is especially useful when migrating to autonomous. The reason is that to enable the magic of the the autonomous database there are things that cannot be done. One such thing is that there are a lot of session settings you cannot use.

To see what APIs you can no longer use you can look at the documentation. If you look at alter session there are a number of “alter session set …” that are available. But many you are used to are not.

SO what is the big deal, if autonomous solves it for me it’ll save me having to issue those commands. True, for adhoc work you just don’t issue them. However, imagine that your source code uses some of it in many places. Wouldn’t it be nice to just set that those things should not report an error when they cannot be executed?

The issue you encounter is that you get an “ORA-01031 Insufficient privileges” when you ssue a command even as the ADMIN-account.

Here are three commands that I know is frequently set in custom code.

alter session set workarea_size_policy = manual;
alter session set sort_area_size = nnnnnn;
alter session set hash_area_size = nnnnnn;

Imagine you have a lot of places in the code that issues that or you have a closed source solution that uses it where you cannot modify the source and it fails on the ORA-01031 each of them get. It coule be either hard or close to impossible to work around.

The nice things is that Oracle provide a solution to define on a very granular level what errors we want to avoid seeing (or getting reported).

We can define that we do not want to see errors on this no matter what session it is issues in, that is we avoid it on a system-wide level.

alter system set ignore_session_set_param_errors = 
   workarea_size_policy, sort_area_size, hash_area_size scope=both;

I have not found a comprehensive list for what can be ignored. But the keywords named are those that follows “alter session set “.

Now when those commands are issued, they are of course still not taking effect but you do not get an error returned so the process continues just as if you had removed them from your code.

SQL> alter session set workarea_size_policy = manual;

Error starting at line : 1 in command -
alter session set workarea_size_policy = manual
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Document: YES
*Cause:    A database operation was attempted without the required
           privilege(s).
*Action:   Ask your database administrator or security administrator to grant
           you the required privilege(s).
SQL> alter session set ignore_session_set_param_errors = 
  2*    workarea_size_policy;

Session altered.

SQL> alter session set workarea_size_policy = manual;

Session altered.

SQL> select value from v$parameter where name = 'workarea_size_policy';

VALUE    
________ 
AUTO   

Here I change it only for the session, but you could persist it for the whole system as shown above. First we see the error ORA-1031 for the statement. Next we issue the command to ignore errors on workarea_size_policy and the command then returns no errors. We finsih by checking the session in the session and it is still AUTO. Thus, you got no error but it dod not change the fact that it cannot be set to manual in an autonomous database.

The above was tested on an ADW database created in April 2024. Just in case any of this changes in the future. Researching for this post indicates that this is a parameter that was introduced with 19.4.

I think being able to control on a keyword level what errors to ignore is pretty impressive. It is at least more than I expected.

Last note: The document linked above does not show that set ignore_session_set_param_errors can be used for alter session. The document for what parameters one can change shows it as one can modify in an autonomous database. The first document links to the latter for being able to be used with alter system.