ORA-12801: error signaled in parallel query server P000, instance exdbadm01:exdbx1 (1)

ORA-12801: error signaled in parallel query server P000, instance exdbadm01:exdbx1 (1)

Cause:

The error is caused due to different parallel settings for tables

  1* select * from sales_t where order_id in (select order_id from SALES_TAB)
kish@exdbx<>select * from sales_t
  2  where ORDER_ID in (select order_id from SALES_TAB)
  3  or
  4  order_id=369;
select * from sales_t
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000, instance exdbadm01:exdbx1 (1)

Workaround:

The query has two tables where one table( SALES_TAB ) has parallel 4 and other table ( SALES_T ) has parallel 1

kish@exdbx<>select index_name,degree from dba_indexes where table_name in ('SALES_T');

INDEX_NAME                     DEGREE
------------------------------ ----------------------------------------
PK_SALES_OID                   1

Elapsed: 00:00:00.02

kish@exdbx<>select table_name,degree from dba_tables where table_name in ('SALES_T','SALES_TAB');

TABLE_NAME                     DEGREE
------------------------------ ----------------------------------------
SALES_TAB                               4
SALES_T                                 1

Equalize the parallel degree for both the tables

kish@exdbx<>alter table SALES_TAB parallel(degree 1);

Table altered.

Query gets executed

Elapsed: 00:00:00.27
kish@exdbx<>select * from sales_t
  2  where ORDER_ID in (select order_id from SALES_TAB)
  3  or
  4  order_id=3;