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 systemoracle 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 librarylibuser 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 systemoracle SQL> alter user library quota unlimited on libgeneral; User altered. sqlplus librarylibuser SQL> insert into t1 values (1); 1 row created. sqlplus systemoracle SQL> alter user library quota 0 on libgeneral; User altered. sqlplus librarylibuser SQL> insert into t1 values (1); 1 row created.