Confessions of an Oracle Database Junkie - Arup Nanda
The opinions expressed here are mine and mine alone. They may not necessarily reflect that of my employers and customers - both past or present. The comments left by the reviewers are theirs alone and may not reflect my opinion whether implied or not. None of the advice is warranted to be free of errors and ommision. Please use at your own risk and after thorough testing in your environment.

Pages

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.

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.

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.