Com o objetivo de disseminar conhecimento e compartilhar informações, darei início a uma série de posts sobre determinados assuntos do Oracle Database chamada: Oracle de A a Z! O intuito é falar o máximo possível sobre um assunto mostrando desde a criação do item até a administração como um todo, e nesse primeiro momento irei falar sobre Tablespaces Temporárias. O que são? Pra que servem? Quais cuidados tomar?

A série “Oracle de A a Z” irá contar com temas variados, dentre os quais:

Datapump: Expdp/Impdp
Duplicate Database
Backup
Segurança
RAC
Data Guard
Data Masking
Oracle Cloud Control
Tuning

1 – Começe criando sua tablespace temporária com esse simples comando:

[code lang=”sql”]
CREATE TEMPORARY TABLESPACE TEMP01
TEMPFILE ‘+DATA_ASM_DG’
SIZE 1000M
AUTOEXTEND ON NEXT 1000M
MAXSIZE 32000M;
[/code]

2  – Caso precise adicionar ou remover um TEMPFILE na tablespace:

[code lang=”sql”]
–Adicionando
ALTER TABLESPACE TEMP01
ADD TEMPFILE ‘+DATA_ASM_DG’
SIZE 1000M
AUTOEXTEND ON NEXT 1000M
MAXSIZE 32000M;

–Removendo
ALTER DATABASE TEMPFILE ‘+DATA_ASM_DG/ORCL/TEMPFILE/temp_01.1298.1031871495’
DROP INCLUDING DATAFILES;
[/code]

O nome do tempfile utilizado no comando de DROP pode ser encontrado na view abaixo:

[code lang=”sql”]

select FILE_NAME, TABLESPACE_NAME
from dba_temp_files
where tablespace_name = ‘TEMP01’

FILE_NAME CONTENTS
————————————————— ———
+DATA_ASM_DG/ORCL/TEMPFILE/temp_01.1298.1031871495 TEMP01

[/code]

Nota: O erro: ORA-1652: unable to extend temp segment by 128 in tablespace TEMP01 ocorre quando o espaço da tablespace acaba e é preciso adicionar um novo datafile. Outra opção é fazer o SHIRINK na tablespace como é mostrado no item 15.

3 – Se precisar excluir sua tablespace temporária, simplesmente digite:

[code lang=”sql”]
DROP TABLESPACE TEMP01 INCLUDING CONTENTS AND DATAFILES;
[/code]

*Lembrando que não é possível dropar uma tablespace temporária quando seu ambiente se encontra em uma dessas situações:

A) A tablespace está em uso por alguma sessão (ORA-60100: dropping temporary tablespace with tablespace ID number (tsn) 278 is blocked due to sort segments)

B) Ela é a tablespace default do banco de dados (ORA-12906: cannot drop default temporary tablespace)

C) A tablespace pertence a um TEMP GROUP (ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group)

D) Ela é a única tablespace temporária do banco de dados (ORA-12906: cannot drop default temporary tablespace)

4 – Liste quais são as tablespaces temporárias existente no seu banco de dados:

[code lang=”sql”]

select TABLESPACE_NAME, CONTENTS
from dba_tablespaces
where contents = ‘TEMPORARY’;

TABLESPACE_NAME CONTENTS
————— ———
TEMP01 TEMPORARY
TEMP02 TEMPORARY
TEMP03 TEMPORARY
TEMP05 TEMPORARY

[/code]

5 – Qual é a tablespace padrão do banco de dados?

[code lang=”sql”]
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = ‘DEFAULT_TEMP_TABLESPACE’;

PROPERTY_VALUE
———
TEMP01
[/code]

6 – Podemos ter um grupo de de tablespaces temporárias e deixar que o próprio Oracle faça a distribuição das tablespaces entre os usuários:

[code lang=”sql”]
ALTER TABLESPACE TEMP01 TABLESPACE GROUP TEMP_GROUP;
ALTER TABLESPACE TEMP02 TABLESPACE GROUP TEMP_GROUP;
ALTER TABLESPACE TEMP03 TABLESPACE GROUP TEMP_GROUP;
ALTER TABLESPACE TEMP04 TABLESPACE GROUP TEMP_GROUP;
[/code]

