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> 

Permissions Required to Create a Materialized View

The idea for this post came from a problem, which I saw on Javier Morales Carreras' blog here.

This example was tested on Oracle 11.2. It shows the permissions required to create a materialized view. First I created a user:

 

SQL> conn / as sysdba

Connected.

SQL> create user andrew identified by reid

  2  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

I logged in as the user and tried to create a materialized view. This failed (obviously):

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

create materialized view mv1 as select * from dual

                                              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

I granted create materialized view to the user:

 

SQL> conn / as sysdba

Connected.

SQL> grant create materialized view to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

…but when I logged in as the user and tried to create a materialized view again, this failed too:

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

create materialized view mv1 as select * from dual

                                              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

This is because you also need the create table privilege before you can create a materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> grant create table to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

When I tried to create a materialized view this time, I saw a different error:

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

create materialized view mv1 as select * from dual

                                              *

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

 

SQL>

 

I attended to the ORA-01950 and the user was then able to create a materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> alter user andrew quota unlimited on users

  2  /

 

User altered.

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

 

Materialized view created.

 

SQL>

 

You need to be able to create tables because a materialized view has an underlying table itself:

 

SQL> select object_name, object_type from user_objects

  2  /

 

OBJECT_NAME     OBJECT_TYPE

--------------- --------------------

MV1             TABLE

MV1             MATERIALIZED VIEW

 

SQL>

 

Oracle does not allow you to drop the table as this would stop the materialized view working:

 

SQL> drop table mv1

  2  /

drop table mv1

           *

ERROR at line 1:

ORA-12083: must use DROP MATERIALIZED VIEW to drop

"ANDREW"."MV1"

 

SQL>

 

Conversely, if you drop a materialized view, the associated table disappears too:

 

SQL> drop materialized view mv1

  2  /

 

Materialized view dropped.

 

SQL> select object_name, object_type from user_objects

  2  /

 

no rows selected

 

SQL>

Create Any Table

This post looks at the Create Any Table privilege. It was tested on Oracle 9. First create a user called A with unlimited quota on tablespace TSA. This means that user A can take up all available space in tablespace TSA if he wants to. There is no particular reason for this. I just wanted to contrast it with the quota for user B, which is limited to 5 megabytes:

SQL> create user a identified by a

  2  quota unlimited on tsa

  3  /

 

User created.

SQL>

User A needs to be able to connect to the database and create tables:

 

SQL> grant create session, create table to a

  2  /

 

Grant succeeded.

SQL>

Now create a user called B. For the purposes of this example, he only needs some quota in a different tablespace:

 

SQL> create user b identified by b

  2  quota 5m on tsb

  3  /

 

User created.

SQL>

Connect to the database as user A and create a table in schema A in tablespace TSA:

 

SQL> conn a/a

Connected.

SQL> create table tab1 (col1 number)

  2  tablespace tsa

  3  /

 

Table created.

SQL>

Try to create a table belonging to user B. This fails:

 

SQL> create table b.tab2 (col1 number)

  2  tablespace tsa

  3  /

create table b.tab2 (col1 number)

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL>

User A needs the create any table privilege if he wants to create tables in other schemas. Grant this privilege and try it out. It still fails because although user A is creating the table, it belongs to user B and it is he who needs the quota in the tablespace:

 

SQL> conn / as sysdba

Connected.

SQL> grant create any table to a

  2  /

 

Grant succeeded.

 

SQL> conn a/a

Connected.

SQL> create table b.tab3 (col1 number)

  2  tablespace tsa

  3  /

create table b.tab3 (col1 number)

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'TSA'

SQL>

This time, user A creates the table in tablespace TSB. This works as user B owns the table AND has a quota in the tablespace: 

 

SQL> create table b.tab4 (col1 number)

  2  tablespace tsb

  3  /

 

Table created.

 

SQL>

With the create any table privilege, user A is now able to carry out a denial of service attack on user B. He can do this by creating a large table belonging to user B in tablespace TSB. This will use up all B's quota there and as soon as he runs any SQL which needs a new extent to be allocated, that SQL will fall over.

ORA-01950

I was in a hurry to get something finished today. I created a user and specified its default tablespace. I logged in as that user then tried to create a table. This failed with an ORA-01950 as the user had no quota on its default tablespace. I granted the user a quota of 5 megabytes and it was then able to create the table. You can see what I mean in the example, which was tested on an Oracle 9 database:

 

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace user_data

  4  /

 

User created.

 

SQL> grant create session, create table

  2  to andrew

  3  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> create table my_table

  2  (col1 varchar2(10))

  3  /

create table my_table

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USER_DATA'

 

SQL> conn / as sysdba

Connected.

SQL> alter user andrew

  2  quota 5m on user_data

  3  /

 

User altered.

 

SQL> conn andrew/reid

Connected.

SQL> create table my_table

  2  (col1 varchar2(10))

  3  /

 

Table created.

 

SQL>