Solved

Oracle 11g

Posted on 2013-11-27
2
642 Views
Last Modified: 2013-11-27
Hi,

When I am running the below command,

exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'SCOTT', tabname=>'REDEF_TEST_INT', estimate_percent=>100, cascade=>true, degree=>8);

I am getting the below error
ORA-12801: error signaled in parallel query server P001
ORA-04030: out of process memory when trying to allocate 64528 bytes (sort subheap,sort key)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

I reduced the estimate_percent to 10 and degree to 2 and ran again. But still I got the same error.
The table is of size 21GB. about(100,000,000 records)

Can you please let me know how can I take the table stats for this table without getting this error?

Thanks.
0
Comment
Question by:GouthamAnand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Accepted Solution

by:
sventhan earned 500 total points
ID: 39680874
Try to run with no-parallel or, increase the memory.

What is your SGA size?

exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'SCOTT', tabname=>'REDEF_TEST_INT', estimate_percent=>100, cascade=>true, degree=>1);
0
 

Author Closing Comment

by:GouthamAnand
ID: 39682748
Thank you. I could run successfully with no parallel.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question