Ora-12801 : is it physical space issue

Hi,
    I am using Proc sql in SAS to connect oracle databse in server. While fetching data i am getting the error
Error fetching from cursor. ORACLE error is ORA-12801: error signaled in parallel query
 server P003   ORA-01652: unable to extend temp segment by 9929 in tablespace USR_TMP.


Is it physical space issue, what are the possible solutions..
LVL 3
harsha_dpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

riazpkCommented:
Yes, you have less tablespace sotrage than you require. Add datafile to tablespace usr_tmp to increase its size.
harsha_dpAuthor Commented:
Is there a upper limit for the available memory at any time.. This is to decide when to use datafile. As we have some estimate about temporary space that the query uses during execution, if we know the limit we can dynamically program the things, I believe. I am using SAS , so i can fix up the upper bound before query execution.
riazpkCommented:
No this is not Memory-related issue. Just set autoextend property of database file (in tablespace usr_tmp to ON and the datafile will grow as much as it needed.

Also you can change maxsize (the maxmimum limit a datafile can grow automatically if the autoextend on) to unlimited.

Like:

alter database datafile 'DataFileNameWithFullPath'
autoextend on 10M
Maxsize 1000M;

Here is said to autoextend by 10M but upto the max of 1000M.

Alternatively:

alter database datafile 'DataFileNameWithFullPath'
autoextend on 10M
Maxsize unlimited;

will extend file by 10M (whenever required) and the max size of file is unlimited.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

harsha_dpAuthor Commented:
I am just querying the server. I cannot do anything for database which is in remote place. I need the solution which just does this much. My query has group by clause, there will be around 11million records in the table. Result of the query is also stored in remote place, finally i download the resultset or recordset which i am interested in.
riazpkCommented:
No...you can't ...Think about it ...you are grouping on 11 Millions records' table...which just needs a lots of Temporary Tablespace. You may ask DBA at remote to do so or to assign you a larger temp tablespace OR ask them to do themselves at their remote location and you downoad the results.
harsha_dpAuthor Commented:
Yes.. Exactly right.. But i am not the only person who use the database. Thats why i am in search of solution from experts. Though i get the pace DBA has to lookafter about concurrency   Right..

Thanks
riazpkCommented:
Another possible solution is you divide (logically) your data between pieces and

(1) process data of piece
(2) store data
(3) goto 1

and repeat until u r finished.

Just like

select count(*),sum(col2),col1
from
(
select * from
(
select rownum rno,a.*
from tab a
where rownum<=YourUpperLimit)
and rno>YourLowerLimit
)
group by col1


Say you can easily use a range of 1 million records. So there will be 11 pieces
harsha_dpAuthor Commented:
Yes.. .. Yesterday after thinking for a long time i wrote a code to fetch data in different stages and at the end summarizing it. It is working fine and good. But i want to minimize the processing time.The solution what i thought and implemented has become a problem again..
riazpkCommented:
The Solution is right **There** But problem is that you are unable to implement it so you will have to live with existing one.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.