INVALID SYS.WWV_FLOW_VAL Package in Pre Upgrade Diagnostics for 11g

End result was a corrupt APEX/HTMLDB installation which has had to be removed. Followed Oracle recommendations in Metalink Note 558340.1How to Uninstall Oracle HTML DB / Application Express from the Database.

It seems the problem had manifested due to an incorrectly imported APEX installation which led to invalid objects.

The only way around this, without creating doubt in the upgrade to 11.1.0.7 was to give the APEX install a clean start by first uninstalling, and then letting the upgrade install the new version.

The following instructions are only valid if the Application Express version is prior to 3.0.

If the Version is above 3.0 then run @apxremov.sql which will remove all of the FLOWS_ and APEX_ related schemas and schema objects

The first step is to drop the schemas associated with your Application Express installation.

Log in to SQL*Plus as the SYS user.

Drop the schemas FLOWS_FILES, APEX_PUBLIC_USER, HTMLDB_PUBLIC_USER

drop user FLOWS_FILES cascade; 
drop user APEX_PUBLIC_USER; 
drop user HTMLDB_PUBLIC_USER;

Next drop any of the APEX install schemas present in the installation:

drop user FLOWS_010500 cascade; 
drop user FLOWS_010600 cascade; 
drop user FLOWS_020000 cascade; 
drop user FLOWS_020100 cascade; 
drop user FLOWS_020200 cascade; 
drop user FLOWS_030000 cascade; 
drop user FLOWS_030100 cascade; 
drop user APEX_030200 cascade;
drop user APEX_040000 cascade;

Next, you will need to drop the Application Express public synonyms. To do this, issue the following statement and run the resulting output:

select 'drop public synonym ' || synonym_name || CHR(10) || '/' from sys.dba_synonyms 
where table_owner in 
('FLOWS_010500',
'FLOWS_010600',
'FLOWS_020000',
'FLOWS_020100',
'FLOWS_020200',
'FLOWS_030000',
'FLOWS_030100',
'APEX_030200',
'APEX_040000',
'FLOWS_FILES');

Remove any private synonyms that remain. Finally, if desired, remove the images directory that was associated with the Application Express install on the HTTP Server used by the APEX DAD. Verify that no remaining private synonyms exist for the FLOWS_FILES schema:

select synonym_name from sys.dba_synonyms where owner = 'FLOWS_FILES';

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: