MyDanes
asked on
SORT_AREA_SIZE ERROR when trying to REORGANIZE an index
we just migrated the db to 10 g and now I need to reorganize some of the
index's that are invalid - this should NOT be a problem but NOW it is giving me
an error - sort area size - invalid value for sort area size - the minimum
value is 48kb - PLEASE help - NO ONE CAN WORK
I have done this a million times NEVER have I had a SORT area size error.
1. I ran the show parameter sort_area_size; in sqlplus
2. Returned: 2000000
3. I then attempted to run:
* alter system set sort_area_size=120K
* alter system set sort_area_size=120000
* BOTH attempts failed stating: ORA-02096 specified initialization parameter is not modifiable with this option
4. I went through OEM and went to all initializaiton parameters and saw it also
stated 2000000 I attempted to change to 120K or 120000 applied and exited
5. I reran the show parameter and it still returned 2000000
6. shutdown and restarted the database to get it to display - when I came back in only to find
it was reverted to the 2000000
NEXT attempt:
1. I FINALLY got it to take 120000 for the sort_area_size by using OEM and changing it under 'current' and under
'spfile' apply
2. shutdown immediate
3. startup
4. sqlplus - show parameter - result was 120000
5. again attempted to reorganize my pragmatic index (one of them) and SAME error about the sort area SIZE
6. I am really panicking now.
NEXT attempt:
1. It took 4000000 I applied
2. Shutdown database
3. restart database
4. sqlplus - show parameter reflects 4000000
5. go to OEM - aministration
6. indexes
7. select index
8. select reorganize
9. next
10. Next - ERROR - same error about SORT_AREA_SIZE - this is VERY strange I DO NOT understand
PLEASE HELP
index's that are invalid - this should NOT be a problem but NOW it is giving me
an error - sort area size - invalid value for sort area size - the minimum
value is 48kb - PLEASE help - NO ONE CAN WORK
I have done this a million times NEVER have I had a SORT area size error.
1. I ran the show parameter sort_area_size; in sqlplus
2. Returned: 2000000
3. I then attempted to run:
* alter system set sort_area_size=120K
* alter system set sort_area_size=120000
* BOTH attempts failed stating: ORA-02096 specified initialization parameter is not modifiable with this option
4. I went through OEM and went to all initializaiton parameters and saw it also
stated 2000000 I attempted to change to 120K or 120000 applied and exited
5. I reran the show parameter and it still returned 2000000
6. shutdown and restarted the database to get it to display - when I came back in only to find
it was reverted to the 2000000
NEXT attempt:
1. I FINALLY got it to take 120000 for the sort_area_size by using OEM and changing it under 'current' and under
'spfile' apply
2. shutdown immediate
3. startup
4. sqlplus - show parameter - result was 120000
5. again attempted to reorganize my pragmatic index (one of them) and SAME error about the sort area SIZE
6. I am really panicking now.
NEXT attempt:
1. It took 4000000 I applied
2. Shutdown database
3. restart database
4. sqlplus - show parameter reflects 4000000
5. go to OEM - aministration
6. indexes
7. select index
8. select reorganize
9. next
10. Next - ERROR - same error about SORT_AREA_SIZE - this is VERY strange I DO NOT understand
PLEASE HELP
Oracle9i Database Reference
Release 2 (9.2)
Part Number A96536-01
Oracle does not recommend using the SORT_AREA_SIZE parameter
unless the instance is configured with the shared server option.
Oracle recommends that you enable automatic sizing of SQL working
areas by setting PGA_AGGREGATE_TARGET instead.
SORT_AREA_SIZE is retained for backward compatibility.
Release 2 (9.2)
Part Number A96536-01
Oracle does not recommend using the SORT_AREA_SIZE parameter
unless the instance is configured with the shared server option.
Oracle recommends that you enable automatic sizing of SQL working
areas by setting PGA_AGGREGATE_TARGET instead.
SORT_AREA_SIZE is retained for backward compatibility.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
alter system set sort_area_size=0 scope=spfile;
alter system set pga_aggregate_target=200m scope=spfile;
and then bounce(restart) the DB
alter system set pga_aggregate_target=200m scope=spfile;
and then bounce(restart) the DB
Go to ...\admin\pfile and find the init.8878781728
file.
Put there the
sort_area_size
parameter value.
Try to bounce the instance so:
SQL>shutdown immediate
....
SQL>startup pfile=.....\init<SID>.ora
Play withe the parameter until you achive stability.
After that create a new SPFILE from the PFILE:
SQL>create spfile='c:\....\SPFILE<SID
Shutdoen the instance, backup the old SPFILE, replace it with the new.
Bounce the DB.
Next advice - what is the version of Oracle?
10g R2 is the only choice for you.