Today I upgraded one of our Oracle APEX environment from 19.2 to 20.1. The upgrade was successful except that I found the following error in the installation log:
# Error: ORA-02298: cannot validate (APEX_200100.WWV_FLOW_DATA_SESSION_FK) - parent keys not found
# Statement: alter table "APEX_200100"."WWV_FLOW_DATA" enable all triggers enable constraint "VALID_ITEM_FILTER" enable constraint "VALID_SESSION_STATE_STATUS"
enable constraint "WWV_FLOW_DATA_SESSION_FK"
I ran the following queries to check the tables:
select count(*) from APEX_200100.WWV_FLOW_SESSIONS$;
select count(distinct flow_instance) from APEX_200100.WWV_FLOW_DATA d
where not exists ( select 1 from APEX_200100.WWV_FLOW_SESSIONS$ s where s.id = d.flow_instance );
Is it safe to run the following SQL to delete the no-parent child entries in the "apex_200100.wwv_flow_data" table and then re-enable the foreign key constraint?
delete from apex_200100.wwv_flow_data d
where not exists (
select 1 from apex_200100.wwv_flow_sessions$ s
where d.flow_instance = s.id );
Also, since I have other Oracle databases that are still running Oracle APEX 19.2, should I run the above SQL before upgrading APEX 19.2 to 20.1 so that the error will not appear when running the upgrade? Will the manual delete cause problem during upgrade?
I spoke to the APEX team. They recommended the following course of action
a) Check to see if the constraint WWV_FLOW_DATA_SESSION_FK was enabled in APEX 19.2. There were historical issues of this constraint getting disabled by mistake.
b) Then fix the error (with the delete you mentioned) in APEX 19.2 *before* the upgrade.
For the instance you have already upgraded, then do the delete, re-enable the constraint and run sys.validate_apex. If that fails, time to talk to Support.
Thanks to Christian Neumueller for this info.