Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

maximum setting of sort_area_size parameter

731838Sep 22 2010 — edited Nov 1 2010
I am trying to create a index on a huge table of size 120G. The sql statement uses around 10G of temp space and it runs for more than 12 hours.
We have 32G of memory available in the box and I am trying to set manual PGA with sort_area_size of around 8G but I am not able to do that as I'm receiving errors if I set sort_area_size to more than 2G
pga_aggregate_target is set to 10G.

SQL> alter session set workarea_size_policy=MANUAL;

Session altered.

SQL> alter session set sort_area_size=3000000000;
alter session set sort_area_size=3000000000
*
ERROR at line 1:
ORA-02017: integer value required

SQL> alter session set sort_area_size=3G;
alter session set sort_area_size=3G
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION


SQL> alter session set sort_area_size=8000000000;
alter session set sort_area_size=8000000000
*
ERROR at line 1:
ORA-02017: integer value required

SQL> alter session set sort_area_size=2000000000;

Session altered.

SQL> alter session set hash_area_size=2000000000;

Session altered.

Any suggestions on how to set sort_area_size to more than 2G?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 29 2010
Added on Sep 22 2010
18 comments
19,435 views