My Oracle Support Banner

P6 Publish Security Job Fails With Error: ORA-01555: Snapshot Too Old: Rollback Segment Number With Name "" Too Small ORA-06512 (Doc ID 2049935.1)

Last updated on MARCH 21, 2018

Applies to:

Primavera P6 Enterprise Project Portfolio Management - Version 8.2 1.0 and later
Information in this document applies to any platform.

Goal

"ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-06512: at line" appears in the Publication Services logs

Sample log below:

SEVERE: 2015-08-13 02:50:35:[TASK]:[User]: [3] CORE(SQL): 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;

     update /*+ INDEX(USERSX PK_USERSX) */ usersx set lastprojectsecuritydate = sysdate where user_id = urec.pk1;

     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;

vins := vins + SQL%ROWCOUNT;

COMMIT;

vtotal := vins;

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 := 'Success[true],'
         ||' Start[' || to_char(start_time,df) || '], End[' || to_char(end_time,df) || '],'
         || ' Elapsed[' || trim(to_char(vsec,'99999.99')) ||  's],'
         || ' Count[' ||  trim(to_char(vtotal,'999999999')) || '],'
         || ' Throughput[' || trim(to_char(vrate,'99999.99')) || '/s],'
         || ' Pages[1],'
         || ' Inserted[' ||  trim(to_char(vins,'999999999')) || '],'
         || ' Updated[' ||  trim(to_char(vupd,'999999999')) || ']';
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 := 'Success[false]';
  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;

SEVERE: 2015-08-13 02:50:35:[TASK]:[User]: [3] CORE(SQL): ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-06512: at line 94

java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-06512: at line 94

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
at com.primavera.infr.db.PPreparedStatement.execute(Unknown Source)
at com.primavera.px.common.PxServiceSqlTaskImpl.a(Unknown Source)
at com.primavera.px.common.PxServiceSqlTaskImpl.a(Unknown Source)
at com.primavera.px.common.PxServiceSqlTaskImpl.executeTask(Unknown Source)
at com.primavera.px.common.PxAbstractServiceTask.execute(Unknown Source)
at com.primavera.px.common.PxAbstractServiceTask.execute(Unknown Source)
at com.primavera.infr.threadpool.ThreadPoolImpl.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Solution

To view full details, 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 a vibrant support community of peers and Oracle experts.