Генерация тестовых данных в Oracle

Наиболее простым методом генерации тестовых данных в Oracle, является метод комбинирования запросов на основе CONNECT BY LEVEL и пакета DBMS_RANDOM. При помощи запроса мы легко можем генерировать необходимое количество строк, а с используя пакет DBMS_RANDOM, добавить необходимое наполнение полей. Простейшим запросом в этом случае будет такой:

SELECT LEVEL AS ID,
       dbms_random.String('X', 10) AS NAME
FROM dual CONNECT BY LEVEL < 10;

Выводим 9 строк, с двумя полями:
ID      NAME
--      --

1 4txer6oa9h
2 sppv4klnh1
3 oz33rs9fsy
4 0dl8azesaq
5 gc33xxnm2g
6 4i5hmvm6uj
7 5oovp3o4oe
8 a10k5lwrqt
9 9eyxgm98f5

Регулируя значения LEVEL, добиваемся необходимого количества строк. В данном случае, LEVEL так же выступает уникальным идентификатором записи, потому как имеет уникальное значения и может быть использован вместо sequece.
Количество полей, которое необходимо вставить, регулируем подстановкой вызовов необходимого метода DBMS_RANDOM. Наиболее полезные и востребованные методы пакета и примеры их использования:

SELECT dbms_random.value(),
       -- случайное число, больше или равно 0 и меньше чем 1

       dbms_random.value(1,5),
       -- случайное число, в заданой границе

       dbms_random.normal(),
       -- случайное число, как пололожительное так и отрицательное

       dbms_random.random(),
       -- устаревшая функция, не рекомендуется использовать

       dbms_random.string('x',10),
       -- случайная строка, как с буквами так и с цифрами

       trunc(SYSDATE,'yyyy') + dbms_random.value(1,360) 
       -- пример для генерации случайных дат
FROM dual;

Подробное использования пакета DBMS_RANDOM описано на сайте oracle в соответствующем разделе http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_random.htm.

Мониторинг использования индексов

Создавая индекс на поле(я) таблицы, мы не можем с уверенностью утверждать, что они будут использованы оптимизатором (Cost-Based Optimizer), для построения наиболее оптимального плана выполнения запроса.
Для уверенности, что индексы реально используются приложениями, а не занимают попусту дисковое пространство, удобно использовать - Monitoring Index Usage.

Включаем мониторинг использования индекса (на примере индекса EMP_EMP_ID_PK таблицы hr.employees):

1. ALTER INDEX EMP_EMP_ID_PK monitoring usage;

Делаем выборку из таблицы, на которой построен индекс:

1. SELECT * FROM hr.employees e WHERE e.employee_id > 100;

Делаем выборку, чтоб удостовериться что индекс используется:

1. INDEX_NAME                 TABLE_NAME                     MONITORING USED
2. -------------------------- ------------------------------ ---------- ----
3. EMP_EMP_ID_PK              EMPLOYEES                      YES        NO

Значение "NO" в поле USED, говорит о том, что индекс не был использован, как при выполнении последнего запроса так и в целом, при выполнении любых запросов в системе с участием этой таблицы.

Попробуем немного видоизменить запрос, и посмотреть что получится:

1. SELECT * FROM hr.employees e WHERE e.employee_id = 100;

Делаем выборку, чтоб удостовериться что индекс используется:

1. INDEX_NAME                 TABLE_NAME                     MONITORING USED
2. -------------------------- ------------------------------ ---------- ----
3. EMP_EMP_ID_PK              EMPLOYEES                      YES        YES

Значение "YES" в поле USED, говорит о том, что индекс был использован.

Теперь можно отключить мониторинг использования индекса:

1. ALTER INDEX index_name nomonitoring usage;

Когда нужно(удобно) использовать Monitoring Index Usage:
Для обычных запросов, которые вы выполняете в ручном режиме в SQL+ либо какой-то другой среде, нет смысла использовать подобный мониторинг. Проще построить план выполнения (Explain Plan), который однозначно скажет, участвует ваш индекс в выборке или нет. Мониторинг использования индексов, очень удобен, когда нужно удостовериться в использовании индексов приложениями, которые взаимодействуют с базой посредством stored procedures, functions либо через технологии ORM (Object-relational mapping). В этом случае,
получить доступ к планам выполнения (Explain Plan) не всегда удобно и очевидно.

P.S. Использовать Monitoring Index Usage нужно осторожно, потому как он не дает понимания, какой именно запрос/процедура/функция использовали индекс в своей работе. Мониторинг удобно включать на индексы, чтобы выявить ненужные/лишние индексы, созданные без понимания того, как в дальнейшем они будут использоваться.

строку в столбец

Элегантное решение по преобразованию строки с разделителем, в столбец:

select
  regexp_substr('1,2,3,4,5','[^,]',level)
from dual
connect by regexp_substr('1,2,3,4,5','[^,]',1,level) is not null

Получить актуальные курсы валют по HTTP

Нам необходимо реализовать механизм получение актуальных курсов валют национального банка, доступного из нашей базы данных. в нашем примере, это будет НБУ (Национальный банк Украины). НБУ предоставляет источник, актуальных курсов валют в виде XML-документа, доступного по адресу http://bank-ua.com/export/currrate.xml, нам остается загрузить этот документ и распарить его.

