Иногда требуется срочно на время расширить временное табличное пространство. К примеру, для пересоздания большого индекса. Самый быстрый путь это добавить в уже используемое табличное пространство ещё один файл. Этот добавление можно сделать и во время активной работы. Но вот как вернуть временное табличное пространство в прежние размеры. Конечно, сделать это можно полностью удалив его и создав заново с нужными размерами. Но что если удалить временное табличное пространство не удаётся, к примеру,  из-за его постоянной занятости. В этом случае нам поможет удаление ранее добавленного  файла. Посмотрим, как это делается:

Добавляем второй файл с размером таким же, как и первый во временное табличное пространство:

SYSTEM@ALFA10G> ALTER TABLESPACE temp ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Temp02.dbf' SIZE 29M; 
 
Табличное пространство изменено 

Удаляем второй файл из временного табличного пространства:

SYSTEM@ALFA10G> ALTER DATABASE TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Temp02.dbf' DROP; 
 
База данных изменена 

Файл был удалён только из базы данных. На уровне операционной системы файл по-прежнему присутствует. Для полного удаления файла необходимо к предыдущей команде добавить опцию DROP INCLUDING DATAFILES:

SYSTEM@ALFA10G> ALTER DATABASE TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Temp02.dbf' DROP INCLUDING DATAFILES; 
 
База данных изменена 

Теперь файл удалён из базы и операционной системы. У этой команды есть аналог:

SYSTEM@ALFA10G> ALTER TABLESPACE temp DROP TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Temp02.dbf'; 
 
Табличное пространство изменено 

Выполнение этой команды приводит к тем же результатам, что и ALTER DATABASE TEMPFILE с опцией  DROP INCLUDING DATAFILES.

Надо заметить, что если файл является единственным в табличном пространстве, а не первым как указано в документации, то попытка его удаления потерпит неудачу. Так же в документации указано, что если файл не пустой, то удалить его так же не удастся. Посмотрим, так ли это на самом деле.

Для начала создадим временную таблицу:

ZH@ALFA10G> CREATE GLOBAL TEMPORARY TABLE zh.t1 
  2>     (id                             NUMBER(11,0), 
  3>     name                           VARCHAR2(50)) 
  4> ON COMMIT PRESERVE ROWS 
  
Таблица создана 

Заполним  таблицу данными:

ZH@ALFA10G> DECLARE 
  2>    i   INTEGER; 
  3> BEGIN 
  4>    FOR i IN 1 .. 2000000 
  5>    LOOP 
  6>       INSERT INTO zh.t1 (id, name) VALUES (i,'ITEM' || i); 
  7>    END LOOP; 
  8> END; 

Посмотрим, сколько места в мегабайтах занимает наш сеанс во временном табличном пространстве:

SYSTEM@ALFA10G>   SELECT s.sid, s.username, s.status, u.tablespace, 
  2>          SUM (u.blocks) * vp.value / 1024 / 1024 sort_size 
  3>     FROM sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp 
  4>    WHERE s.saddr = u.session_addr AND vp.name = 'db_block_size' 
  5> GROUP BY s.sid, s.username, s.status, u.tablespace, vp.value 
  
SID USERNAME STATUS   TABLESPACE SORT_SIZE 
--- -------- -------- ---------- --------- 
158 ZH       INACTIVE TEMP       48        
  
Выбрано: 1 строка 

И так, наш сеанс занял почти всё табличное пространство равное 58 Мб. Второй файл определённо используется. Попробуем удалить его:

SYSTEM@ALFA10G> ALTER TABLESPACE temp DROP TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Temp02.dbf'; 
  
Табличное пространство изменено 

Команда не вызвала исключения. Получается, что второй файл удалился? Чтобы проверить это заглянем в alert.log:

Mon Apr 26 12:50:16 2010 
ALTER TABLESPACE temp DROP TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Temp02.dbf' 
Mon Apr 26 12:50:16 2010 

Completed: ALTER TABLESPACE temp DROP TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Temp02.dbf' 

Похоже, файл удалён. Но! Выполним небольшой запрос:

SQL> SELECT file_name, tablespace_name, bytes, status, user_bytes FROM dba_temp_files; 

FILE_NAME                                        TABLESPACE_NAME BYTES    STATUS    USER_BYTES 
------------------------------------------------ --------------- -------- --------- ---------- 
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF TEMP            30408704 AVAILABLE 29360128   
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF TEMP                     AVAILABLE            
  
Выбрано: 2 строки 

Ссылки  в системном представлении на файл есть, причём со статусом доступный. Сам файл в операционной системе так же присутствует. Так удалён ли файл?

Похоже, не смотря на то, что информация о файле всё же присутствует в контрольном файле,  пространство для этого файла не  распределено. Фактически файл считается удалённым.

Конечно, иногда Oracle правильно отрабатывает удаление файла. В этом случае в alert.log появляются записи, которые честно сигнализируют, что не удалось получить доступ к файлу и удалять его из операционной системы нам придётся вручную:

Mon Apr 26 12:28:52 2010 
ALTER TABLESPACE temp DROP TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Temp02.dbf' 

Mon Apr 26 12:28:53 2010 
WARNING: Cannot delete file C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF 
Mon Apr 26 12:28:53 2010 
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2364.trc: 
ORA-01265: Unable to delete TEMP FILE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF 
ORA-27056: could not delete file 
OSD-04024: Unable to delete file. 
O/S-Error: (OS 32) Процесс не может получить доступ к файлу, так как этот файл занят другим процессом. 

Completed: ALTER TABLESPACE temp DROP TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Temp02.dbf' 

Но несомненным плюсом в этом случае является то, что исчезают любые упоминания о втором файле из системных представлений:

SYSTEM@ALFA10G> SELECT file_name, tablespace_name, bytes, status, user_bytes FROM dba_temp_files 
  
FILE_NAME                                        TABLESPACE_NAME BYTES    STATUS    USER_BYTES 
------------------------------------------------ --------------- -------- --------- ---------- 
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF TEMP            30408704 AVAILABLE 29360128   
  
Выбрано: 1 строка