A job is running and throwing an ORA-1652 error on a regular basis.This is a VB6 program using ADO 2.1 and doing ALOT of I/O (mostly reading data) using recordsets - cursor type adOpenForwardOnly . The recordset is used over and over to retrieve data in a loop.
There are some large memo type fields that get retrieved within this loop and the database person had suggested that sorting the data may be causing the problem. I removed the 'order by' clause from the SQL and we still get the error.
Are you aware of any explicit problems using ADO/recordsets, with a backend Oracle database?
The ORA-1652 error tells you that your process can't extend a temporary segment in a tablespace. Did you verify that this segment is in the TEMP tablespace? If so, then sorting operations, such as GROUP BY, may require sorting space and it is possible that you don't have enough sorting space available on disk. The correct solution is to allocate more space to the TEMP tablespace. This problem is not raised because you are using ADO/recordsets.
Other operations require sorting. You didn't post your SQL statement, so it's hard to tell if you are using those operations. But they include GROUP BY and ORDER BY.
To fix your problem, increase your TEMP tablespace by adding space to it. This is accomplished with the following command:
ALTER DATABASE DATAFILE '/directory/filename' RESIZE xxM;You'll need the file name for the datafile. You can get this by querying DBA_DATA_FILES.
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.