Re: ORA-01467: sort key too long (while gathering statistics)

Repeat the operation with sql_trace set to true, check to see if there is an obvious recursive sql statement in the trace that is directly responsible for the error, then call it in to Oracle as a TAR.

Is the Analyze command causing the problem - have you tested what happens if you try to get the same job done with dbms_stats ?

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


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


"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
news:5jropvobfhsnqct99fabai877dmip3bp5j_at_4ax.com...


> This one is strange:

>

> ORA-01467 : sort key too long

>

> "Cause: A DISTINCT, GROUP BY, ORDER BY, or SET operation requires a

> sort key longer than that supported by ORACLE. Either too many

columns


> or too many group functions were specified in the SELECT statement.

>

> Action: Reduce the number of columns or group functions involved in

> the operation."

>

> The developer came to me since he found out that the error occurs

when


> analyzing the table (i.e., when statistics are gathered), so he has

no


> way to solve the problem - it is not his own sort operation which

> fails. My only immeiate advice was to gather statistics with the

> estimate_percent option, hoping this will help.

>

> What can be done other here? The maximum sort key is less than one

> Oracle block (8K in this case).

>

> Thanks

> Rick Denoire