I am studying creating users and granting roles and I have come across this scenario where I grant unlimited quota to a user, insert into the table and then revoke the quota. I expected that after setting quota to 0, the user would not be able to insert into the table. However, the user is still able to insert. I wonder if this is expected, if there is another command to really prevent the user from inserting again. I am using Oracle 19c and user's current priviledges are create session and create table.
sqlplus system\oracle
SQL> CREATE TABLESPACE libgeneral datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/libgeneral.dbf' SIZE 20M AUTOEXTEND ON NEXT 40M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
sqlplus library\libuser
SQL> create table t1 ( idx number ) tablespace libgeneral;
Table created.
SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'LIBGENERAL'
sqlplus system\oracle
SQL> alter user library quota unlimited on libgeneral;
User altered.
sqlplus library\libuser
SQL> insert into t1 values (1);
1 row created.
sqlplus system\oracle
SQL> alter user library quota 0 on libgeneral;
User altered.
sqlplus library\libuser
SQL> insert into t1 values (1);
1 row created.
Best Answer
When you inserted the first row in the empty table, Oracle allocated an extent, which typically contains 8 blocks with default settings for the first extent of a table. 8 blocks (typical size of single block is 8 KB) can store a lot more than 1 row, so even though you revoked the quota, that space is already allocated, and you can write into it, which is expected behavior.
Try inserting a lot more rows and eventually you will get the same error message about the quota.