maximum setting of sort_area_size parameter
731838Sep 22 2010 — edited Nov 1 2010I 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?