Tablespace e quota no Oracle 19c: ORA-01950

O usuário foi criado com DEFAULT TABLESPACE USERS, recebeu CREATE TABLE, criou a tabela sem reclamar. No primeiro INSERT, a aplicação devolve ORA-01950 e alguém abre um chamado dizendo que o banco "perdeu a permissão". Nada foi perdido. Faltou a quota, e a quota no Oracle é uma coisa separada da tablespace default, por mais que as duas apareçam juntas no CREATE USER.

Montei os dois casos no Oracle 19c multitenant do lab, e os erros abaixo são os que ele devolveu de verdade.

Default tablespace e quota no Oracle: o que cada uma controla

A DEFAULT TABLESPACE responde para onde vai um segmento quando o CREATE TABLE ou o CREATE INDEX não diz a tablespace. A TEMPORARY TABLESPACE é onde o Oracle aloca os segmentos temporários de um ORDER BY ou de um join grande. A quota responde quanto o usuário pode ocupar em cada tablespace.

São três respostas independentes. Um usuário pode ter USERS como default e zero bytes de quota nela, e nesse caso ele tem um endereço padrão onde não pode gravar nada. Ele também pode ter quota em várias tablespaces ao mesmo tempo, e é isso que permite o padrão de separar dados e índices, que aparece mais abaixo.

Usuário criado já com quota

No lab, o CDB1 tem a PDB HUMAN_RESOURCES aberta. Conectado como SYSDBA e dentro da PDB, o jeito mais limpo é declarar a quota no próprio CREATE USER:

alter session set container = HUMAN_RESOURCES;

create user hr_app
  identified by "SenhaForte#2026"
  default tablespace users
  temporary tablespace temp
  quota 500M on users;

grant create session, create table to hr_app;

Com isso o HR_APP pode criar segmentos na USERS até 500 MB. O CREATE SESSION é o mínimo para conectar, como mostrei no artigo sobre criar usuários no Oracle.

O roteiro completo com todos os comandos e as saídas está em https://mrdba.com.br/labs/tablespaces-quotas-usuarios?utm_source=blog&utm_medium=artigo&utm_campaign=tablespaces-quotas-usuarios, pronto para rodar no seu ambiente.

ORA-01950: a tabela é criada, o INSERT falha

Agora um segundo usuário, igual ao primeiro, só que sem a cláusula QUOTA:

create user hr_app_nq
  identified by "SenhaForte#2026"
  default tablespace users
  temporary tablespace temp;

grant create session, create table to hr_app_nq;

Conectado como HR_APP_NQ, o CREATE TABLE funciona:

create table colaboradores (
  id      number,
  nome    varchar2(100),
  salario number(10,2)
);

Table created.

Isso confunde muita gente, e no vídeo eu mesmo simplifico dizendo que sem quota o usuário não cria nada. O detalhe está na criação adiada de segmento. No 19c, com DEFERRED_SEGMENT_CREATION = TRUE (o padrão), o CREATE TABLE só registra a tabela no dicionário. O espaço é alocado na primeira linha:

insert into colaboradores values (1, 'Ana', 5000);

ORA-01950: no privileges on tablespace 'USERS'

O erro aparece na hora de alocar o segmento. Por isso ele costuma estourar na aplicação, no primeiro uso, e não no script de criação que o DBA rodou.

Corrigindo com ALTER USER QUOTA

A correção é dar a quota, como SYSDBA, dentro da PDB:

alter user hr_app_nq quota 200M on users;

De volta como HR_APP_NQ, o mesmo INSERT passa, e a USER_SEGMENTS mostra onde o segmento nasceu:

insert into colaboradores values (1, 'Ana', 5000);
commit;

select segment_name,
       tablespace_name,
       bytes/1024/1024 as size_mb
  from user_segments;

SEGMENT_NAME          TABLESPACE_NAME         SIZE_MB
--------------------- ------------------ ------------
COLABORADORES         USERS                      0.06

Os 0,06 MB são a primeira extensão da tabela, com uma linha só.

ORA-01950 e ORA-01536: a diferença

Os dois erros falam de quota e são parecidos no texto, mas apontam para situações diferentes:

Quota em duas tablespaces: dados e índices separados

Um padrão comum em sistemas corporativos é separar tabelas numa tablespace e índices em outra. Hoje se fala menos nisso do que no passado, mas eu continuo usando, porque facilita enxergar o crescimento de cada parte e organizar o armazenamento.

No CDB2 do lab, a PDB SALES_HISTORY sobe MOUNTED, então ela precisa ser aberta antes (alter pluggable database sales_history open;). O banco usa OMF, por isso o DATAFILE não leva caminho:

create tablespace tbs_sh_data
  datafile size 1G autoextend on next 256M maxsize 4G;

create tablespace tbs_sh_idx
  datafile size 1G autoextend on next 256M maxsize 4G;

