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;