Oracle users, roles and tablespaces: setup script and usage report (SQL)

In Oracle a user and a schema are almost the same thing: creating user APPUSER also creates the schema APPUSER that owns its tables. Roles bundle privileges. Tablespaces are logical storage areas backed by one or more data files; each user has a default tablespace for new objects and needs a quota there to store data.

Before you run it

• Connect to the PDB, not the root. A local user name like appuser is refused in CDB$ROOT, where names must start with C##. The first query prints the container so you can check.
• DATAFILE SIZE 500M without a file name relies on Oracle Managed Files (DB_CREATE_FILE_DEST is set). Otherwise give a path, for example DATAFILE '/u02/oradata/ORCLCDB/orclpdb1/app_data01.dbf' SIZE 500M.
• Change the password. Oracle passwords are case-sensitive; PASSWORD EXPIRE forces the user to choose a new one at first login.

The script

-- app_user_setup.sql - tablespace, role and application user in a PDB, plus a usage report.
-- Run in SQL*Plus or SQLcl as a DBA connected to the PDB. License: CC0-1.0
SET ECHO ON
WHENEVER SQLERROR EXIT SQL.SQLCODE

SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container FROM dual;

CREATE TABLESPACE app_data
  DATAFILE SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

CREATE ROLE app_rw;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO app_rw;

CREATE USER appuser
  IDENTIFIED BY "CHANGE_ME_Str0ng#1"
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON app_data
  PASSWORD EXPIRE;

GRANT app_rw TO appuser;

WHENEVER SQLERROR CONTINUE

COLUMN tablespace_name FORMAT A20
COLUMN used_mb         FORMAT 999,999,990.0
COLUMN max_mb          FORMAT 999,999,990.0
COLUMN used_pct        FORMAT 990.0

SELECT m.tablespace_name,
       m.used_space      * t.block_size / 1024 / 1024 AS used_mb,
       m.tablespace_size * t.block_size / 1024 / 1024 AS max_mb,
       m.used_percent                                 AS used_pct
FROM   dba_tablespace_usage_metrics m
JOIN   dba_tablespaces t ON t.tablespace_name = m.tablespace_name
ORDER  BY m.used_percent DESC;

COLUMN file_name FORMAT A60
SELECT tablespace_name,
       file_name,
       ROUND(bytes / 1024 / 1024)    AS size_mb,
       autoextensible,
       ROUND(maxbytes / 1024 / 1024) AS maxsize_mb
FROM   dba_data_files
ORDER  BY tablespace_name, file_name;

SELECT username, account_status, default_tablespace, temporary_tablespace
FROM   dba_users
WHERE  username = 'APPUSER';

sql [email protected]:1521/orclpdb1.example.com @app_user_setup.sql
sqlplus [email protected]:1521/orclpdb1.example.com @app_user_setup.sql

What each part does

• AUTOEXTEND ON NEXT 100M MAXSIZE 10G lets the data file grow in 100 MB steps up to 10 GB instead of filling up at 500 MB.
• The role app_rw holds the system privileges an application owner needs: log in (CREATE SESSION) and create its own objects. Prefer a role you define over broad built-in roles.
• QUOTA UNLIMITED ON app_data lets the user store data in that tablespace only. Without a quota, CREATE TABLE succeeds but inserting rows fails.
• TEMPORARY TABLESPACE temp is used for sorts and hash joins; TEMP is the usual default name.

Reading the usage report

DBA_TABLESPACE_USAGE_METRICS reports USED_SPACE and TABLESPACE_SIZE in database blocks, which is why the query joins DBA_TABLESPACES for BLOCK_SIZE. For tablespaces with autoextensible files, TABLESPACE_SIZE is the maximum size the tablespace can grow to (taking free storage into account), so USED_PERCENT is measured against that maximum, not against the current file size. The second query lists the data files with their current size, autoextend flag and MAXBYTES.

Queried inside a PDB these views show that PDB’s tablespaces. To see every container at once, connect to the root as a common DBA and use the matching CDB_ views, which add a CON_ID column.

Later changes

ALTER USER appuser QUOTA 2G ON app_data;
ALTER TABLESPACE app_data ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G;
REVOKE CREATE VIEW FROM app_rw;
ALTER USER appuser ACCOUNT LOCK;

Warning: DROP USER appuser CASCADE deletes the user and every object in its schema, and DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES deletes the data and the files. Check what you would lose first with SELECT object_type, COUNT(*) FROM dba_objects WHERE owner = 'APPUSER' GROUP BY object_type and make sure a current backup exists.

Official documentation

• Oracle Database 19c SQL Reference: CREATE USER
• Oracle Database 19c SQL Reference: CREATE TABLESPACE
• Oracle Database 19c Reference: DBA_TABLESPACE_USAGE_METRICS