Re: index creation fails because temp tablespace is too small

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1115396206.396494_at_yasure...
> steven wrote:
>
>> If utlxpls.sql can estimate temp tablespace usage when building
>> index,that would be very nice.
>>
>> Steven
>
> I can not. UTLXPLS.SQL is nothing more than a call to DBMS_XPLAN
> to view an explain plan.

But an execution plan includes the column temp_space, which is supposed to be exactly what the user wanted. And a call to dbms_xplan will populate and report it if CPU costing is enabled.

The materialized view advisor uses explain plan in just this way to estimate the size of materialized views.

I am not totally convinced that the answer will be correct - I haven't tested it thoroughly, but I have some examples which are far too small, and one example which is much too big, but would be correct if the plan were supposed to be reporting the size that "sort_area_size" would have to be to do an in-memory sort to create the index.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005