What is the default quota on a tablespace for a given user in oracle if you don't set one?

Question

My environment

I'm using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Explanation of the situation

I was creating a user with the following command

create user USRPRACTICA2 identified by 1234 default tablespace users;

So ok, I assigned it a default tablespace, but I HAVEN'T SPECIFIED any quota limit. So then I went to the Oracle docs, and I read this:

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.

So then I said well, ok! Now I want to test if this user really CAN'T create tables for example, if I didn't assigned it a quota.
Then, I granted it these privileges:

grant create session to USRPRACTICA2;
grant create table to USRPRACTICA2; 

Well, now I shouldn't be able to create tables since I didn't specify a quota on the tablespace users. Let's try it.
I create a connection with USRPRACTICA2 to oracle, I try to create a table, and this is what happens:

SQL> CREATE TABLE EMP
  2  (EMPNO NUMBER(4) NOT NULL,
  3  ENAME VARCHAR2(10),
  4  JOB VARCHAR2(9),
  5  MGR NUMBER(4),
  6  HIREDATE DATE,
  7  SAL NUMBER(7, 2),
  8  COMM NUMBER(7, 2),
  9  DEPTNO NUMBER(2));

Tabla creada.

Oops! The table was created.

At this moment is when I think "well, we should now try to figure out what is the quota assigned to that user on that tablespace, maybe there's a default quota in oracle". So I tried this:

SQL> select tablespace_name, username, max_bytes
  2     from dba_ts_quotas
  3     where username = 'USRPRACTICA2' and TABLESPACE_NAME = 'USERS';

no rows selected

At this point I was thinking "this is a nonsense, maybe I didn't write the query properly", so I tried this:

SQL> select * from dba_ts_quotas;

TABLESPACE_NAME                USERNAME                                                                                                                      BYTES  MAX_BYTES     BLOCKS MAX_BLO
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ----------
SYSAUX                         AUDSYS                                                                                                                       655360         -1         80
SYSAUX                         GSMADMIN_INTERNAL                                                                                                            917504         -1        112
SYSAUX                         DBSFWUSER                                                                                                                         0         -1          0
SYSAUX                         APPQOSSYS                                                                                                                         0         -1          0
SYSAUX                         GGSYS                                                                                                                             0         -1          0
SYSAUX                         OLAPSYS                                                                                                                           0         -1          0

6 rows selected.

So with this information I guessed that, for sure, no quota was assigned for on users tablespace for the user that I created.
Also, I guessed that in oracle it should be stored some value with the default quota on tablespaces if you don't set one. Furthermore, that user also doesn't have unlimited space on the tablespace, because It would have appeared in that view with a "MAX_BYTES -1" value.

I browsed a lot looking for a default quota value in oracle, and all I could get is that snippet from oracle saying "If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects". But obviously like I showed here, It should be a default quota value somewhere.

To finish, I just tried another thing. If you do assign a quota value for the user like this

alter user USRPRACTICA2 quota 1M on USERS; 

Now if you query the tablespace quotas, you'll find the information

SQL> select tablespace_name, username, max_bytes
  2     from dba_ts_quotas
  3     where username = 'USRPRACTICA2' and TABLESPACE_NAME = 'USERS';

TABLE USERNAME      MAX_BYTES
----- ------------ ----------
USERS USRPRACTICA2    1048576

So my question here is...

Is there a default quota value assigned to a user in a tablespace if you don't specify it? If so, how can I show that value?

Answer 1

The default value is 0.

You were able to create a table befause of deferred segment creation.

CREATE TABLE

deferred_segment_creation

Use this clause to determine when the database should create the segment(s) for this table:

SEGMENT CREATION DEFERRED: This clause defers creation of the table segment — as well as segments for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table — until the first row of data is inserted into the table. At that time, the segments for the table, LOB columns and indexes, and explicitly created indexes are all materialized and inherit any storage properties specified in this CREATE TABLE statement or, in the case of explicitly created indexes, the CREATE INDEX statement. These segments are created regardless whether the initial insert operation is uncommitted or rolled back. This is the default value.

Caution:When creating many tables with deferred segment creation, ensure that you allocate enough space for your database so that when the first rows are inserted, there is enough space for all the new segments. SEGMENT CREATION IMMEDIATE: The table segment is created as part of this CREATE TABLE statement.

Immediate segment creation is useful, for example, if your application depends upon the object appearing in the DBA_, USER_, and ALL_SEGMENTS data dictionary views, because the object will not appear in those views until the segment is created. This clause overrides the setting of the DEFERRED_SEGMENT_CREATION initialization parameter.

With deferred segment creation, you just created the metadata in the dictionary for the table, but in the tablespace, nothing was created.

SQL> create user u1 identified by u1 default tablespace users;

User created.

SQL> grant create table to u1;

Grant succeeded.

SQL> create table u1.t1 (c1 number);

Table created.

SQL> create table u1.t2 (c1 number) segment creation immediate;
create table u1.t2 (c1 number) segment creation immediate
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Even though you have a table, trying to insert data into it will fail without a quota:

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