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