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: cosmin ioan <>
Date: Fri, 6 Oct 2006 03:59:41 -0700 (PDT)
Message-ID: <>

thanks v. much Jonathan for your input. I shall try that when I get back in the office.   The workaround without the inline views for us would have been about 8-10 times slower so that was the last resort. Probably the no_merge should not be that bad.    

  Anyway, it was great seeing you at Lalandia ... and it's awesome seeing (the others I've met in Denmark) posting in this "underground" Oracle forum. Wow... this is fast becoming my second "AskTom", a virtual "Miracle" support forum, if I may.... ;-)    

  thanks for all the tips,

Jonathan Lewis <> wrote:   

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) */ ..
... , 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 - 05:59:41 CDT

Original text of this message