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]
