ORA-12801 and ORA-01652 errors on running query
I am running a query, and I am getting a error message saying:
ORA-12801: error signaled in parallel query server P009 ORA-01652: unable to extend temp segment by 64 in tablespace TEMP
Why am I getting this error, and how should I resolve it?
The ORA-1652 error is your problem. Your query is performing a sort operation that cannot complete in memory alone. Therefore, it needs to use disk space to complete that sort operation. That disk space is taken from the TEMP tablespace. The ORA-1652 error is telling you that there is not enough room in this tablespace.
You have two options. First, you can increase your sort area in memory in an attempt to complete the sort operation fully in memory and not use any disk space. You can do that with the following command:
ALTER SESSION SET sort_area_size=1000000;
This will set your session's sort area in memory to 1 million bytes.
Second, you can increase the size of your temporary tablespace. That can be done by adding another datafile with a command similar to the following:
ALTER TABLESPACE temp ADD DATAFILE '/dir/filename' SIZE 2G;