Read Inconsistency?

Alex Nuijten 8

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



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 /

------------------------------ -----------


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".

8 thoughts on “Read Inconsistency?

  1. 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.


    Sunil Gidwani
    Indore (Madhya Pradesh) – INDIA

  2. 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?

  3. 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..

  4. 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.

  5. 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

  6. 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.

Comments are closed.

Next Post

Oracle 10.2 EE on SUSE 10 ES

I signed up a while ago for the SUSE Cool Solutions mailling list and I saw a very good step-by-step from Michel Bluteau regarding "Installing Oracle 10g R2 Database on SLES 10". Although it is not yet a supported environment; it is very good post on how to install Oracle on SUSE Linux.
%d bloggers like this: