The other day I tried to run a small PL/SQL block to check all records from a temporary table. The PL/SQL has a simple cursor defined as:

cursor sel_cur is 
	select pk as t1_pk from temp_t1_0928;

I’ve done this a few weeks before. It ran fine and finished successfully. But this time it failed with the error after running for a while:

declare
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server
ORA-06512: at line 8
ORA-06512: at line 8

ORA-12801 is a general error on parallel execution and the accompanying message ORA-06512 again is another general one. And in my PL/SQL block, line 8 is just the statement BEGIN.

The ineresting part was that parallel execution was triggered. That’s because I used CTAS with PARALLEL 4 to create this temporary table. With Oracle Enterprise Edition, CTAS with the NOLOGGING keyword and PARALLEL clause is a frequently used technique to fast duplicate another table partially or in full.

select owner, table_name, TABLESPACE_NAME, DEGREE from dba_tables where table_name='TEMP_T1_0928';

TABLE_NAME        DEGREE
----------------- -------------------
TEMP_T1_0928    	4

I could not find the cause behind this failure. So I turned off parallel execution by setting the table level of DOP (degree of parallelism) to 1 with the following statement:

ALTER TABLE TEMP_T1_0928 PARALLEL 1;

• Oracle