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

Rick,

According to Tom Kyte, this has been fixed in 8.1.7.4:

http://asktom.oracle.com/pls/ask/f?p=4950:8:8868160921157129407::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4541159655224,

"Followup:
Hdr: 2336505 8.1.7.3 PERFORMANC 11.5.6 PRODID-510 PORTID-59 1709806 Abstract: GETTING ORA-1467 WHEN PERFORMING FND_STATS.GATHER_TABLE_STATS

basically -- dbms_stats is building a query with lots of aggregates. on tables
with lots of large character strings -- this would blow up depending on your block size -- an analyze would work on that table. The fix was to substr just
the first 32 characters (thats all we use) and it would work. It is fixed, in
the terminal release of Oracle8iR3. You can contact support, reference that number and tell them you hit that.

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

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

HTH,
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 Sat Nov 08 2003 - 16:09:54 CST