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

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

Добавляем второй файл с размером таким же, как и первый во временное табличное пространство:
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;
 
PL/SQL procedure successfully completed
Посмотрим, сколько места в мегабайтах занимает наш сеанс во временном табличном пространстве:
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 строка