Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: inline views & unions & other schemas; ora bug?

Re: inline views & unions & other schemas; ora bug?

From: Jonathan Lewis <>
Date: Fri, 6 Oct 2006 07:19:59 +0100
Message-ID: <025901c6e90f$73a37030$0200a8c0@Primary>

If you can associate the problem with particular views in certain cases, then you may be able to bypass the problem by referencing that view with a no_merge hint. This is likely to make the query run more slowly - but slow may be better than crashing.

select /*+ no_merge(viewx) */ ..

... , viewx, ....

where ...

You may even find that a global view will work in this context (though I haven't tried it) , e.g.

    viewQ includes viewA

select /*+ no_merge( viewQ.viewA) */ .. from

... , viewQ, ....

where ...


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

hello all, I'm getting an ORA-03001 --"unimplemented feature" when I'm trying to select off a view based on other views (in turn based on other views... multiple levels, etc) containing UNIONS and INLINE VIEWS, in some pretty big ORA Apps modules.

  I am **only** getting this error when querying from a different schema, and even then, it happens intermittently, on some instances, on some particular selects in a long UNION set.

  I do not get the error when trying to select **from within** the owner's schema.
  I've tried to reduce the problem to the simplest tables/queries but still cannot put my finger on the issue other than the fact that..the problem seems to go away if I do not use UNION to pull in more sets data.... however this is needed... I traced this to a Metalink article:

  Subject: Ora-3001: "Unimplemented Feature" On Query Using "WITH" and FGAC   Doc ID: Note:361345.1 Type: PROBLEM
  Last Revision Date: 15-MAR-2006 Status: MODERATED

  Has anyone seen anything like this before, can this typically be circumvented?
  (I have query_rewrite_enabled=true, etc)

  my initial thought would be to create/populate some adhoc global temporary tables, do this preloading of a pretty huge ora apps summary beforehand.... or...fix this somehow (the part I'm interested).... or basically to turn (simplistically speaking) a query such as   line1, 1,2,3,4,5,6 from dual (a horizontal layout) into a vertical one   line1

  in effect eliminating the current UNIONS which are causing me this current headache?

  thx much,

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.12.13/463 - Release Date: 04/10/2006

Received on Fri Oct 06 2006 - 01:19:59 CDT

Original text of this message