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

From: Chris Saxon <chris.saxon_at_gmail.com>
Date: Mon, 6 May 2013 16:11:08 +0100
Message-ID: <CAJ7OfbMifjLccFt0rWaOGTQX94o=Epvvy+68O_YGK1+8-VrXkA_at_mail.gmail.com>

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

Received on Mon May 06 2013 - 17:11:08 CEST