Oracle 9i: Setting sort_area_size at session level has no effect
Dear All,
Yesterday, I tried to add a new index to a table. The index size was about 600Mb. In order to speed up the process, I increased the sort_area_size to 400Mb. Here are the commands:
alter session set sort_area_size=409600000;
alter session set sort_area_retained_size=409600000;
create index ind01 ....;
In another session, I monitored the sort usage in v$sort_usage table, I noticed that the create index operation was still using over 600Mb in the temp tablespace. Does that mean sort_area_size has no effect on create index, rebuild index, or this kind of operations?
Regards,
-- Chris