Friday, June 08, 2012

Quiz: Mystery of Create Table Statement

Happy Friday! I thought I would jumpstart your creative juices with this little, really simple quiz. While it's trivial, it may not be that obvious to many. See if you can catch it. Time yourself exactly 1 minute to get the answer. Tweet answer to me @arupnanda

Here it goes. Database is 11.2.0.3. Tool is SQL*Plus.

The user ARUP owns a procedure that accepts an input string and executes it. Here is the procedure.

create or replace procedure manipulate_arup_schema
(
        p_input_string  varchar2
)
is
begin
        execute immediate p_input_string;
end;
/

The user ARUP have granted EXECUTE privileges on this to user SCOTT. The idea is simple: SCOTT can create and drop tables and other objects in ARUP's schema without requiring the dangerous create any table system privilege.

With this, SCOTT tries to create a table in the ARUP schema:

SQL> exec arup.manipulate_arup_schema ('create table abc (col1 number)')

PL/SQL procedure successfully completed.
The table creation was successul. Now SCOTT tries to create the table in a slightly different manner:

SQL> exec arup.manipulate_arup_schema ('create table abc1 as select * from dual');
It fails with an error:

BEGIN bus_schema.manipulate_bus_schema ('create table abc as select * from dual'); END;

*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at "ARUP.MANIPULATE_ARUP_SCHEMA", line 18
ORA-06512: at line 1

Huh? After checking you did confirm that the user indeed doesn't have the quota on tablespace USERS, so the error is genuine; but how did the first table creation command go through successfully?

Tweet me the answer @arupnanda. Aren't on Twitter? Just post the answer here as a comment. I will post the answer right here in the evening. Let's see who posts the first answer. It shouldn't take more than 5 minutes to get the answer.

Have fun.

Update at the end of the Day. Here is the answer:




Oracle 11g R2 introduced a new feature called deferred segment creation. Segments are stored data objects such as tables, views and materialized views. Prior to Oracle 11gR2, when you created a table, a segment was automatically created. The segment was empty; but created it was. From 11gR2, the table is created only in data dictionary, if there is no data. In the second case, the create table statement used create table as select format, which pulled the data from dual to create the table. However the user didn't have quota on tablespace users; so the statement failed. In the first case, the create table statement merely created the table in dictionary; not the segment. Since there was no segment, there was no space consumption; so the unavailability of quota in the tablespace didn't matter and the statement was successful.

It was a simple puzzle; but I have seen many DBAs, even seasoned ones, stumble over. Eventually they get it; but, well..., they should have taken just a few minutes. From all the responses I got - on twitter and this blog - Yasin Baskan (@yasinbaskan) was the first one to get back with correct answer. Several others did eventually; but Yasin takes the honor of being the first one.

Congratulations, Yasin and thank you all who twitted and posted comments here.





Post a Comment

Translate