The Project Security Job Fails Due To the PROJECTSECURITY Table Size. (Doc ID 2297606.1)

Last updated on SEPTEMBER 15, 2017

Applies to:

Primavera P6 Enterprise Project Portfolio Management - Version 15.2.0.0 and later
Primavera P6 Enterprise Project Portfolio Management Cloud Service - Version 15.2.0.0 and later
Information in this document applies to any platform.

Symptoms

The PROJECTSECURITY table, present in admuser schema, is very large in size (more than 121GB).
As a result, the Project Security job is failing with error:

ERROR:
Errors occurred when executing following statement: declare DIRTY_USERS_CONTEXT_NAME varchar2(12) := 'DIRTY_USERS'; vspid number; vtotal pls_integer := 0; vupd pls_integer := 0; vins pls_integer := 0; i_start pls_integer; i_end pls_integer; vsec number; vrate number; start_time timestamp; end_time timestamp; vmsg varchar2(1000); df varchar2(22) := 'yyyy-mm-dd hh24:mi:ss'; vmsg_cnt pls_integer := 0; begin vspid := sys_context('USERENV','SID') ; delete from wrk_log_results where spid = vspid; commit; i_start := dbms_utility.get_time; execute immediate 'select current_timestamp from dual' into start_time; COMMIT; for urec in (select pk1 from pkxref where system_id = vspid and context_name = DIRTY_USERS_CONTEXT_NAME) loop BEGIN DELETE /*+ INDEX(PROJECTSECURITY NDX_PK_PROJECTSECURITY) / FROM projectsecurity WHERE RDBMSUSERID = urec.pk1; INSERT /+ APPEND / INTO ProjectSecurity (rdbmsuserid, projectobjectid, costflag) select uo.user_id, op.proj_id, max(allow_flag) from userobs uo inner join obsproj op on op.obs_id = uo.obs_id inner join profpriv pp on pp.prof_id = uo.prof_id and (priv_num = 23 or priv_num = 12) and uo.user_id = urec.pk1 group by uo.user_id, op.proj_id; vins := vins + SQL%ROWCOUNT; update /+ INDEX(USERSX PK_USERSX) */ usersx set lastprojectsecuritydate = sysdate where user_id = urec.pk1; vupd := vupd + SQL%ROWCOUNT; commit; EXCEPTION WHEN others THEN vmsg := 'User id ' || urec.pk1 || ' ERROR: ' || SQLERRM; vmsg_cnt := vmsg_cnt + 1; insert into wrk_log_results ( spid, seq_num, log_message) values (vspid,vmsg_cnt,vmsg); commit; END; end loop; COMMIT; vtotal := vins + vupd; i_end := dbms_utility.get_time; execute immediate 'select current_timestamp from dual' into end_time; vsec := round(( (i_end-i_start) * 0.01),3); if vsec > 0 then vrate := round( vtotal / vsec , 3); else vrate := 0; end if; vmsg := 'Successtrue,' ||' Start, End,' || ' Elapsed,' || ' Count,' || ' Throughput,' || ' Pages[1],' || ' Inserted,' || ' Updated'; vmsg_cnt := vmsg_cnt + 1; insert into wrk_log_results ( spid, seq_num, log_message) values (vspid,vmsg_cnt,vmsg); commit; EXCEPTION when others then ROLLBACK; vmsg := 'Successfalse'; vmsg_cnt := vmsg_cnt + 1; insert into wrk_log_results ( spid, seq_num, log_message) values (vspid,vmsg_cnt,vmsg); vmsg := SQLERRM; vmsg_cnt := vmsg_cnt + 1; insert into wrk_log_results ( spid, seq_num, log_message) values (vspid,vmsg_cnt,vmsg); commit; raise; end;
<TIMESTAMP>:TASK:User: [3] CORE(SQL): IO Error: Socket read timed out
<TIMESTAMP>:TASK:User: [3] CORE(SQL): Connection has been recycled. Database pvm Pool PML
<TIMESTAMP>:TASK:User: [3] CORE(SQL): Task completed 2017-02-02 13:00:19

STEPS

The issue can be reproduced at will with the following steps:
1. Observe the Project Security job is failing with the reported error.

Changes

 

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