XML-документ представляет собой следующую структуру:

<?xml version="1.0" encoding="windows-1251"?> 
<chapter> 
  <item> 
    <date>2011-02-11</date> 
    <code>036</code> 
    <char3>AUD</char3> 
    <size>100</size> 
    <name>австралійських доларів</name> 
    <rate>797.0465</rate> 
    <change>-5.3082</change> 
  </item>
</chapter>

На первом этапе, пытаемся получить данные с сервера по HTTP протоколу, для этого будем использовать стандартный пакет UTL_HTTP. Потом при помощи механизмов работы с XML, парсим полученный ответ и возвращаем результирующий курсор. Вместо курсора, может быть любая друга обработка данных, загрузка в таблицы, рассылка на почтовые адреса и т.д.
Процедура, которая получилась в итоге

CREATE OR REPLACE PROCEDURE GetCurrencyRate (
 p_Cursor  out sys_refcursor
) IS
 v_Result      CLOB;
 v_Request     UTL_HTTP.Req;
 v_Response    UTL_HTTP.Resp;
 v_Block       VARCHAR2(6000);
BEGIN
  -- устанавливаем ожидание ответа равным 10 секунд
 UTL_HTTP.Set_Transfer_Timeout(10);                      
  -- делаем запрос по указанному URL
 v_Request := UTL_HTTP.Begin_Request('http://bank-ua.com/export/currrate.xml');
  -- устанавливаем соответствующую кодировку ответа
 UTL_HTTP.Set_Body_Charset(v_Request, 'windows-1251');                         
  -- получаем ответ
 v_Response := UTL_HTTP.Get_Response(v_Request);
  -- читаем блоками полученный ответ
  begin
    loop
    v_Result := v_Result || v_Block;
    UTL_HTTP.Read_Text(v_Response, v_Block);
    end loop;
  exception
    when UTL_HTTP.End_Of_Body then
     null;
  end;               
  -- завершаем запрос и ответ
 UTL_HTTP.End_Response(v_Response);
  -- получаем ответ в результирующий курсор
  open p_Cursor for
    select
     extract(value(t),'//date/text()').getStringVal()   as CurrencyDate
      ,extract(value(t),'//code/text()').getStringVal()   as CurrencyCode
      ,extract(value(t),'//char3/text()').getStringVal()  as CurrencyChar3
      ,extract(value(t),'//size/text()').getStringVal()   as CurrencySize
      ,extract(value(t),'//name/text()').getStringVal()   as CurrencyName
      ,extract(value(t),'//rate/text()').getStringVal()   as CurrencyRate
      ,extract(value(t),'//change/text()').getStringVal() as CurrencyChange                      
    from table(xmlsequence(xmltype(v_Result).extract('chapter/item'))) t;

END GetCurrencyRate;

Неполное восстановление базы данных на момент времени

Если технология FLASHBACK вам по каким-то причинам не доступна, а восстановить данные или состояние объектов нужно, на определенный момент времени, то можно воспользоватся довольно простым и удобным способом, хоть и не совсем оптимальным. Для этого, ваша база должна быть в режиме ARCHIVELOG, а также у вас должны быть в наличии, относительно свежие бекапы.

Единственный, но очень существенный, недостаток этого способа, кроется в его названии. Неполное восстановление, говорит о том, что вы теряете часть актуальной информации. Все изменения, которые делались после точки восстановления (на которую вы хотите откатить базу), будет утеряна.

Чтоб избежать этого, можно восстановить бекап в отдельную базу, а потом с нее уже получить всю необходимую информацию. В таком случае вы ничего не теряете. Этот метод будет рассмотрен более подробно в следующих постах, а пока поговорим о случае, когда вы готовы пожертвовать информацией, после точки восстановления. Итак, сам метод:

RMAN> shutdown immediate;   

RMAN> startup mount;

RMAN> RUN 
{ 
# SET UNTIL TIME 'Nov 15 2002 09:00:00';       
# SET UNTIL SCN 1000;      
# SET UNTIL SEQUENCE 9923; 
RESTORE DATABASE;       
RECOVER DATABASE;   
} 
RMAN> ALTER DATABASE OPEN RESETLOGS;

Неполное восстановление, предполагает установку временной метки, до которой будет восстанавливаться база, существует три варианта установки этой точки (TIME, SCN, SEQUENCE), наиболее оптимальным и удобным, как мне кажется, является работа с последним. SEQUENCE являет собой обычное число, порядковый номер группы Redologs, который возрастают на одно значение, каждый раз, когда происходит переключение на следующую группу при заполнении предыдущей. Посмотреть, номера и даты переключения SEQUENCE, на которые вы хотите восстановить базу, можно в представлении V$LOG_HISTORY.

После востановления, открываем базу с опцией RESETLOGS, так как мы делаем неполное восстановление, текущие изменения в REDO логах, нам уже не нужны.

Зачем это все?

Давно хотел создать место, для сбора полезной информации, в первую очередь для себя, но если кому-то тоже пригодится в работе, буду только рад!

Буду стараться, здесь собирать реальные, рабочие примеры, готовые решения для каких-то практических задач с использование SQL, PL/SQL и других возможностей Oracle.