Перестворення TEMP tablespace для вирішення меджику в ораклі

Махінації з оракловим тимчасовим табличним простором за замовчанням.

Проблема: 

У багатьох користувачів з різного приводу почала з'являтися помилка:

line 1: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/oradata/temp/temp01.dbf'

Відтворення:

Сам спостерігав її (помилку) при виконанні селекту

select * from dba_temp_files

Причому виконання селекту

select * from V$TEMPFILE

повертало доволі притомну відповідь, що вказувала на наявність в базі запису про тимчасовий файл, що й вказував саме на /oradata/temp/temp01.dbf

Аналіз:

Зайшовши на сам сервер я там не те що temp01.dbf не знайшов, а навіть шляху такого не було. Попитавши колег, зрозумів, що база (маю зазначити, що це було тестове середовище) нещодавно відновлювалася з бекапу продуктивної бази. А в продуктиві, вочевидь, цей таб.простір був сконфігурений саме так. Але в тестовому екземплярі оракла все було не так.


Було прийнято рішення перестворити тейблспейс TEMP цієї тестової бази, заразом усунувши і проблему неправильного шляху, і відсутності файлу, і запису в базі про неіснуючий файл.

Послідовність вирішення: 

1. Базу треба перевести в mount моду. Тому вирішив перестартувати її.
   

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   ALTER DATABASE DATAFILE '/oradata/temp/temp01.dbf' OFFLINE DROP;
   ALTER DATABASE OPEN;

2. Оскільки дефолтовий тейблсейс дропнути просто так не можна, бо ж ораклу треба мати якийсь тейблспейс, позначений як дефолтний - створюємо додатковий тейблспейс і передаємо йому признак дефолтового:
   

   CREATE TEMPORARY TABLESPACE TEMP_1 TEMPFILE '/oradata/svbo/temp/temp1.dbf' SIZE 1024M autoextend on next 1024M maxsize 32000M;
   ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_1;

3. Новий тейблспейс вже отримав признак дефолтового, але база ще тримає старий тейблспейс, тому, задля того. аби старий можна було дропнути - перестартуємо базу:
   

   SHUTDOWN IMMEDIATE;
   STARTUP;
   DROP TABLESPACE TEMP INCLUDING CONTENTS; 

    Причому, ораклові деви навчили оракл при створенні тейблспейсу створювати також і файл, але от при його знищенні видаляти датафайли Оракл не вміє - не привчили його деви до таких хитромудростей. То ж треба руками зайти і кільнути цей файл '/oradata/temp/temp01.dbf'.
   P.S. Кажуть, якщо виконати DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; - то вб'ються й файли. ;) Haven't tried it yet.


4. А тепер робимо те саме, тільки вже заміняючи тимчасові об'єкти (тейблспейс і датафайл ) на постійні. Створюємо вже правильний тейблспейс з нормальною назвою, і з датафайлом зі стандартною назвою, оголошуємо це дефолтним і, після рестарту бази - дропаємо тимчасовий у всіх сенсах тейблспейс TEMP_1, а потім - кіляємо і його файл:
   

   CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/svbo/temp/temp01.dbf' SIZE 1024M autoextend on next 1024M maxsize 32000M;
   ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
   SHUTDOWN IMMEDIATE;
   STARTUP;
   DROP TABLESPACE TEMP_1 INCLUDING CONTENTS;