Hi,
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$;
Answer: 3
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 );
Answer: 23598
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?
Thank you.
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.