Re: Change in statement level read-consistent behaviour with _ae (edition based redefintion) dictionary views

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Tue, 7 May 2013 01:20:40 +0400
Message-ID: <CAOVevU7R6qnHCEtzMN+L1rBeq1urhiTc8ruN=LSjGx8ip-u7iA_at_mail.gmail.com>

In fact, the entry in sys.obj$ is inserted in recursive query before the "select runs". So same result you can get simply with sys.obj$: select * from sys.obj$ where name='FROM_SYS_OBJ$'; create table FROM_SYS_OBJ$ as
select * from sys.obj$ where name='FROM_SYS_OBJ$'; select * from FROM_SYS_OBJ$;

On Mon, May 6, 2013 at 7:11 PM, Chris Saxon <chris.saxon_at_gmail.com> wrote:

> Hi,
> I made a recent blog post about "non-existent" objects in the _ae
> dictionary views when Matthias Rogel noticed in the
> comments<
> http://www.sqlfail.com/2013/05/06/the-non-existent-edition/#comment-1334
> >that
> there seems to be some unusual behaviour when performing a CTAS using
> these. His example is as follows:
>
> select
> object_name,
> 'contains one row when created with a "create table as empty - query"' as
> paradoxon
> from user_objects_ae
> where object_name =
> 'DOES_NOT_YET_EXIST_BUT';
>
> no rows selected
>
> create table DOES_NOT_YET_EXIST_BUT
> as
> select
> object_name,
> 'contains one row when created with a "create table as empty - query"' as
> paradoxon
> from user_objects_ae
> where object_name =
> 'DOES_NOT_YET_EXIST_BUT';
>
> sokrates_at_11.2 > select * from DOES_NOT_YET_EXIST_BUT;
>
> OBJECT_NAME
>
> -----------------------------------------------------------------------------
> PARADOXON
> --------------------------------------------------------------------
> DOES_NOT_YET_EXIST_BUT
> contains one row when created with a "create table as empty - query"
> Basically this is creating a table using a query on user_objects_ae for the
> name of the table to be created. As this doesn't exist when the statement
> is parsed and therefore the query returns no rows, I would expect the table
> to be empty, but it isn't! It contains one row.
>
> If you use this example above with the normal user_objects view (rather
> than _ae), the new table is empty as you'd expect.
>
> I've verified this in 11.2.0.2. Can anyone else see this effect?
>
> Can anyone explain why this is happening please?
>
> Thanks,
> Chris
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org


--
http://www.freelists.org/webpage/oracle-l

Received on Mon May 06 2013 - 23:20:40 CEST