Change in statement level read-consistent behaviour with _ae (edition based redefintion) dictionary views
- From: Chris Saxon <chris.saxon@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 6 May 2013 16:11:08 +0100
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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
