Read Inconsistency?

September 8, 2006 Database, Oracle, PL/SQL 8 Comments

Riddle me this: Can an object exist in all_objects before it is created?
Well no, of course not.
I was under the assumption that if you create a table with a select-statement, a CTAS – Create Table As Select – that the select was executed prior to the creation of the table. Read Consistency dictates that the result set returned by a Select statement, returns the values as of that particular moment in time.

....

To demonstrate this Tom Kyte has an example similar to this: 

SQL> var rc refcursor
SQL> create table t
  2  as
  3  select *
  4    from dual
  5  /

Table created.

SQL> exec open :rc for select * from t

PL/SQL procedure successfully completed.

SQL> delete from t
  2  /

1 row deleted.

SQL> commit
  2  /

Commit complete.

SQL> print

D
-
X

SQL>

The shortest possible script to demonstrate Read Consistency. The result you see after the Delete followed by a Commit, are returned by the Ref Cursor, which was executed prior to the delete.

Odd or at least Remarkable?

Isn’t this odd then?

SQL> drop table t purge
  2  /

Table dropped.

SQL> select owner, object_name
  2    from all_objects
  3   where owner = 'ALEX'
  4     and object_name = 'T'
  5  /

no rows selected

SQL> create table t
  2  as
  3  select *
  4    from all_objects
  5  /

Table created.

SQL> select owner, object_name
  2    from t
  3   where owner = 'ALEX'
  4     and object_name = 'T'
  5  /

OWNER                          OBJECT_NAME
------------------------------ -----------
ALEX                           T

SQL>

How is it possible that an entry is alread present in the newly created Table T, which should be a copy of ALL_OBJECTS? Apparently, when you create a table, an entry in the datadictionary is created prior to executing the Select-statement…. strange, don’t you think?
That goes to show it, the datadictionary is "special".

Related Posts

About The Author

Alex Nuijten

8 Comments

  1. Alex Nuijten October 10, 2006

    Dear Sunil,

    I took the liberty of removing your phone numbers from the blog for privacy matters.

    To answer your question I do not know what GUI is used with Oracle 11i. Maybe this blog helps to answer your question: http://technology.amis.nl/blog/?p=169

    Alex

  2. Sunil Gidwani October 9, 2006

    Dear Sir

    If possible would you please reply my query?

    We want to develop a web based application and we would incorporate a GUI (Graphical User Interface) in our application just like oracle 11i application UI (User interface). I would like to know that technology behind to develop oracle 11i Web User Interface (Presentation Layer) only.

    Thanks

    Sunil Gidwani
    Indore (Madhya Pradesh) – INDIA

  3. Alex Nuijten September 13, 2006

    That’s a good one, Edwin! Talking about the magic of the Data Dictionary…

  4. Andre Crone September 12, 2006

    Create table is DDL; so for that part read consistency is not required. First the table is created; does that part do an implicit commit? Then the query is started read consistency starts at that point in time?

  5. Edwin van Meerendonk September 11, 2006

    Always nice to see these kind of mind-breakers.
    Too bad you haven’t put the
    create table B as select * from all_tables equivalent as step 1 in it.

    No data found.

    So we have an object and we have no table..

  6. Gary September 11, 2006

    Add on to that thought, if the SELECT fails, the CREATE TABLE fails and is ‘rolled back’.
    So what you are actually seeing in T is uncommited changes in the system tables underlying the data dictionary views. If the SELECT ran for long enough, and you were doing a simultaneous SELECT from all_objects in another session, the ‘T’ object shouldn’t be there.

  7. Michael Friedman September 8, 2006

    I don’t think it’s special.

    You can hardly expect the Select to execute before the table is created – where would the data go?

    I’ve always assumed the steps are:
    1. Parse
    2. Create new table
    3. Open cursor

  8. Jo Olluyn September 8, 2006

    If you look at DBMS_SQL, you’ll find that BEFORE executing a query, you’ll need to FIRST define the result set (DEFINE_COLUMN). In other words, Oracle can create the table before actually executing the query, apparenlty becasue this is inherent to SQL processing.