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.
13 comments:
Sir, didn't get it.
you executed
arup.manipulate_arup_schema ('create table abc1 as select * from dual');
and your in your error procedure and parameter is different.
BEGIN bus_schema.manipulate_bus_schema ('create table abc as select * from all_objects'); END;
Took me little over 5 mins.
OWNER OBJECT_NAME
------------------------------ ----------------
SYS DUAL
PUBLIC DUAL
Thanks,
Vishal
@FG - Many apologies. Sloppy copy and paste error. The error message should have been the same as the actual command, i.e. ... from dual. Anyway, it does not change the underlying question at all.
Deferred segment creation.
Cheers,
Szymon
X.Y>create table abc (col1 number);
Table created.
X.Y>create table abc1 as select * from dual;
create table abc1 as select * from dual
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
It should be Deferred Segment Creation.
select * from dba_segments where owner='TEST';
no rows selected
Sorry it has nothing to do with dual.
VDESAI2@db > exec vdesai1.manipulate_arup_schema ('create table abc1 as select * from dual');
BEGIN vdesai1.manipulate_arup_schema ('create table abc1 as select * from dual'); END;
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "VDESAI1.MANIPULATE_ARUP_SCHEMA", line 1
ORA-06512: at line 1
SYS@db AS SYSDBA> alter user vdesai1 quota unlimited on users;
User altered.
VDESAI2@db > exec vdesai1.manipulate_arup_schema ('create table abc1 as select * from dual');
PL/SQL procedure successfully completed.
SYS@db AS SYSDBA> create user vdesai1 identified by ifmc default tablespace users temporary tablespace temp;
User created.
SYS@db AS SYSDBA> grant connect ,create table to vdesai1;
Grant succeeded.
VDESAI1@db > create table abc (col1 number);
Table created.
VDESAI1@db > create table abc1 as select * from dual;
create table abc1 as select * from dual
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
VDESAI1@db > select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
ABC USERS
--deferred segment creation
SYS@db AS SYSDBA> alter user vdesai1 quota unlimited on users;
User altered.
VDESAI1@db > create table abc1 as select * from dual;
Table created.
One of the previous comments clearly mentioned that it is because of "Deferred Segment Creation" (11g feature)
Deferred segment creation was going to be my guess to but I see lots of others beat me to it :-)
Living it up; living placard statesman n more n more.
compare them here
Thank you for influencing so many readers with your great information. Your article has sparked a lot of thought for me. I will share your views and info with my friend and class fellows. Keep it up.
digital media recovery
very nice sir,
Thanks&Regards
Muhammad Abdul Halim
http://halimdba.blogspot.com
Excellent exercise and proof concepts
Suggest good information in this message, click here.
วิธีการแทงบอล ด้วยสูตรการแทงบอล 1×2 "
Post a Comment