Return
-
Title
ORA-30004: when using SYS_CONNECT_BY_PATH function -
Description
The DBO Initialization Parameters collection fails with the following error
DBO_Initialization_Parameters Failed with the exception: Failed to execute query on instance [xxx-xxx-xxxx-xxxxx.xxx.xxxxx.xxx.xx], user account [foglight]. Reason : [ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have separator as part of column value
-
Cause
A comma value followed by multiple space characters was present in some of the initialization parameters.
-
Resolution
This issue was previous listed as fixed in the 5.7.5.1 Oracle cartridge as ODB-12317, however further investigation determined this was a client side issue and manual updates were necessary to the initialization parameters.
- Run a query to find all of the initialization parameters with a comma
SELECT name, VALUE
FROM v$spparameter
WHERE VALUE LIKE '%, %'; - Find any of the values that has more than one space after the comma character.
- For each of these values, use an ALTER SYSTEM statement to update the value to keep only one space after the command, for example where the log_archive_dest_1 is 'location="/oracle/INSTANCENAME/oraarch/", valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
- Run a SELECT statement to find any values with a comma
alter system set log_archive_dest_1='location="/oracle/INSTANCENAME/oraarch/", valid_for=(ONLINE_LOGFILE,ALL_ROLES)';
- Run a query to find all of the initialization parameters with a comma
-
Additional Information
In V$SPPARAMETER dictionary view, parameters which contain several values has several rows - for example, if you have multiplexed the control_files (i.e. you have more than 1 control file) then you'll find that V$SPPARAMETER returns more than one row for the "control_files" initialization parameter (1 row per each value so if you have 3 control files V$SPPARAMETER will contain 3 rows - 1 per each control file). In order to display in the "Initialization Parameters" dashboard 1 row per each parameter we had to concatenate (with a comma seperator) the different values of each parameter that has more than 1 value/row (e.g. control_files). This is why we use the SYS_CONNECT_BY_PATH function but in your environment if fails due to the fact that you have 2 (or more) blanks and it's part of the seperator which casues ORA-30004. So as a short-term solution, just removing these unnecessary blanks will solve the problem and won't do any damage to your database.