create user sh_app
  identified by "SenhaForte#2026"
  default tablespace tbs_sh_data
  temporary tablespace temp
  quota 2G on tbs_sh_data
  quota unlimited on tbs_sh_idx;

grant create session, create table to sh_app;

Não existe privilégio CREATE INDEX. Quem tem CREATE TABLE já pode indexar as próprias tabelas, e um grant create index devolve ORA-00990.

Como SH_APP, a tabela vai para a default e o índice é direcionado:

create table vendas (
  id          number,
  data_venda  date,
  valor_total number(12,2)
);

create index idx_vendas_data on vendas(data_venda)
  tablespace tbs_sh_idx;

insert into vendas values (1, sysdate, 100);
commit;

select segment_name, segment_type, tablespace_name
  from user_segments
 order by segment_type, segment_name;

SEGMENT_NAME          SEGMENT_TYPE    TABLESPACE_NAME
--------------------- --------------- ---------------
IDX_VENDAS_DATA       INDEX           TBS_SH_IDX
VENDAS                TABLE           TBS_SH_DATA

O INSERT está ali por causa da criação adiada: sem ele, a USER_SEGMENTS viria vazia.

Cuidado com o UNLIMITED TABLESPACE

O privilégio de sistema UNLIMITED TABLESPACE ignora qualquer quota e deixa o usuário gravar em todas as tablespaces do banco, inclusive as que você nunca pensou em liberar para ele. Até o 11g, conceder a role RESOURCE trazia esse privilégio junto, sem ninguém pedir. A partir do 12c isso deixou de acontecer, mas bancos migrados de versões antigas podem ter herdado o privilégio.

Para conferir quem tem:

select grantee, privilege
  from dba_sys_privs
 where privilege = 'UNLIMITED TABLESPACE'
 order by grantee;

Na PDB recém-criada do lab vêm 13 linhas, todas de usuários administrativos do próprio Oracle, como SYSTEM e OJVMSYS. Se aparecer um usuário de aplicação ali, revogue e trabalhe com quota:

revoke unlimited tablespace from sh_app;

Revogar de quem não tem o privilégio dá ORA-01952, então confira antes.

Zerando a quota com QUOTA 0

Não existe DROP QUOTA. Para tirar a quota de um usuário, o comando é quota 0:

alter user hr_app_nq quota 0 on users;

Os segmentos que ele já tem continuam lá, e ele continua lendo as tabelas. O que acaba é a alocação de espaço novo. Um INSERT grande, que precisa de novas extensões, falha com o outro erro da tabela:

insert into colaboradores
select level, 'Colaborador ' || level, 1000
  from dual
connect by level <= 200000;

ORA-01536: space quota exceeded for tablespace 'USERS'

Isso é útil para congelar o crescimento de um schema que vai ser desativado sem apagar nada.

Consultando a DBA_TS_QUOTAS

A DBA_TS_QUOTAS mostra, por usuário e tablespace, quanto foi usado e qual é o limite:

select username,
       tablespace_name,
       bytes    /1024/1024/1024 as used_gb,
       max_bytes/1024/1024/1024 as max_gb,
       case when max_bytes = -1 then 'UNLIMITED' else 'LIMITED' end as status
  from dba_ts_quotas
 where username in ('HR_APP', 'HR_APP_NQ')
 order by username, tablespace_name;

USERNAME   TABLESPACE_NAME      USED_GB     MAX_GB STATUS
---------- --------------- ------------ ---------- ---------
HR_APP     USERS                   0.00       0.49 LIMITED

MAX_BYTES igual a -1 significa quota ilimitada. Repare que o HR_APP_NQ sumiu: depois do QUOTA 0, a linha dele sai da view, mesmo com a tabela ainda existindo. Quem procura o usuário ali para saber se ele ocupa espaço tem que olhar também a DBA_SEGMENTS.

Boas práticas de quota no Oracle

Nunca dê quota nas tablespaces SYSTEM e SYSAUX, que são do próprio Oracle. Declare a DEFAULT TABLESPACE e a TEMPORARY TABLESPACE no CREATE USER, em vez de depender do padrão do banco.

Para usuários nomeados, de pessoas, prefira quotas com limite. Para usuários de aplicação a minha prática é outra: quando um sistema tem tablespaces exclusivas, por exemplo de dados e de índices de vendas, eu dou quota UNLIMITED nelas para o usuário desse sistema. A tablespace existe só para ele, e o controle de crescimento fica no tamanho máximo dos datafiles. O que eu evito é o UNLIMITED TABLESPACE, que abre o banco inteiro.

O próximo vídeo da série trata de profiles, onde ficam os limites de senha e de recursos do usuário. Se você está montando a base para trabalhar como DBA, o e-book Raízes de um DBA organiza esse caminho: https://mrdba.com.br/roteiro-dba.