7 – Listar os Tablespaces Groups e as Temporary Tablespaces vinculadas a eles:

[code lang=”sql”]
select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME TABLESPACE_NAME
———- ——————-
TEMP_GROUP TEMP01
TEMP_GROUP TEMP02
TEMP_GROUP TEMP03
TEMP_GROUP TEMP05
[/code]

8 – Como remover uma Temporary Tablespace de um Tablespace Group?

[code lang=”sql”]
ALTER TABLESPACE TEMP01 TABLESPACE GROUP ”;
ALTER TABLESPACE TEMP02 TABLESPACE GROUP ”;
[/code]

9 – Podemos ter diversas diversas tablespaces temporárias e alocá-las para diferentes grupos de usuários de acordo com a criticidade de cada um:

[code lang=”sql”]
ALTER USER FINANCEIRO TEMPORARY TABLESPACE TEMP01;
ALTER USER COMPRAS TEMPORARY TABLESPACE TEMP02;
ALTER USER RH TEMPORARY TABLESPACE TEMP03;
ALTER USER TECNOLOGIA TEMPORARY TABLESPACE TEMP04;
[/code]

10 – Quantos usuários estão configurados para usar determinada tablespace:

[code lang=”sql”]
select count(temporary_tablespace) as Total,
temporary_tablespace as Tablespace
From dba_users
group by temporary_tablespace;

TOTAL TABLESPACE
—- ———–
337 TEMP_GROUP
57 TEMP01
28 TEMP02
[/code]

11 – Você pode facilmente alterar a tablespace padrão da sua instância:

[code lang=”sql”]
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GROUP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;
[/code]

12 – Quando você altera a tablespace padrão da instância, automaticamente todos os usuários existentes na base são configurados para utilizar essa tablespace, bem como os novos usuários criados.

[code lang=”sql”]
–Verificando alocação de tablespaces
select count(*) as QTDE, temporary_tablespace
from dba_users
group by temporary_tablespace

TOTAL TABLESPACE
—- ———–
141 TEMP01
23 TEMP02

–Alterando a tablespace padrão para TEMP_GROUP
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GROUP;

–Verificando alocação de tablespaces
select count(*) as QTDE, temporary_tablespace
from dba_users
group by temporary_tablespace

TOTAL TABLESPACE
—- ———–
164 TEMP_GROUP
[/code]

13 – Descubra quais são as sessões que mais estão utilizando segmentos temporários:

[code lang=”sql”]

SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY 7 desc;

[/code]

14 – Eventos do AWR relacionados a tablespace temporária

No Relatório AWR você vai encontrar os seguintes eventos relacionados á tablespace temporária:

–> direct path read temp
–> direct path write temp
–> SMON posted for dropping temp segment
–> physical reads direct temporary tablespace
–> physical writes direct temporary tablespace
–> temp space allocated (bytes)

Saiba como gerar o relatório AWR clicando aqui!

15 – Para reduzir o tamanho de uma tablespace temporária basta usar o comando SHRINK. No exemplo abaixo, a TEMP13 possui 58 GB de espaço livre conforme pode ser visto na coluna FREE_SPACE MB:

[code lang=”sql”]

SELECT TABLESPACE_NAME,
TABLESPACE_SIZE/1024/1024 AS "TABLESPACE_SIZE MB",
ALLOCATED_SPACE/1024/1024 AS "ALLOCATED_SPACE MB",
FREE_SPACE/1024/1024 AS "FREE_SPACE MB"
FROM dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE MB ALLOCATED_SPACE MB FREE_SPACE MB
————— —————— —————— ————-
TEMP13 64760 64760 58497

–Reduzindo o tamanho da tablespace:

ALTER TABLESPACE TEMP13 SHRINK SPACE;

–Checar o novo valor em uso da tablespace:

SELECT TABLESPACE_NAME,
TABLESPACE_SIZE/1024/1024 AS "TABLESPACE_SIZE MB",
ALLOCATED_SPACE/1024/1024 AS "ALLOCATED_SPACE MB",
FREE_SPACE/1024/1024 AS "FREE_SPACE MB"
FROM dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE MB ALLOCATED_SPACE MB FREE_SPACE MB
————— —————— —————— ————-
TEMP 63055 1312396 50659

[/code]