ORA-12008 ORA-12801 ORA-00060 deadlock for running online redef in parallel (Doc ID 2670089.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

alter table applsys.fnd_lobs_new nologging;
alter session force parallel dml parallel 6;

declare
  l_col_mapping varchar2(1000);
begin
  l_col_mapping :=
  'file_id file_id, '
  ||'file_name file_name, '
  ||'file_content_type file_content_type, '
  ||'file_data file_data, '
  ||'upload_date upload_date, '
  ||'expiration_date expiration_date, '
  ||'program_name program_name, '
  ||'program_tag program_tag, '
  ||'language language, '
  ||'oracle_charset oracle_charset, '
  ||'file_format file_format'
  ;
  dbms_redefinition.start_redef_table ('APPLSYS', 'FND_LOBS', 'FND_LOBS_NEW', l_col_mapping)
  ;
end;
/


Getting error

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P002
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_REDEFINITION", line 75
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3459
ORA-06512: at line 17
 

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.