Oracle de A a Z: Domine as Tablespaces Temporárias

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:

CREATE TEMPORARY TABLESPACE TEMP01 
TEMPFILE '+DATA_ASM_DG' 
SIZE 1000M 
AUTOEXTEND ON NEXT 1000M 
MAXSIZE 32000M;

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

--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;

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


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

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:

DROP TABLESPACE TEMP01 INCLUDING CONTENTS AND DATAFILES;

 

*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:


select TABLESPACE_NAME, CONTENTS 
from dba_tablespaces 
where contents = 'TEMPORARY';

TABLESPACE_NAME CONTENTS
--------------- ---------
TEMP01          TEMPORARY
TEMP02          TEMPORARY
TEMP03          TEMPORARY
TEMP05          TEMPORARY

 

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

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
---------
TEMP01

 

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:

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;

 

 

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

select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME TABLESPACE_NAME
---------- -------------------
TEMP_GROUP TEMP01
TEMP_GROUP TEMP02
TEMP_GROUP TEMP03
TEMP_GROUP TEMP05

 

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

ALTER TABLESPACE TEMP01 TABLESPACE GROUP '';
ALTER TABLESPACE TEMP02 TABLESPACE GROUP '';

 

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:

ALTER USER FINANCEIRO TEMPORARY TABLESPACE TEMP01;
ALTER USER COMPRAS TEMPORARY TABLESPACE TEMP02;
ALTER USER RH TEMPORARY TABLESPACE TEMP03;
ALTER USER TECNOLOGIA TEMPORARY TABLESPACE TEMP04;

 

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

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

 

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

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GROUP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

 

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.

--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

 

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


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;

 

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:


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

 

 

1 comentário

    • Tiago em 16 de março de 2020 às 17:12

    Excelente! Parabéns!

Deixe uma resposta

Seu e-mail não será publicado.

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

×