Why am I getting a ORA-44737 Parameter does not exist when setting the MAX_DUMP_FILE_SIZE parameter on Oracle 18c?

Question

Here is my code, pretty straight forward, but it wont work. Thank you all!

begin
    -- to set the max dump file size for a given session
    sys.dbms_system.set_int_param_in_session(
        217,
        36575,
        'MAX_DUMP_FILE_SIZE',
        1073741824
    );
end;

It generates:

ORA-44737: Parameter MAX_DUMP_FILE_SIZE did not exist.
ORA-06512: at "SYS.DBMS_SYSTEM", line 122
ORA-06512: at line 3

Answer 1

It appears that 18c is just reporting an error that 11g used to silently ignore; namely that the parameter name doesn't exist, or at least isn't the right type for the procedure. In 11g this didn't error, but also didn't do anything. As a normal user:

select name, type, display_value
from v$parameter
where name in ('max_dump_file_size', 'sort_area_size');

NAME                                 TYPE DISPLAY_VALUE
------------------------------ ---------- ------------------------------
sort_area_size                          3 65536
max_dump_file_size                      2 unlimited

As SYS after identifying that session:

begin
    sys.dbms_system.set_int_param_in_session(
        209,
        34295,
        'MAX_DUMP_FILE_SIZE',
        123456
    );
    sys.dbms_system.set_int_param_in_session(
        209,
        34295,
        'SORT_AREA_SIZE',
        123456
    );
end;
/

PL/SQL procedure successfully completed.

In the original user's session:

select name, type, display_value
from v$parameter
where name in ('max_dump_file_size', 'sort_area_size');

NAME                                 TYPE DISPLAY_VALUE
------------------------------ ---------- ------------------------------
sort_area_size                          3 123456
max_dump_file_size                      2 unlimited

The sort_area_size change has taken effect. The max_dump_file_size has not - but it didn't report that, or complain. In fact you can pass anything you like as the parameter name and it will still silently ignore it.

In 18c it is no longer silent, so you see the ORA-44737 error.

The reason it complains, it seems, comes down to the name of the procedure - set_int_param_in_session - and the parameter type. It works for sort_area_size because that is an integer parameter; but max_dump_file_size is a string parameter. It might be more helpful if the error said "Parameter MAX_DUMP_FILE_SIZE did not exist or was the wrong type", but you can't have everything.

You happen to be trying to pass it an integer value, but you might not be - and there's no way to pass 'UNLIMITED' as a number argument. In this case it could, perhaps, have been written allow the numeric values anyway - but then you would have no way to reset it, which would be problematic.

Unfortunately there isn't an equivalent set_str_param_in_session procedure, and as it's an undocumented and unsupported package it's unlikely you'd get very far asking Oracle to add one.

I don't think there's any way to do what you're attempting from another session.

I'm not aware of any way to apply this via a profile or resource manager either. With some advance planning you could potentially have a way to tell Apex (e.g. via a parameter somewhere) to set this itself, but that sounds like a lot of work. It might be simpler to use a logon trigger to always set this (for some users, or in a role, perhaps) via an alter session call, so you don't have to think about it when starting a trace.