May 062013
 
drop procedure is_not_really_gone_but;

select object_name || ' ' || object_type 
                 as "Continues on"
from   user_objects_ae
where  object_name = 
           'IS_NOT_REALLY_GONE_BUT';

Continues on
-----------------------------------
IS_NOT_REALLY_GONE_BUT NON-EXISTENT

create table is_not_really_gone_but ( 
  until_replaced            number,
  by_a_non_editioned_object varchar2(1) 
                            default 'Y',
  which_we_then_drop        varchar2(1)
);

drop table is_not_really_gone_but;

select object_name || ' ' || object_type 
from   user_objects_ae
where  object_name = 
           'IS_NOT_REALLY_GONE_BUT';

no rows selected
If you often create test procedures/fucntions (like me), you can find the _ae dictionary views 
quickly fill with these "NON-EXISTENT" objects. This can be confusing once you start working with 
editions. Hoepfully Oracle will introduce some form of purge option when dropping PL/SQL to provide 
a nicer way to get rid these

For a full script, use this. (NB – requires 11gR2 to see the effect)

  2 Responses to “The NON-EXISTENT Edition”

  1. Thanks Chris, learnt something new today !

    Did you know:


    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"

    Concepts Guide says (
    http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#CNCPT88960
    ):
    “Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent with respect to a single point in time. The point in time to which a single SQL statement is consistent depends on the transaction isolation level and the nature of the query:

    In the read committed isolation level, this point is the time at which the statement was opened.

    The above CTAS saw the data not at the point in time when the statement was opened, but at the point in time when the statement was closed !

    • Very interesting Matthias, I’ve learned something too!

      It seems very strange behaviour, I’ve sent a message about this to the Oracle-L freelists group to see if anyone can explain this.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)