Setting GUC Parameters for GaussDB When the Source Database Type Is Oracle

When migrating data from Oracle to GaussDB, you can set the GUC parameters of GaussDB to enhance the compatibility. For details about the GUC parameters involved in the migration and the impact scope, see Table 1.

GaussDB provides many operation parameters. Configurations of these parameters affect the behavior of the database system. Before changing these parameters, learn the impact of these parameters on the database. Otherwise, unexpected results may occur. For details, see GUC Parameter Usage.

**Table 1** GUC parameters for migrating data from Oracle to GaussDB
Configuration item (Compatible with A Mode)	Compatibility Configuration Item	Compatibility Configuration Item Name	Supported Database	Enable	Description
behavior_compat_options	display_leading_zero	Specifies how floating point numbers are displayed. 0 before the decimal point is displayed	Primary/Standby and distributed	Yes	Primary/Standby: GUC Parameters. Distributed: Parameter Details
	end_month_calculate	Specifies the calculation logic of the add_months function.	Primary/Standby and distributed	Yes
	bind_procedure_searchpath	Specifies the search path of the database object in a stored procedure for which no schema name is specified.	Primary/Standby and distributed	Yes
	correct_to_number	Specifies the compatibility of the to_number() result.	Primary/Standby and distributed	Yes
	unbind_divide_bound	Specifies the range check on the result of integer division.	Primary/Standby and distributed	Yes
	convert_string_digit_to_numeric	Determines whether to convert numeric constants of the character string type to those of the numeric type before these two types are compared.	Primary/Standby and distributed	Yes
	return_null_string	Specifies how to display the empty result (empty string '') of the lpad() and rpad() functions.	Primary/Standby and distributed	Yes
	merge_update_multi	When MERGE INTO ... WHEN MATCHED THEN UPDATE and INSERT ... ON DUPLICATE KEY UPDATE are used, controls the UPDATE behavior if a piece of target data in the target table conflicts with multiple pieces of source data.	Primary/Standby and distributed	Yes
	plstmt_implicit_savepoint	Determines whether the execution of an UPDATE statement in a stored procedure has an independent subtransaction.	Primary/Standby and distributed	Yes
	hide_tailing_zero	Controls configuration item for numeric display.	Primary/Standby and distributed	Yes
	rownum_type_compat	Specifies the ROWNUM type.	Primary/Standby	Yes
	aformat_null_test	Specifies the logic for checking whether the row type is not null.	Primary/Standby and distributed	Yes
	aformat_regexp_match	Determines the matching behavior of regular expression functions.	Primary/Standby and distributed	Yes
	compat_cursor	Determines the compatibility behavior of implicit cursor states.	Primary/Standby	Yes
	proc_outparam_override	Determines the overloading of output parameters of a stored procedure.	Primary/Standby and distributed	Yes
	proc_implicit_for_loop_variable	Controls the behavior of the FOR_LOOP query statement in a stored procedure.	Primary/Standby	Yes
	allow_procedure_compile_check	Controls the compilation check of the SELECT and OPEN CURSOR statements in a stored procedure.	Primary/Standby	Yes
	plsql_security_definer	Determines whether the definer permission is used by default when a stored procedure is created.	Primary/Standby and distributed	Yes
	plpgsql_dependency	Determines whether a function, stored procedure, or package containing undefined objects can be created.	Primary/Standby	Yes
plsql_compile_check_options	plsql_expression_check	If the plsql_expression_check parameter is enabled, the plpgsql_dependency parameter must also be enabled.	503.1 primary/standby	Yes
a_format_version	10C	Specifies database platform compatibility configuration items.	Primary/Standby and distributed	Yes
a_format_dev_version	S1	Specifies the compatible minor version of the database platform.	503.0 primary/Standby and distributed	Yes
	S2	Specifies the compatible minor version of the database platform.	503.1 primary/Standby and distributed	Yes
	S3	Specifies the compatible minor version of the database platform.	503.2 primary/standby and distributed	Yes
	S4	Specifies the compatible minor version of the database platform.	505.0 primary/standby and distributed	Yes
sql_beta_feature	a_style_coerce	Affects the decode function.	Primary/Standby and distributed	Yes

a_format_dev_version is set to s1 for GaussDB 3.1 enterprise edition, s2 for GaussDB 3.2 enterprise edition, and s3 for GaussDB 3.3 enterprise edition. For details about the version numbers, see What Is the Mapping Between the GaussDB Versions Displayed on the UGO Console and Self-built GaussDB Versions?

Parameter Settings

For details about how to set cloud-based GaussDB GUC parameters, see Setting Parameters in the primary/standby deployment and Setting Parameters in the distributed deployment.
For details about how to set on-premises GaussDB GUC parameters, see Setting Parameters in the primary/standby deployment and Setting Parameters in the distributed deployment.

Procedure

For active/standby GaussDB 8.0:

Connect to the GaussDB database and switch to the GaussDB installation user.
su - omm

In the preceding command, omm is an example. Change it to the actual installation user.
Run the following command to configure the GUC parameters:
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "behavior_compat_options='bind_procedure_searchpath,truncate_numeric_tail_zero,plsql_security_definer,proc_outparam_override,aformat_null_test,rownum_type_compat,allow_procedure_compile_check,proc_implicit_for_loop_variable,plstmt_implicit_savepoint,end_month_calculate,disable_rewrite_nesttable,plpgsql_dependency,display_leading_zero,correct_to_number,unbind_divide_bound,convert_string_digit_to_numeric,hide_tailing_zero,return_null_string,aformat_regexp_match,compat_cursor,enable_funcname_with_argsname,tableof_elem_constraints,merge_update_multi'";

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "plsql_compile_check_options='plsql_expression_check'";

It is applied for GaussDB 3.1 enterprise edition and later versions.

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_version='10c'";

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_dev_version='s4'";

a_format_dev_version is set to s1 for GaussDB 3.1 enterprise edition, s2 for GaussDB 3.2 enterprise edition, s3 for GaussDB 3.1 enterprise edition, and s4 for GaussDB 8.0 enterprise edition. For details about the versions, see What Is the Mapping Between the GaussDB Versions Displayed on the UGO Console and Self-built GaussDB Versions?.

For Distributed GaussDB 8.0:

Connect to the GaussDB database and switch to the GaussDB installation user.
su - omm

In the preceding command, omm is an example. Change it to the actual installation user.
Run the following command to configure the GUC parameters:
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "behavior_compat_options='bind_procedure_searchpath,truncate_numeric_tail_zero,plsql_security_definer,proc_outparam_override,aformat_null_test,plstmt_implicit_savepoint,end_month_calculate,disable_rewrite_nesttable,display_leading_zero,correct_to_number,unbind_divide_bound,convert_string_digit_to_numeric,hide_tailing_zero,return_null_string,aformat_regexp_match,enable_funcname_with_argsname,tableof_elem_constraints,merge_update_multi'";

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_version='10c'";

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_dev_version='s4'";

s1 is used for 503.0, s2 is used for 503.1, s3 is used for 503.2, and s4 is used for 505.0. For details about the versions, see What Is the Mapping Between the GaussDB Versions Displayed on the UGO Console and Self-built GaussDB Versions?