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:
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>
