Re: Update: "ORA-01467 : sort key too long" refreshing MV

"Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote:

>Until then, you can analyze the tables using dbms_stats one by one -- and
>find
>the problem table, use analyze on that one instead. "

Yes, I found that it works using the analyze command.

>
>If interested, also take a look at bug 1709806 on metalink.

Thanks a lot, I will do it.

The question still remains unclear, why a simple call exec dbms_snapshot.refresh(...etc)
can't be executed correctly in a scheduled manner, although it runs perfectly if called interactively.

The accounts running the scrits can run other scripts from the crontab (not related to DB work).

Crazy!

>Dave
>
>
>
>
>"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
>news:su9nqv42q2nhp9raaipue9gpq59gc7v6pa_at_4ax.com...
>> Synopsis:
>> The refresh of materialized views (several) fails if not done
>> interactively. Consecutive analysis (statistics gathering) fails with
>> the dbms_stats package, but not with the analyze command.
>>
>> This is an update to the (still unsolved) problem:
>>
>> 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 error appears while trying to analyze the table (mat. view).
>> But the actual problem is creating the mat. view being analyzed.
>>
>> We are struggling several weeks with this problem now. I was adviced
>> in this forum to trace the operation (exec dbms_refresh.refresh), but
>> the trace file does not reveal any problem (?).
>>
>> The refresh of the mat. view prior to gathering statistics fails, but
>> only when not done manually!!! (i.e. from a cron job or using the "at"
>> command). And: It turns out that if using the "analyze table ...
>> compute statistics" command after a manual (successfull) invocation of
>> the dbms_refresh.refresh procedure, everything goes well (while
>> dbms_stats fails). Very strange.
>>
>> So during the last weeks, I have being calling the refresh procedure
>> manually. Every day. Users are angry, because it takes several hours
>> to complete (it is normally scheduled to run at night) and during this
>> time the mat. view appears as an empty table (and when called from
>> cron, the mat. view stays empty at the end even when it contained
>> data).
>>
>> My question now is, perhaps the SNP background process has crashed.
>> But is it needed at all when using a cron job? This is not an Oracle
>> job from the user_jobs table.
>>
>> The mat. view is a of type "complex", the refresh method is "force"
>> (=complete), and its definition is based mostly on tables from a
>> remote DB using a DB-link (well, the "remote" instance runs on the
>> same server). And it used to run flawlessly for longer than one year.
>>
>> job_queue_processes=1, job_queue_invertals=60
>> Oracle 8.1.7.0 on Sun/Solaris.
>>
>> Thanks in advance
>> Rick Denoire
>>
>
Received on Mon Nov 10 2003 - 15:38:59 CST