Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Please see the following statements. My question is, the table t1 was created in the users tablespace, so it will take up some space. But why it can be created before I impose the quota on the users tablespace?

Thanks.

sys@ORCL>create user a identified by a account unlock;

User created.

sys@ORCL>create table a.t1(c int);

Table created.

sys@ORCL>select owner, table_name, tablespace_name from dba_tables where table_name = 'T1';

OWNER                  TABLE_NAME             TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
A                  T1                 USERS

sys@ORCL>insert into a.t1 values(1);
insert into a.t1 values(1)
              *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


sys@ORCL>alter user a quota 10M on users;

User altered.

sys@ORCL>insert into a.t1 values(1);

1 row created.

sys@ORCL>commit;

Commit complete.

sys@ORCL>

BTW, I've tested using the user a to create the table before the user was imposed the quota. (Suppose the user has create session and create table privilege) The table still can be created. But in my example I'm creating the table using SYS user.

Edit:

This is the version information:

sys@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

sys@ORCL>
share|improve this question
    
Because you are logged in as sys? –  Jack Douglas Oct 25 '11 at 19:03
    
No Jack. I've tested using the user a itself, it still can create the table. But I'm not posted the commands. –  Yousui Oct 25 '11 at 19:08
    
interesting, I get ORA-01950: no privileges on tablespace 'USERS' until I set the quota –  Jack Douglas Oct 25 '11 at 19:33
    
please post your version and preferably the exact actual commands you are running –  Jack Douglas Oct 25 '11 at 19:36
    
I've added the version information. Yes I've posted the exact commands that's I'm executed. I copied them directly from sqlplus. –  Yousui Oct 25 '11 at 19:56

1 Answer 1

up vote 5 down vote accepted

Because of deferred segment creation.

In Oracle 11.2, when you create a table with no data, you no longer allocate any space in the tablespace. Oracle doesn't actually create the segment until you try to insert data into the table. This is a difference from earlier versions in which the segment was created when the table was created rather than when data was inserted.

The reason for the new feature is that there are a lot of packaged applications on the market that create potentially hundreds of tables that will never have data because the customer isn't using some specific module of the project that uses those tables. It was annoying for someone that packaged a big ERP tool, for example, to end up with potentially GB of space allocated to tables that would never have any data.

You'll only see deferred segment creation if you've set COMPATIBLE to 11.2 and if DEFERRED_SEGMENT_CREATION is set to the default of TRUE

SQL> conn / as sysdba
Connected.
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0
SQL> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> create user deferred identified by deferred default tablespace users;

User created.

SQL> crant create table, create session to deferred;
SP2-0734: unknown command beginning "crant crea..." - rest of line ignored.
SQL> grant create table, create session to deferred;

Grant succeeded.

SQL> conn deferred/deferred
Connected.
SQL> create table test( col1 number );

Table created.

SQL> insert into test values( 1 );
insert into test values( 1 )
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
share|improve this answer
    
Interesting - but then why is it not the same for me on 11.2 too? Can you check you can reproduce Yousui's test? –  Jack Douglas Oct 25 '11 at 20:15
1  
@JackDouglas - What are your COMPATIBLE and DEFERRED_SEGMENT_CREATION parameters set to? –  Justin Cave Oct 25 '11 at 20:21
    
compatible = '11.1.0.0.0' - well done :) –  Jack Douglas Oct 25 '11 at 20:24
1  
I guess some of the docs need updating, eg "You must assign Nick a tablespace quota on his default tablespace before he can create objects in that tablespace" –  Jack Douglas Oct 25 '11 at 20:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.