Execution of vpd_util.refresh_vpd_policies procedure Fails with Error Code ORA-04021 and ORA-06512 (Doc ID 2207053.1)

Last updated on DECEMBER 27, 2016

Applies to:

Oracle Healthcare Translational Research - Version 3.1.0.2 and later
Information in this document applies to any platform.

Symptoms

When users are running the MASTER_EXECUTION_PLAN which calls the LEVEL7_LOAD_PROTOCOL_UTIL is experiencing locking behavior.

Procedure analyze_data is:
begin
dbms_stats.gather_table_stats(v_schema_name, 'VPD_PATIENT_DATA',
method_opt=> 'for all columns size auto for columns acs_wid size 1',
block_sample=>true, estimate_percent=>5, cascade=>true);
dbms_stats.gather_table_stats(v_schema_name, 'VPD_SUBJECT_DATA',
method_opt=> 'for all columns size auto for columns acs_wid size 1',
block_sample=>true, estimate_percent=>5, cascade=>true);
end analyze_data;

This creates hundreds of the same VPD_ANALYSE_DATA procedures to be started at the same time. Most of the sessions are stuck in this waiting state with the following error.

ERROR
-----------------------

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms