Resizing Temporary Tablespace

Em muitos sistemas utilizamos os temp_files das tablespaces temporárias como autoextensible on, porém devido a consultas e ordenações errôneas pode-se consumir espaço desnecessário.

Para recuperarmos o espaço ocupado por essa tablespace podemos proceder da seguinte maneira de acordo com a versão do banco:

1. Drop/recreate
2. Drop tempfile - 9i ou posterior
3. Shrink temporary tablespace

1. Drop/recreate

Not a default temporary tablespace:

Para tablespaces não default o metodo é simples, basta dropar a tablespace e recria-la como desejado:

select tablespace_name, contents from dba_tablespaces;

TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT
EXAMPLE PERMANENT
SEC_TEMP TEMPORARY

SELECT * FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

DROP TABLESPACE SEC_TEMP;

Tablespace dropped.

CREATE TEMPORARY TABLESPACE SEC_TEMP
TEMPFILE 'C:\APP\ORADATA\ORCL\SEC_TEMP01.DBF' SIZE 10M REUSE
AUTOEXTEND ON NEXT 5M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

Default temporary tablespace:


Para default temporary tablespace o processo é similar, porém, devemos criar um nova tablespace com os parametros desejados atribui-la como a nova tablespace default e dropar a antiga:

select tablespace_name, contents from dba_tablespaces;

TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT
EXAMPLE PERMANENT
SEC_TEMP TEMPORARY

SELECT * FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

CREATE TEMPORARY TABLESPACE SEC_TEMP
TEMPFILE 'C:\APP\ORADATA\ORCL\SEC_TEMP01.DBF' SIZE 10M REUSE
AUTOEXTEND ON NEXT 5M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE SEC_TEMP;

Database altered.

DROP TABLESPACE TEMP;

Tablespace dropped.

Drop tempfile - 9i ou posterior


Há outro método para redimensionar a tablespace temporary, nesse exemplo basta dropar e recriar o tempfile:

SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES/1024/1024
FROM DBA_TEMP_FILES;

FILE_NAME FILE_ID TABLESPACE_NAME
----------------------------------- ---------- ------------------------------
BYTES/1024/1024
---------------
C:\APP\ORADATA\ORCL\TEMP01.DBF 1 TEMP
10

ALTER DATABASE TEMPFILE 'C:\APP\ORADATA\ORCL\TEMP01.DBF' DROP INCLUDING DATAFILES;

Database altered.

ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\ORADATA\ORCL\TEMP01.DBF' SIZE 5m
AUTOEXTEND ON NEXT 1m MAXSIZE UNLIMITED;

Tablespace altered.

Shrink temporary tablespace 11g


Já na versão 11g é possível recuperar o espaço através do comando shrink, que é utilizado em nível da tablespace.

FILE_NAME FILE_ID TABLESPACE_NAME
----------------------------------- ---------- ------------------------------
BYTES/1024/1024
---------------
C:\APP\ORADATA\ORCL\TEMP01.DBF 1 TEMP
100

SQL> ALTER TABLESPACE TEMP SHRINK SPACE;

Tablespace altered.

SQL> SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES/1024/1024
2 FROM DBA_TEMP_FILES;

FILE_NAME FILE_ID TABLESPACE_NAME
----------------------------------- ---------- ------------------------------
BYTES/1024/1024
---------------
C:\APP\ORADATA\ORCL\TEMP01.DBF 1 TEMP
1,0625