How do you change the tablespace quota for a user on Autonomous Database on Shared Infrastructure

Autonomous Database come with a predefined database role named DWROLE. This role provides the privileges necessary for most database users. Here are the privileges it grants a user:

CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
ALTER SESSION
CREATE HIERARCHY
CREATE JOB
CREATE MINING MODEL
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
READ,WRITE ON directory DATA_PUMP_DIR

To grant the DWROLE role to a database user, connect to the database as the ADMIN user using any SQL client tool and then enter this SQL statement:

GRANT DWROLE TO user;

Instead of or in addition to granting DWROLE privileges, you can grant individual privileges to users with the GRANT command.

The DWROLE role does not allocate any tablespace quota to the user. If the user is going to be adding data or other objects, you need to grant the user tablespace quota in one of these ways:

• Grant the user UNLIMITED TABLESPACE privileges:

  GRANT UNLIMITED TABLESPACE TO user;

• Grant the user quota to tablespaces individually; for example:

  ALTER USER user QUOTA 500M ON data;

Note

Granting the UNLIMITED TABLESPACE privilege allows a user to use all the allocated storage space. You cannot selectively revoke tablespace access from a user with the UNLIMITED TABLESPACE privilege. You can grant selective or restricted access only after revoking the privilege.