Q
Problem solve Get help with specific problems with your technologies, process and projects.

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;

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close