set serveroutput on DECLARE v_version sys.v_$instance.version%type; v_param_value sys.v_$system_parameter.value%type; v_pdb boolean; v_con_id number; v_con_type varchar(7); BEGIN -- find current setting of optimizer_dynamic_sampling select value into v_param_value from v$parameter where name = 'optimizer_dynamic_sampling'; -- find current(major) version select version into v_version from sys.v_$instance; -- find container type select case when (sys_context('USERENV','CON_ID') = 0 ) then 'NON-CDB' when (sys_context('USERENV','CON_ID') = 1 ) then 'CDB' when (sys_context('USERENV','CON_ID') > 1 ) then 'PDB' end into v_con_type from DUAL; --dbms_output.put_line('v_con_type: ' || v_con_type); --dbms_output.put_line('version: ' || v_version); --dbms_output.put_line('optimizer_dynamic_sampling: ' || v_param_value); v_pdb := FALSE; IF v_con_type = 'PDB' THEN v_pdb := TRUE; ELSE v_pdb := FALSE; END IF; CASE WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value = '0' THEN dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling = 0'); IF v_pdb = FALSE THEN dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 scope=both;'); execute immediate 'alter system set optimizer_dynamic_sampling=4 scope=both'; ELSIF v_pdb = TRUE THEN dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 container=current scope=both;'); execute immediate 'alter system set optimizer_dynamic_sampling=4 container=current scope=both'; END IF; WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value <> '0' THEN dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling is already set.'); ELSE dbms_output.put_line('version is < 18'); END CASE; END; /
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Tuesday, August 2, 2022
How to change an Oracle instance parameter based on database version and container type
Here is a simple pl/sql script that will set parameters based on version and container type.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment