Minor Problem with Resumable Session

Oracle started allowing sessions with space issues to hang rather than fail in version 9. I had always assumed this only worked if a tablespace ran out of space. However, I read recently that it also works for quota failures so I decided to try this out in an Oracle 19.3 database. First I created a user without any tablespace quota:

SQL> conn / as sysdba

Connected. 

SQL> alter session set container = orclpdb 

  2  /

Session altered.

SQL> create user andrew identified by reid
  2  default tablespace users
  3  /

User created.

SQL> grant create session, create table,
  2  resumable to andrew
  3  /

Grant succeeded.

SQL>

Then I connected as this user and tried to create a table in a resumable session. Instead of hanging, it failed with an ORA-01950:

SQL> conn andrew/reid@orclpdb
Connected.
SQL> alter session enable resumable timeout 3600
  2  /

Session altered.

SQL> create table tab1
  2  (col1 varchar2(1))
  3  segment creation immediate
  4  storage (initial 8k next 8k minextents 2)
  5  /
create table tab1
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

SQL>

...and there was a corresponding message in the alert log:

2021-10-15T18:58:30.541244+01:00
ORCLPDB(3):statement in resumable session 'User ANDREW(125), Session 136, Instance 1' was aborted
2021-10-15T19:09:39.525424+01:00

I gave the user a quota of zero:

SQL> conn / as sysdba
Connected.
SQL> alter session set container = orclpdb
  2  /

Session altered.

SQL> alter user andrew quota 0 on users
  2  /

User altered.

SQL>

Then I ran the test again. This time it halted instead of failing:

SQL> conn andrew/reid@orclpdb
Connected.
SQL> alter session enable resumable timeout 3600
  2  /

Session altered.

SQL> create table tab1
  2  (col1 varchar2(1))
  3  segment creation immediate
  4  storage (initial 8k next 8k minextents 2)
  5  /

...and there was a different message in the alert log:

2021-10-15T19:19:51.593060+01:00
ORCLPDB(3):statement in resumable session 'User ANDREW(126), Session 147, Instance 1' was suspended due to
ORCLPDB(3):    ORA-01536: space quota exceeded for tablespace 'USERS'

In a separate session in blue, I gave the user unlimited quota:

C:\Andrew>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 15 19:27:43 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container = orclpdb
  2  /

Session altered.

SQL> alter user andrew quota unlimited on users
  2  /

User altered.

SQL>

There was a confirmation message in the alert log:

2021-10-15T19:29:04.461911+01:00
ORCLPDB(3):statement in resumable session 'User ANDREW(126), Session 147, Instance 1' was resumed

...and the table was created moments later:

SQL> create table tab1
  2  (col1 varchar2(1))
  3  segment creation immediate
  4  storage (initial 8k next 8k minextents 2)
  5  /

Table created.

SQL>