WWW.DISSERS.RU

БЕСПЛАТНАЯ ЭЛЕКТРОННАЯ БИБЛИОТЕКА

   Добро пожаловать!

Pages:     | 1 |   ...   | 19 | 20 || 22 | 23 |   ...   | 24 |

«Oracle для профессионалов Том Кайт DiaSoft 2003 торгово-издательский дом Москва • Санкт-Петербург • Киев УДК 681.3. 06(075) Б Б К 32.973.2 К 91 КАЙТ ТОМ К 91 Oracle для профессионалов. Пер. с ...»

-- [ Страница 21 ] --

PL/SQL procedure successfully completed. Итак, я загрузил два файла. Один из них — сценарий, над которым я сейчас работаю, clean.sql. Другой — файл экспорта expdat.dmp, подвернувшийся под руку. Теперь я собираюсь написать функцию, которую можно будет вызывать в SQL-операторах и позволяющую просматривать любой 4000-байтовый фрагмент данных типа BLOB в среде SQL*Plus. Просматривать можно не более 4000 байт, поскольку именно такое ограничение в SQL налагается на размер данных типа VARCHAR2. Представленная ниже функция CLEAN работает аналогично функции SUBSTR для обычной строки, но принимает параметр типа BLOB и необязательные параметры FROM_BYTE и FOR_BYTES. Это позволяет выбирать и выдавать подстроку объекта типа BLOB. Обратите внимание, как используется функция UTL_RAW.CAST_TO_VARCHAR2 для преобразования типа RAW в тип VARCHAR2. Если не использовать эту функцию, байты данных типа RAW перед помещением в переменную типа VARCHAR2 будут преобразовываться в шестнадцатеричное представление. С помощью этой функции мы просто меняем тип данных с RAW на VARCHAR2, не выполняя никаких преобразований:

Приложение А scott@DEV816> create or replace 2 function clean(p_raw in blob, 3 p_from_byte in number default 1, 4 p_for_bytes in number default 4000) 5 return varchar2 6 as 7 l_tmp varchar2(8192) default 8 utl_raw.cast_to_varchar2( 9 dbms_lob.substr(p_raw,p_for_bytes,p_from_byte) 10 );

11 l_char char(1);

12 l_return varchar2(16384);

13 l_whitespace varchar2(25) default 14 chr(13) || chr(10) || chr(9);

15 l_ws_char varchar2(50) default 16 'rnt';

17 18 begin 19 for i in 1.. length(l_tmp) 20 loop 21 l_char := substr(l_tmp, i, 1 ) ;

22 23 — Если символ — "печатный" (а не управляющий), ничего с ним 24 — делать не надо. Если это \, добавить еще один символ 25 — \, поскольку мы будем заменять символы новой строки и 26 — табуляции последовательностями \n, \t и т.д., поэтому 27 — надо различать в файле текст \n и символ новой строки. 28 29 if (ascii(l_char) between 32 and 127) 30 then 31 l_return := l_return || l_char;

32 if (l_char = '\' ) then 33 l_return := l_return || '\';

34 end if;

35 36 — Если символ — пробельный, заменить его 37 — специальным символом типа \r, \n, \t 38 39 elsif (instr(l_whitespace, l_char) > 0) 40 then 41 l_return := l_return || 42 '\' || 43 substr(l_ws_char, instr(l_whitespace,l_char), 1);

44 45 — Вместо всех остальных непечатных символов 46 — просто выдать точку ('.'). 47 48 else 49 l_return := l_return || '.';

50 end if;

51 end loop;

Пакет DBMS_LOB 53 — Теперь надо вернуть первые 4000 байт, поскольку больше 54 — язык SQL все равно не позволит увидеть. После обработки в 55 — строке может оказаться более 4000 символов, поскольку CHR(10) 56 — превратится в \n (используется два байта) и т.д., т.е. это 57 — необходимо. 58 return substr(l_return,l,4000);

59 end;

60 / Function created. scott@DEV816> select id, 2 dbms_lob.getlength(theBlob) len, 3 clean(theBlob,30,40) piece, 4 dbms_lob.substr(theBlob,40,30) raw_data 5 from demo;

ID LEN PIECE RAW_DATA 1 3498 \ndrop sequence 0A64726F702073657175656E636520 blob_seq;

\n\ncreate 626C6F625F7365713B0A0A63726561 table d 7465207461626C652064 2 2048 TE\nRTABLES\nl024\nO 54450A525441424C45530A31303234 \n28\n4000\n 0A300A32380A343030300A0001001F 00010001000000000000 Как видите, теперь можно просматривать текстовые части данных типа BLOB в среде SQL*Plus, как обычный текст, воспользовавшись функцией CLEAN. Если использовать функцию DBMS_LOB.SUBSTR, возвращающую значение типа RAW, мы получим результат в шестнадцатиричном виде. Просматривая шестнадцатиричное представление, можно убедиться, что первый байт первого объекта типа BLOB имеет значение 0A, или CHR(10) — это символ новой строки. В текстовом представлении большого объекта можно увидеть, что функция CLEAN преобразовала 0A в \n (символ новой строки). Это подтверждает, что функция выполнена, как предполагалось. Во втором объекте типа BLOB мы видим много двоичных нулей (значений 00 в шестнадцатиричном представлении) в обычном представлении содержимого файла expdat.dmp. В функции CLEAN, как видите, они преобразуются в точки, поскольку подобные специальные символы, при выдаче непосредственно на терминал, будут выдаваться в нераспознаваемом виде (как мусор). Помимо функции CAST_TO_VARCHAR2 пакет UTL_RAW содержит функцию CAST_TO_RAW. Как было показано ранее, в объект типа BLOB можно поместить обычный текст. Если для изменения этих данных надо использовать строки, пришлось бы преобразовывать их в шестнадцатиричный вид. Например, следующий оператор: scott6DEV816> update demo 2 set theBlob = 'Hello World' 3 where id - 1 4/ set theBlob = 'Hello World' * ERROR at line 2: ORA-01465: invalid hex number Приложение А не работает. При неявном преобразовании данных из типа VARCHAR2 в RAW предполагается, что строка Hello World состоит из шестнадцатиричных цифр. Сервер Oracle берет первые два байта, преобразует их из шестнадцатиричного в десятичный вид и присваивает полученное значение первому байту данных типа RAW, и т.д. Надо либо преобразовать строку Hello World в шестнадцатиричный вид, либо изменить тип данных с VARCHAR2 на RAW — изменить только тип данных, не меняя сами байты данных. Например: scott@DEV816> update demo 2 set theBlob = utl_raw.cast_to_raw('Hello World') 3 where id = 1 4 / 1 row updated. scott@DEV816> commit;

Commit complete. scott@DEV816> select id, 2 dbms_lob.getlength(theBlob) len, 3 clean(theBlob) piece, 4 dbms_lob.substr(theBlob,40,l) raw_data 5 from demo 6 where id =1;

ID LEN PIECE RAW_DATA 1 11 Hello World 48656C6C6F20576F726C Использование UTL_RAW.CAST_TO_RAW('Hello World') обычно намного проще преобразования строки Hello World в шестнадцатиричное представление 48656C6C6F20576F726C64.

Преобразование данных типа LONG/LONG RAW в большой объект Преобразовать данные типа LONG или LONG RAW в большой объект очень просто. Стандартная функция TO_LOB языка SQL позволяет это сделать. Использование функции TO_LOB, однако, весьма ограничено. Ее можно применять исключительно в операторах INSERT или CREATE TABLE AS SELECT и только в языке SQL (но не в PL/ SQL). В результате первого ограничения нельзя выполнять операторы, подобные следующему: alter table t add column clob_column;

update t set clob_column = to_lob(long_column);

alter table t drop column long_column;

При попытке выполнения UPDATE будет получено сообщение об ошибке: ORA-00932: inconsistent datatypes Пакет DBMS_LOB Для множественного преобразования типа в существующих таблицах со столбцами LONG/LONG RAW придется создавать новую таблицу. В большинстве случаев это вполне допустимо, поскольку данные типа LONG и LONG RAW хранятся в самой строке (inline) вместе с остальными данными таблицы. Если преобразовать их в большие объекты, а затем удалить столбец типа LONG, таблица окажется не в лучшем виде: будет много выделенного и неиспользуемого пространства. Такие таблицы лучше пересоздавать. Второе ограничение означает, что функцию TO_LOB нельзя использовать в PL/SQLблоке. Чтобы использовать TO_LOB в PL/SQL, придется прибегнуть к динамическому SQL. Вскоре я это продемонстрирую. В следующих примерах мы рассмотрим два способа использования функции TO_LOB. Один из них — использование функции TO_LOB в операторе CREATE TABLE AS SELECT или INSERT INTO. Другой способ пригодится, когда данные должны остаться в столбце типа LONG или LONG RAW. Например, старому приложению нужен именно тип LONG. Хотелось бы предоставить другим приложениям возможность работать с этим столбцом как с большим объектом, чтобы можно было обрабатывать его значение в PL/SQL по частям с помощью функций пакета DBMS_LOB, например READ и SUBSTR. Начнем с создания данных типа LONG и LONG RAW: ops$tkyte@DEV816> c r e a t e t a b l e long_table 2 (id int primary key, 3 data long 4) 5/ Table created. ops$tkyte@DEV816> create table long_raw_table 2 (id int primary key, 3 data long raw 4) 5/ Table created. ops$tkyte@DEV816> declare 2 l_tmp long := 'Hello World';

3 l_raw long raw;

4 begin 5 while(length(l_tmp) < 32000) 6 loop 7 l_tmp := l_tmp || ' Hello World';

8 end loop;

9 10 insert into long_table 11 (id, data) values 12 (1, l_tmp);

13 14 l_raw := utl_raw.cast_to_raw(l_tmp);

15 16 insert into long_raw_table 17 (id, data) values Приложение А 18 (1, l_raw);

19 20 dbms_output.put_line('created long with length = ' || 21 length(l_tmp));

22 end;

23 / created long with length = 32003 PL/SQL procedure successfully completed.

Пример множественного однократного преобразования типа Итак, имеется две таблицы с одной строкой и столбцом типа LONG или LONG RAW. Преобразование типа данных из LONG в CLOB легко выполнить с помощью следующего оператора CREATE TABLE AS SELECT: ops$tkyte@DEV816> create table clob_table 2 as 3 select id, to_lob(data) data 4 from long_table;

Table created. Кроме того, мы могли создать таблицы ранее и использовать для наполнения ее данными разновидность оператора INSERT INTO: ops$tkyte@DEV816> insert into clob_table 2 select id, to_lob(data) 3 from long_table;

1 row created. Следующий пример показывает, что функция TO_LOB не работает в PL/SQL-блоке, как и следовало ожидать: ops$tkyte@DEV816> begin 2 insert into clob_table 3 select id, to_lob(data) 4 from long_table;

5 end;

6/ begin * ERROR at line 1: ORA-06550: line 3, column 16: PLS-00201: identifier 'TO_LOB' must be declared ORA-06550: line 2, column 5: PL/SQL: SQL Statement ignored Это ограничение легко обойти с помощью динамического SQL (придется выполнять оператор INSERT динамически, а не статически, как в примере выше). Теперь, разобравшись, как преобразовывать данные типа LONG или LONG RAW в тип CLOB или BLOB, рассмотрим производительность такого преобразования. Обычно таблицы со Пакет DBMS LOB столбцами типа LONG и LONG RAW — большого размера. Они большие по определению, поскольку используются для хранения очень больших объектов. Во многих случаях их размер достигает многих гигабайт. Вопрос в том, можно ли выполнить множественное преобразование за допустимое время? Рекомендую использовать следующие возможности: • невосстановимые действия, такие как непосредственная вставка и опция NOLOGGING;

• распараллеливание операторов ЯМД (в частности, параллельные вставки);

• параллельные запросы. Ниже представлен пример использования этих возможностей. У меня есть большая таблица IMAGE, содержащая многие сотни загруженных из Web файлов. Таблица содержит столбцы NAME (название документа), MIME_TYPE (например, application/MSWord), IMG_SIZE (размер документа в байтах) и, наконец, сам документ в столбце типа and LONG RAW. Преобразуем эту таблицу так, чтобы документ хранился в столбце типа BLOB. Можно начать с создания новой таблицы: scott@DEV816> CREATE TABLE "SCOTT"."T" 2 ("NAME" VARCHAR2(255), 3 "MIME_TYPE" VARCHAR2(255), 4 "IMG_SIZE" NUMBER, 5 "IMAGE" BLOB) 6 PCTFREE 0 PCTUSED 40 7 INITRANS 8 MAXTRANS 2 5 5 9 NOLOGGING 10 11 12 13 14 15 16 TABLESPACE "USERS" LOB ("IMAGE") STORE AS (TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 32768 PCTVERSION 10 NOCACHE NOLOGGING );

Table created. Обратите внимание, что таблица и большой объект создаются с опцией NOLOGGING — это важно. Можно не создавать их так сразу, а применить оператор ALTER. Теперь, чтобы преобразовать данные из существующей таблицы IMAGE, выполним следующее: scott@DEV816> ALTER SESSION ENABLE PARALLEL DML;

Session a l t e r e d. scott@DEV816> INSERT /*+ APPEND PARALLEL(t,5) */ INTO t 2 SELECT /*+ PARALLEL(long_raw,5) */ 3 name, mime_type, img_size, to_lob(image) 4 F O long_raw;

RM В результате выполняется непосредственная параллельная вставка в объекты типа BLOB без журнализации. Для сравнения я выполнил INSERT INTO c включенной и Приложение А отключенной журнализацией и получил следующие результаты (на подмножестве преобразуемых строк): scott@DEV816> create table t 2 as 3 select name, mime_type, img_size, to_lob(image) image 4 from image where l=0;

Таblе created. scott@DEV816> set autotrace on scott@DEV816> insert into t 2 select name, mime_type, img_size, to_lob(image) image 3 from image;

99 rows created. Execution Plan 0 1 0 INSERT STATEMENT Optimizer=CHOOSE TABLE ACCESS (FULL) OF 'IMAGE' Statistics 1242 36057 12843 7870 34393500 1006 861 4 2 0 recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) 99 rows processed Обратите внимание, что в результате было сгенерировано 34 Мбайт данных повторного выполнения (если суммировать размеры 99 изображений, получится 32 Мбайт данных). Если таблица T, как было показано ранее, создается с опцией NOLOGGING и используется непосредственная вставка, получим: scott@DEV816> INSERT /*+ APPEND */ INTO t 2 SELECT name, mime_type, img_size, to_lob(image) 3 FROM image;

99 rows created. Execution Plan 0 1 0 INSERT STATEMENT Optimizer=CHOOSE TABLE ACCESS (FULL) OF 'IMAGE' Statistics 1242 recursive calls 36474 db block gets 13079 consistent gets 6487 physical reads Пакет DBMS_LOB 1355104 1013 871 4 2 0 redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed Сгенерировано лишь около 1 Мбайт информации в журнал. Это преобразование выполняется существенно быстрее, при этом генерируется намного меньше данных в журналы повторного выполнения. Конечно, как и для всех невосстанавливаемых операций, необходимо обеспечить резервное копирование базы данных как можно раньше, чтобы новые объекты можно было восстановить. Иначе в случае сбоя диска преобразование данных придется выполнять заново. Представленный выше пример нельзя повторить непосредственно. У меня случайно под рукой оказалась таблица IMAGE, содержащая около 200 Мбайт данных. Она использовалась для демонстрации множественных однократных преобразований и влияния опции NOLOGGING на объем генерируемых при этом данных повторного выполнения.

Оперативное преобразование типа данных Во многих случаях необходимо читать данные типа LONG или LONG RAW в различных средах, но оказывается, что это не получается. Например, при использовании языка PL/SQL, если объем данных типа LONG RAW превышает 32 Кбайт, их практически невозможно прочитать. В других языках и интерфейсах тоже есть проблемы с данными типа LONG и LONG RAW. C помощью функции TO_LOB и временной таблицы, однако, можно оперативно преобразовать данные типа LONG или LONG RAW в тип CLOB или BLOB. Это очень удобно, например, при использовании средств загрузки файлов в OAS4.x или WebDB. Эти средства загружают документы по сети (через Web) в таблицу базы данных, но, к сожалению, загружают они их в столбец типа LONG RAW. Это делает практически невозможной работу с документами в PL/SQL. Представленные ниже функции показывают, как обеспечить прозрачный доступ к таким данным через промежуточный BLOB-объект. Начнем с создания временной таблицы для хранения преобразованного объекта типа CLOB/BLOB и последовательности, идентифицирующей строку: ops$tkyte@DEV816> create global temporary table lob_temp 2 (id int primary key, 3 c_lob clob, 4 b_lob blob 5) 6/ Table created. ops$tkyte@DEV816> create sequence lob_temp_seq;

Sequence created.

Приложение А Теперь создадим функции TO_BLOB и TO_CLOB. Эти функции используют для оперативного преобразования данных типа LONG или LONG RAW следующий подход. • Пользователь выбирает идентификатор строки из таблицы со столбцом типа LONG или LONG RAW, а не значение столбца LONG или LONG RAW в этой строке. Функции передается имя столбца типа LONG, имя таблицы и идентификатор нужной строки. • Функция получает последовательный номер, идентифицирующий строку, которая будет создаваться во временной таблице. • С помощью динамического SQL к указанному столбцу типа LONG или LONG RAW применяется функция TO_LOB. Использование динамического SQL не только делает функцию универсальной (она может работать со столбцом типа LONG в любой таблице), но и позволяет непосредственно вызывать функцию TO_LOB в языке PLSQL. • Функция считывает из временной значение созданного объекта типа BLOB или CLOB таблицы и возвращает вызывающему. Вот код для функций TO_BLOB и TO_CLOB: ops$tkyte@DEV816> create or replace 2 function to_blob(p_cname in varchar2, 3 p_tname in varchar2, 4 p_rowid in rowid) return blob 5 as 6 l_blob blob;

7 l_id int;

8 begin 9 select lob_temp_seq.nextval into l_id from dual;

10 11 execute immediate 12 'insert into lob_temp (id,b_lob) 13 select :id, to_lob(' || p_cname || ') 14 from ' || p_tname || 15 ' where rowid = :rid ' 16 using IN l_id, IN p_rowid;

17 18 select b_lob into l_blob from lob_temp where id - l_id ;

19 20 return l_blob;

21 end;

22 / Function created. ops$tkyte@DEV816> create or replace 2 function to_clob(p_cname in varchar2, 3 p_tname in varchar2, 4 p_rowid in rowid) return clob 5 as 6 l_clob clob;

7 l_id int;

Пакет DBMS_LOB 8 begin 9 select lob_temp_seq.nextval into l_id from dual;

10 11 execute immediate 12 'insert into lob_temp (id,c_lob) 13 select :id, to_lob(' || p_cname || ') 14 from ' || p_tname || 15 ' where rowid = :rid ' 16 using IN l_id, IN p_rowid;

17 18 select c_lob into l_clob from lob_temp where id = l_id ;

19 20 return l_clob;

21 end;

22 / Function created. Теперь можно продемонстрировать использование этих функций с помощью простого PL/SQL-блока. Данные типа LONG RAW в BLOB будут преобразованы, и выдана длина полученного объекта и небольшая часть его данных: ops$tkyte@DEV816> declare 2 l_blob blob;

3 l_rowid rowid;

4 begin 5 select rowid into l_rowid from long_raw_table;

6 l_blob :=to_blob('data', 'long_raw_table', l_rowid);

7 dbms_output.put_line(dbms_lob.getlength(l_blob));

8 dbms_output.put_line( 9 utl_raw.cast_to_varchar2( 10 dbms_lob.substr(l_blob,41/l) 11 ) 12 );

13 end;

14 / 32003 Hello World Hello World Hello World Hello PL/SQL procedure successfully completed. Для тестирования функции TO_CLOB применяется практически такой же код, но использовать средства пакета UTL_RAW не нужно: ops$tkyte@DEV816> declare 2 l_clob clob;

3 l_rowid rowid;

4 begin 5 select rowid into l_rowid from long_table;

6 l_clob :=to_clob('data', 'long_table', l_rowid);

7 dbms_output.put_line(dbms_lob.getlength(l_clob));

8 dbms_output.put_line(dbms_lob.substr(l_clob,41,1));

9 end;

10 / Приложение А 32003 Hello World Hello World Hello World Hello PL/SQL procedure successfully completed.

Запись значений объекта типа BLOB/CLOB на диск Этой возможности в пакете DBMS_LOB недостает. Пакет предоставляет средства загрузки больших объектов из файлов, но не создания файла, содержащего большой объект. Решение этой проблемы предложено в главах 18 и 19. Там приведен код на языке С и Java для внешней процедуры, записывающей значение столбца типа BLOB, CLOB в базе данных или временного большого объекта в файл файловой системы сервера. Обе реализации выполняют одну и ту же функцию, просто использованы разные языки. Применяйте ту из них, которая больше подходит для вашего сервера (например, если на сервере не установлена поддержка языка Java, но есть прекомпилятор Pro*C и компилятор языка С, то внешняя процедура на языке С подойдет больше).

Выдача большого объекта на Web-странице с помощью PL/SQL Представленный ниже пример предполагает, что в системе установлены и работают следующие компоненты: • компонент прослушивания (lightweight listener) WebDB;

• сервер приложений OAS 2.x, З.х или 4.x с PL/SQL-картриджем;

• сервер iAS с модулем mod_plsql. При отсутствии любого из этих компонентов пример выполнить не получится. В нем используется набор инструментальных средств PL/SQL Web Toolkit (речь идет о широко известных функциях HTP), а также PL/SQL-картридж или модуль. Предполагается также, что наборы символов (кодировки) на Web-сервере (клиенте сервера базы данных) и в базе данных совпадают. Дело в том, что PL/SQL-картридж или модуль использует для генерации страниц из базы данных тип VARCHAR2. Если набор символов у клиента (в данном случае клиентом является Web-сервер) отличается от набора символов в базе данных, будет выполнено преобразование. При этом обычно повреждаются данные типа BLOB. Предположим, Web-сервер работает на платформе Windows NT. Обычно для клиента на платформе Windows NT используется набор символов WE8ISO8859P1 — западноевропейская 8-битовая кодовая страница. А сервер баз данных работает на платформе Solaris. Стандартной и наиболее типичной кодовой страницей на этой платформе является 7-битовая US7ASCII. При попытке передачи значения BLOB через интерфейс VARCHAR2 в случае использования такой пары кодовых страниц окажется, что старший бит данных из базы сброшен. Данные изменятся. Только если кодировки на клиенте (Web-сервере) и сервере базы данных совпадают, данные передаются без искажений.

Пакет DBMS LOB Итак, предполагая, что все предварительные условия выполнены, можно рассмотреть использование средств PL/SQL Web Toolkit для выдачи значения BLOB на Webстранице. Продолжим один из предыдущих примеров преобразования, в котором была создана таблица DEMO. Загрузим в нее еще один файл: ops$tkyte@DEV816> exec load_a_file('MY_FILES', 'demo.gif');

PL/SQL procedure successfully completed. Это будет GIF-файл. Теперь необходим пакет, который сможет выбрать это изображение в формате GIF и выдать его на Web-странице. Он может иметь следующий вид: ops$tkyte@DEV816> create or replace package image_get 2 as 3 — Можно задать соответствующее имя процедуры 4 — для каждого типа отображаемых документов, 5 — например: 6 — procedure pdf 7 — procedure doc 8 — procedure txt 9 — и т.д. Некоторые браузеры (MS IE, например) при обработке 10 — документов используют расширения имен файлов, 11 — а не mime-типы 12 procedure gif(p_id in demo.id%type);

13 end;

14 / Package created. ops$tkyte@DEV816> create or replace package body image_get 2 as 3 4 procedure gif(p_id in demo.id%type) 5 is 6 l_lob blob;

7 l_amt number default 32000;

8 l_off number default 1;

9 l_raw raw(32000);

10 begin 11 12 — Получить LOB-локатор для 13 — нашего документа. 14 select theBlob into l_lob 15 from demo 16 where id = p_id;

17 18 — Выдать mime-заголовок для 19 — документа этого типа. 20 owa_util.mime_header('image/gif');

21 22 begin 23 loop 24 dbms_lob.read(l_lob, l_amt, l_off, l_raw);

Приложение А 26 — Важно использовать вызов htp.PRN, чтобы избежать 27 — добавления в документ ненужных символов 28 — перевода строки. 29 htp.prn(utl_raw.cast_to_varchar2(l_raw));

30 l_off := l_off+l_amt;

31 l_amt := 32000;

32 end loop;

33 exception 34 when no_data_found then 35 NULL;

36 end;

37 end;

38 39 end;

40 / Package body created. При наличии DAD (Database Access Descriptor — дескриптор доступа к базе данных, который обычно создается при настройке PL/SQL-картриджа или модуля) с именем mydata можно использовать адрес URL http://myhost:myport/pls/mydata/image_get.gif?p_id=3 для получения изображения. Аргумент P_ID=3 передается процедуре image_get.gif, требующий от нее выдать локатор большого объекта, который хранится в строке со значением id=3. Это изображение можно включить в страницу с помощью тэга IMG: 21 Oracle для профессионалов Том Кайт DiaSoft 2003 торгово-издательский дом Москва • Санкт-Петербург • Киев УДК 681.3. 06(075) Б Б К 32.973.2 К 91 КАЙТ ТОМ К 91 Oracle для профессионалов. Пер. с Это мой GIF-файл Резюме Большие объекты предлагают намного больше возможностей, чем устаревший тип данных LONG. B этом разделе я ответил на некоторые часто задаваемые вопросы, касающиеся работы с большими объектами. Мы рассмотрели, как загружать большие объекты в базу данных. Мы разобрались, как преобразовать данные типа BLOB в CLOB, и наоборот. Мы выяснили, как эффективно преобразовать все существующие унаследованные данные типа LONG и LONG RAW в типы CLOB и BLOB c помощью невосстановимых и распараллеливаемых действий. Наконец, мы обсудили использование средств PL/SQL Web Toolkit для получения данных типа CLOB или BLOB и отображения их на Web-странице.

Пакет DBMS_LOCK Пакет DBMS_LOCK дает программисту доступ к механизму блокирования, используемому сервером Oracle. Он позволяет создавать собственные именованные блокировки. Эти блокировки можно контролировать точно так же, как и любые другие блокировки Oracle. Они будут отображаться в представлении динамической производительности V$LOCK как блокировки типа UL (user lock — пользовательская блокировка). Кроме того, они будут отображаться любыми стандартными средствами, такими как Oracle Enterprise Manager и сценарий UTLOCKT.SQL (который находится в каталоге [ORACLE_HOME]/rdbms/admin). Помимо обеспечения доступа к механизму блокирования пакет DBMS_LOCK (благодаря наличию функции SLEEP) позволяет приостановить работу PL/SQL-программы на указанное количество секунд. Пакет DBMS_LOCK имеет много применений, например. • Предположим, имеется подпрограмма, использующая средства пакета UTL_FILE для записи сообщений проверки в файл операционной системы. Записывать сообщения в этот файл процессы должны поочередно. В некоторых операционных системах, например в ОС Solaris, записывать данные в файл могут одновременно много пользователей (ОС этого не предотвращает). В результате сообщения с данными проверки перемешиваются, и читать их сложно или невозможно. Пакет DBMS_LOCK можно использовать для обеспечения очередности доступа к этому файлу.

• Можно предотвратить одновременное выполнение взаимоисключающих действий. Предположим, имеется программа подготовки данных, которая работает только при условии, что данные не используются другими сеансами. Сеансы не должны Приложение А обращаться к данным в процессе их подготовки. Сеанс подготовки должен устанавливать именованную блокировку в режиме X (как исключительную). Другие сеансы должны пытаться установить эту же именованную блокировку в режиме S (как разделяемую). Запрос блокировки X будет ожидать, если имеются блокировки S, а запрос блокировки S будет ожидать, если удерживается блокировка X. В результате сеанс подготовки данных будет в состоянии ожидания, пока работают "обычные" сеансы, но если сеанс подготовки уже начался, все остальные сеансы будут заблокированы до его завершения. У этого пакета есть два основных варианта использования. Оба они подходят, если все сеансы согласованно используют блокировки (ничто не мешает сеансу использовать средства пакета UTL_FILE для открытия и записи в файл проверки без каких-либо попыток установить соответствующую блокировку). В качестве примера попытаемся решить проблему взаимоисключающего доступа, что пригодится во многих приложениях. Проблема возникает при попытке двух сеансов вставить данные в одну и ту же таблицу, для которой задано требование первичного ключа или уникальности. Если оба сеанса попытаются использовать одни и те же значения в столбцах, связанных этим требованием, второй (третий и т.д.) сеанс будет заблокирован, пока не зафиксируется или отменится транзакция первого сеанса. Когда первый сеанс зафиксирует транзакцию, в заблокированных сеансах будет получено сообщение об ошибке. Только если в первом сеансе будет выполнен откат, один из последующих сеансов сможет успешно выполнить вставку. Суть проблемы в том, что пользователи после вынужденного ожидания узнают, что выполнить необходимое Действие невозможно. Этой проблемы можно избежать при использовании оператора UPDATE, поскольку можно заранее заблокировать строку, которую предполагается менять, так, чтобы работа других сеансов не блокировалась. Другими словами, вместо выполнения: update emp s e t ename = 'King' where empno = 1234;

можно написать: select ename from emp where empno = 1234 FOR UPDATE NOWAIT;

update emp set ename = 'King' where empno = 1234;

За счет использования конструкции FOR UPDATE NOWAIT в операторе SELECT можно заблокировать строку для использования сеансом (так что выполнение UPDATE не будет заблокировано) или будет получено сообщение об ошибке ORA-54 'Resource Busy'. Если при выполнении оператора SELECT сообщений об ошибках не получено, строка уже заблокирована. Однако при выполнении операторов INSERT этот метод неприменим. Нет строки, которую можно было бы выбрать с помощью SELECT и заблокировать, а потому нет и способа предотвратить вставку строки с таким же значением в других сеансах, что приведет к блокированию и потенциально бесконечному ожиданию в текущем сеансе. Вот тут и поможет пакет DBMS_LOCK. Чтобы продемонстрировать, как, я создам таблицу с первичным ключом, предотвращающим одновременную вставку одних и тех же значений двумя (или более) сеансами. Для этой таблицы я задам триггер. Триггер будет использовать функцию DBMS_UTILITY.GET_HASH_VALUE (подробнее о ней см. в разделе, посвященном пакету DBMS UTILITY, далее в этом приложении) для получе Пакет DBMS_LOCK ния по первичному ключу числового хеш-значения в диапазоне от 0 до 1073741823 (диапазон значений идентификаторов блокировок, допускаемых сервером Oracle). B этом примере я задал размер хеш-таблицы равным 1024, т.е. по первичным ключам будет получено одно из 1024 значений идентификаторов блокировок. Затем я использую вызов DBMS_LOCK.REQUEST для выделения исключительной блокировки с этим идентификатором. В каждый момент времени это сможет сделать только один сеанс, поэтому, если другой сеанс попытается вставить запись в таблицу с таким же первичным ключом, его запрос на блокировку завершится неудачно (и будет получено сообщение об ошибке RESOURCE BUSY): tkyte@TKYTE816> create table demo (x int primary key);

Table created. tkyte@TKYTE816> create or replace trigger demo_bifer 2 before insert on demo 3 for each row 4 declare 5 l_lock_id number;

6 resource_busy exception;

7 pragmaexception_init(resource_busy, -54);

8 begin 9 l_lock_id := 10 dbms_utility.get_hash_value(to_char(:new.x), 0, 1024);

11 12 if (dbms_lock.request 13 (id => l_lock_id, 14 lockmode => dbms_lock.x_mode, 15 timeout => 0, 16 release_on_commit => TRUE) = 1) 17 then 18 raise resource_busy;

19 end if;

20 end;

21 / Trigger created. Если в двух отдельных сеансах теперь выполнить: tkyte@TKYTE816> insert into demo values (1);

1 row created. то в первом сеансе оператор выполнится, но во втором будет выдано: tkyte@TKYTE816> insert into demo values (1);

insert into demo values (1) * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified ORA-06512: at "TKYTE.DEMO_BIFER", line 15 ORA-04088: error during execution of trigger 'TKYTE.DEMO_BIFER' если в первом сеансе транзакция будет зафиксирована, то будет выдано сообщение о нарушении требования уникальности.

Приложение А Идея здесь в том, чтобы в триггере брать первичный ключ таблицы и помещать его значение в строку символов. После этого можно использовать функцию DBMS_UTILITY.GET_HASH_VALUE для получения "почти уникального" хеш-значения для строки. Если использовать хеш-таблицу размером не более 1073741823 значений, можно будет заблокировать это значение в исключительном режиме с помощью пакета DBMS_LOCK. Можно также использовать подпрограмму ALLOCATE_UNIQUE пакета DBMS_LOCK, но на это потребуются дополнительные ресурсы. Подпрограмма ALLOCATE_UNIQUE создает уникальный идентификатор блокировки в диапазоне от 1073741824 до 1999999999. Для этого она использует другую таблицу в базе данных и рекурсивную (автономную) транзакцию. Благодаря хешированию используется меньше ресурсов и, кроме того, можно избежать вызова рекурсивных SQL-операторов. После хеширования мы берем полученное значение и с помощью пакета DBMS_LOCK запрашиваем блокировку с соответствующим идентификатором в исключительном режиме с нулевым временем ожидания (если значение кем-то уже заблокировано, происходит немедленный возврат). Если получить блокировку за это время не удалось, возбуждается исключительная ситуация ORA-54 RESOURCE BUSY. B противном случае можно выполнить оператор INSERT, и он не будет заблокирован. Конечно, если в качестве первичного ключа таблицы используется целое число и есть уверенность, что значения ключа не превысят 1 миллиарда, можно его не хешировать, а использовать непосредственно в качестве идентификатора блокировки. Нужно "поиграть" с размером хеш-таблицы (в моем примере — 1024), чтобы избежать сообщений RESOURCE BUSY, связанных с получением одного и того же хешзначения по разным строкам. Размер хеш-таблицы зависит от приложения (точнее, от используемых данных);

на него также влияет количество одновременно выполняемых вставок. Кроме того, владельцу триггера понадобится непосредственно (не через роль) предоставленная привилегия EXECUTE на пакет DBMS_LOCK. Наконец, при вставке большого количества строк таким способом, без фиксации может не хватить ресурсов ENQUEUE_RESOURCES. В случае возникновения такой проблемы (при этом генерируется соответствующее сообщение) необходимо увеличить значение параметра инициализации ENQUEUE_RESOURCES. Можно также добавить в триггер флаг, позволяющий включать и отключать эту проверку. Например, если бы я планировал вставлять сотни/тысячи записей, то не хотел бы выполнять подобную проверку при каждой вставке. Пользовательские блокировки, а также количество первичных ключей с соответствующим хеш-значением, можно получить из представления V$LOCK. Например, если такой триггер был установлен для рассмотренной в предыдущих примерах таблицы DEMO, мы получим: tkyte@TKYTE816> insert into demo values (1);

1 row created. tkyte@TKYTE816> select sid, type, idl 2 from v$lock 3 where sid = (select sid from v$mystat where rownum = 1) 4/ SID TY 8 ТХ ID1 Пакет DBMS LOCK 8 ТМ б UL 30536 tkyte@TKYTE816> begin 2 dbms_output.put_line 3 (dbms_utility.get_hash_value(to_char(l), 0, 1024));

4 end;

5/ 827 PL/SQL procedure successfully completed. Обратите внимание на пользовательскую блокировку UL со значением ID1 827. Оказывается, что 827 — хеш-значение для результата функции TO_CHAR(1), примененной к первичному ключу. Чтобы завершить этот пример, нужно разобраться, что произойдет, если приложение допускает изменение первичного ключа. В идеале первичный ключ лучше не изменять, но некоторые приложения это делают. Надо учитывать последствия того, если один сеанс изменит значение первичного ключа: tkyte@TKYTE816> update demo s e t x = 2 where x = 1;

1 row updated. а другой сеанс попытается вставить строку с измененным значением первичного ключа: tkyte@TKYTE816> INSERT INTO DEMO VALUES (2);

Второй сеанс опять окажется заблокированным. Проблема в том, что не каждый процесс, который может изменить первичный ключ, учитывает измененную схему блокирования. Для решения этой проблемы, связанной с изменением первичного ключа, необходимо изменить событие, вызывающее срабатывание триггера: before i n s e r t OR UPDATE OF X on demo Если созданный триггер срабатывает до вставки данных в столбец X или каких-либо изменений его значения, будет происходить именно то, что требуется (и изменение тоже станет неблокирующим).

Резюме Пакет DBMS_LOCK открывает приложениям доступ к внутреннему механизму блокирования сервера Oracle. Как было продемонстрировано, эту возможность можно использоватьдля реализации специфического метода блокирования, расширяющего стандартные возможности. Мы рассмотрели способы использования этого механизма для обеспечения очередности доступа к общему ресурсу (например, к файлу ОС) и для координации конфликтующих процессов. Мы углубленно изучили использование средств пакета DBMS_LOCK для предотвращения блокирующих вставок. Этот пример показал особенности использования пакета DBMS_LOCK, а также отображение информации о соответствующих блокировках в представлении V$LOCK. В завершение я обратил ваше внимание на важность обеспечения координации действий сеансов, связанных со специфическим методом блокирования, описав, как изменение значения первичного ключа может нарушить работу созданного неблокирующего алгоритма вставок.

Пакет DBMS_LOGMNR Пакеты LogMiner, DBMS_LOGMNR и DBMS_LOGMNR_D, позволяют анализировать файлы журнала повторного выполнения сервера Oracle. Этот анализ может потребоваться в таких случаях: • Необходимо определить, когда и кем таблица была удалена. • Необходимо проверить, какие действия выполнялись с таблицей или набором таблиц, чтобы разобраться, кто и что изменял. Такую проверку можно выполнить "постфактум". Обычно достаточно выполнить команду AUDIT (но ее надо выполнять заранее), и вы узнаете, что кто-то изменил таблицу, а вот что именно было изменено узнать таким образом невозможно. Пакеты LogMiner позволяют определить постфактум лицо, внесшее изменения, и какие именно данные были изменены. • Необходимо "отменить" транзакцию. Для этого надо узнать, что было сделано в этой транзакции, и создать PL/SQL-код для отмены этих действий. • Необходимо получить эмпирические значения количества строк, изменяемых типичной транзакцией. • Необходимо выполнить ретроспективный анализ использования базы данных за определенный период времени. • Необходимо определить, почему сервер вдруг стал генерировать в журнал по 10 Мбайт данных в минуту. Можно ли найти очевидные причины при беглом просмотре журналов?

Пакет DBMS_LOGMNR • Необходимо разобраться, что в действительности происходит "за кадром". Содержимое журналов повторного выполнения показывает, что именно происходит при выполнении вставки в таблицу с триггером, изменяющим другую таблицу. Все результаты транзакции записываются в журнал. Пакеты LogMiner — прекрасное средство изучения этих результатов. Пакеты LogMiner предоставляют средства для решения всех этих и многих других задач. В этом разделе я представлю краткий обзор использования пакетов LogMiner, а затем опишу ряд проблем при его использовании, о которых не сказано в руководстве Supplied PL/SQL Packages Reference, поставляемом корпорацией Oracle. Как и в случае прочих пакетов, рекомендуется прочитать разделы руководства Supplied PL/SQL Packages Reference, посвященные пакетам DBMS_LOGMNR и DBMS_LOGMNR_D, чтобы получить общее представление о функциях и процедурах, которые они содержат, и о принципах использования этих пакетов. Далее в разделе "Опции и использование" представлен обзор соответствующих процедур и их входных данных. Пакеты LogMiner лучше всего работают с архивными файлами журнала повторного выполнения, хотя их можно использовать и для анализа неактивных оперативных файлов журнала повторного выполнения. Попытка анализа активного оперативного файла журнала повторного выполнения может привести к выдаче сообщения об ошибке или дать некорректные результаты, поскольку файл журнала повторного выполнения содержит данные старых и новых транзакций. Интересно, что с помощью LogMiner можно анализировать файл журнала, первоначально созданный в другой базе данных. Даже версии серверов при этом могут не совпадать (архивные файлы версии 8.0 можно анализировать на сервере версии 8.1). Можно перенести архивный файл журнала повторного выполнения в другую систему и анализировать его там. Это весьма удобно в случае проверки или ретроспективного анализа тенденций использования, не влияющей на работу системы. Для этого, однако, надо использовать сервер на той же аппаратной платформе (т.е. обеспечить тот же порядок байтов, размер слова и т.п.). Желательно обеспечить такой же размер блоков базы данных, как в исходной (размер блока в базе данных, где выполняется анализ, не должен быть меньше, чем в базе данных, где журнал повторного выполнения сгенерирован), и совпадение кодовых страниц. Процесс использования пакетов LogMiner состоит из двух этапов. На первом — создается словарь данных для работы пакетов LogMiner. Именно это и позволяет анализировать файл журнала повторного выполнения не в той базе данных, где он был сгенерирован (пакеты LogMiner не используют существующий словарь данных). Используется словарь данных, экспортированный во внешний файл с помощью пакета DBMS_LOGMNR_D. Пакеты LogMiner можно использовать и без этого словаря данных, но разобраться в полученных результатах при этом практически невозможно. Формат представления этих результатов мы рассмотрим несколько позже. На втором этапе импортируются файлы журнала повторного выполнения, и запускается LogMiner. После запуска основного пакета LogMiner можно просматривать содержимое файлов журнала повторного выполнения с помощью SQL-операторов. С пакетами LogMiner связано четыре представления V$. Основное представление — V$LOGMNR_CONTENTS. Именно оно будет использоваться для анализа содержимого загруженных файлов журнала повторного выполнения. Более детально это представ • Приложение А ление мы рассмотрим в примере, а в конце раздела представлена таблица с описанием его столбцов. Остальные три представления описаны ниже. V$LOGMNR_DICTIONARY. Это представление содержит информацию о загруженном файле словаря. Этот словарь был создан на первом этапе. Чтобы разобраться в содержимом файла журнала повторного выполнения, необходим файл словаря, задающий имя объекта с данным идентификатором, имена и типы данных столбцов таблиц и т.п. Это представление содержит не больше одной строки, описывающей текущий загруженный словарь.

• V$LOGMNR_LOGS. Это представление содержит информацию о файлах журнала повторного выполнения, которые пользователь загрузил в систему с помощью LogMiner. Содержимое файлов журнала повторного выполнения можно найти в представлении V$LOGMNR_CONTENTS. Там же вы найдете характеристики самих файлов: имя файла журнала повторного выполнения, имя базы данных, в которой он сгенерирован, номера системных изменений (SCNs — system change numbers), содержащихся в нем, и т.д. В представлении содержится по одной строке для каждого анализируемого файла. • V$LOGMNR_PARAMETERS. Это представление содержит параметры, переданные LogMiner при запуске. После вызова подпрограммы запуска LogMiner в нем будет одна строка. Важно отметить, что, поскольку пакеты LogMiner выделяют память в области PGA, средства LogMiner нельзя использовать в среде MTS. Дело в том, что в среде MTS каждое обращение к базе данных будет обрабатываться другим разделяемым сервером (процессом или потоком). Данные, загруженные первым процессом (первым разделяемым сервером) не доступны для второго процесса (второго разделяемого сервера). Для работы пакетов LogMiner необходимо подключиться к выделенному серверу. Кроме того, результат доступен в одном сеансе и только в процессе его работы. Если необходим дальнейший анализ, надо либо загрузить информацию повторно, либо сохранить ее в постоянной таблице, например, с помощью оператора CREATE TABLE AS SELECT. При анализе больших объемов данных размещение их в обычной таблице с помощью операторов CREATE TABLE AS SELECT или INSERT INTO имеет еще больше смысла. В дальнейшем эту информацию можно проиндексировать, тогда как представление V$LOGMNR_CONTENTS всегда просматривается полностью, что требует очень много ресурсов.

Обзор После обзора средств LogMiner мы рассмотрим назначение входных параметров стандартных пакетов LogMiner. Затем я расскажу вам, как с помощью LogMiner определить, когда в базе данных произошло определенное действие. Далее будет рассмотрено влияние пакетов LogMiner на использование памяти сеансами, а также кэширование пакетами файлов журнала повторного выполнения. Наконец, я опишу ограничения, связанные с использованием пакетов LogMiner, не упоминающиеся в документации.

Пакет DBMS_LOGMNR Этап 1: создание словаря данных Чтобы средства LogMiner могли сопоставить внутренним идентификаторам объектов и столбцов соответствующие имена, необходим словарь данных. Имеющийся в базе данных словарь при этом не используется. Словарь данных должен загружаться из внешнего файла. Это необходимо для того, чтобы журналы повторного выполнения можно было анализировать в другой базе данных. Кроме того, текущий словарь данных в базе может поддерживать уже не все объекты, находившиеся в базе данных в момент генерации файла журнала повторного выполнения, вот почему словарь данных необходимо импортировать. Чтобы понять назначение файла словаря данных, давайте рассмотрим результата работы LogMiner, когда словарь данных не загружен. Для этого загрузим архивный файл журнала повторного выполнения и запустим LogMiner. Затем выполним запрос к представлению V$LOGMNR_CONTENTS, чтобы определить его содержимое: tkyte@TKYTE816> begin 2 sys.dbms_logmnr.add_logfile 3 ('C:\oracle\oradata\tkyte816\archive\TKYTE816T001S01263.ARC', 4 sys.dbms_logmnr.NEW);

5 end;

6/ PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 sys.dbms_logmnr.start_logmnr;

3 end;

4/ PL/SQL procedure successfully completed. tkyte@TKYTE816> column sql_redo format a30 tkyte@TKYTE816> column sql_undo format a30 tkyte@TKYTE816> select scn, sql_redo, sql_undo from v$logmnr_contents 2/ SCN SQL_REDO SQL_UNDO 6.4430E+12 6.4430E+12 set transaction read write;

6.4430E+12 update UNKNOWN.Objn:30551 set update UNKNOWN.Objn:30551 set Col[2] = HEXTORAW('787878') wh Col[2] = HEXTORAW('534d495448' ere ROWID = 'AAAHdXAAGAAAAJKAA ) where ROWID = 'AAAHdXAAGAAAA A';

JKAAA';

6.4430E+12 6.4430E+12 commit;

tkyte@TKYTE816> s e l e c t utl_raw.cast_to_varchar2(hextoraw('787878')) from d u a l ;

UTL_RAW.CAST_TO_VaRCHAR2(HEXTORAW('787878')) XXX Приложение А tkyte@TKYTE816> select utl_raw.cast_to_varchar2(hextoraw('534d495448')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('534D495448•)) SMITH Читать этот результат практически невозможно. Мы знаем, что изменен столбец 2 объекта с идентификатором 30551. Более того, можно получить значение HEXTORAW('787878') в виде строки символов. Можно обратиться к словарю данных и определить, какой объект имеет идентификатор 30551: tkyte@TKYTE816> select object_name 2 from all_objects 3 where data_object_id = 30551;

OBJECT_NAME ЕМР но сделать это можно только в той же базе данных, в которой был сгенерирован журнал повторного выполнения, и только в том случае, если этот объект еще существует. Далее можно выполнить команду DESCRIBE ЕМР и определить, что столбец 2 — это ENAME. Поэтому в столбце SQL_REDO пакет LogMiner поместил значение UPDATE EMP SET ENAME = 'XXX' WHERE ROWID =.... К счастью, подобные запутанные преобразования не придется выполнять при каждом анализе журнала. Мы убедимся, что, создав и загрузив словарь, можно получить намного более понятные результаты. Следующий пример показывает, каких результатов можно ожидать, создав файл словаря для пакетов LogMiner, a затем загрузив его. Начнем с создания файла словаря. Создать его весьма просто. Для этого должны быть выполнены следующие требования. • Конфигурация параметров инициализации позволяет создавать с помощью пакета UTL_FlLE файлы хотя бы в одном каталоге. Подробнее соответствующая настройка описана в разделе, посвященном пакету UTL_FILE. Пакет DBMS_LOGMNR_D, с помощью которого создается файл словаря данных, для выполнения ввода-вывода использует средства пакета UTL_FILE. • Схема, в которой будет вызываться пакет DBMS_LOGMNR_D, имеет привилегию EXECUTE ON SYS.DBMS_LOGMNR_D, или ей предоставлена роль с привилегий выполнения этого пакета. По умолчанию роль EXECUTE_CATALOG_ROLE имеет привилегию для выполнения этого пакета. После настройки пакета UTL_FILE и получения привилегии EXECUTE ON DBMS_LOGMNR_D создание файла словаря представляет собой тривиальную задачу. Надо вызвать всего одну подпрограмму пакета DBMS_LOGMNR_D — процедуру BUILD. Достаточно выполнить примерно следующее: tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> begin 2 sys.dbms_logmnr_d.build('miner_dictionary.dat', 3 'c:\temp');

Пакет DBMS_LOGMNR 4 end;

5/ LogMnr Dictionary Procedure started LogMnr Dictionary File Opened TABLE: OBJ$ recorded in LogMnr Dictionary File TABLE: TAB$ recorded in LogMnr Dictionary File TABLE: COL$ recorded in LogMnr Dictionary File TABLE: SEG$ recorded in LogMnr Dictionary File TABLE: UNDO$ recorded in LogMnr Dictionary File TABLE: UGROUP$ recorded in LogMnr Dictionary File TABLE: TS$ recorded in LogMnr Dictionary File TABLE: CLU$ recorded in LogMnr Dictionary File TABLE: IND$ recorded in LogMnr Dictionary File TABLE: ICOL$ recorded in LogMnr Dictionary File TABLE: LOB$ recorded in LogMnr Dictionary File TABLE: USER$ recorded in LogMnr Dictionary File TABLE: FILE$ recorded in LogMnr Dictionary File TABLE: PARTOBJ$ recorded in LogMnr Dictionary File TABLE: PARTCOL$ recorded in LogMnr Dictionary File TABLE: TABPART$ recorded in LogMnr Dictionary File TABLE: INDPART$ recorded in LogMnr Dictionary File TABLE: SUBPARTCOL$ recorded in LogMnr Dictionary File TABLE: TABSUBPART$ recorded in LogMnr Dictionary File TABLE: INDSUBPART$ recorded in LogMnr Dictionary File TABLE: TABCOMPART$ recorded in LogMnr Dictionary File TABLE: INDCOMPART$ recorded in LogMnr Dictionary File Procedure executed successfully — LogMnr Dictionary Created PL/SQL procedure successfully completed. Перед вызовом процедуры BUILD пакета DBMS_LOGMNR_D рекомендуется выполнять команду SET SERVEROUTPUT ON — это обеспечит выдачу информационных сообщений пакета DBMS_LOGMNR_D. Они помогут выяснить причины ошибки при выполнении DBMS_LOGMNR_D.BUILD. Выполненная выше команда создала файл C:\TEMP\MINER_DICTIONARY.DAT. Это обычный текстовый файл, который можно просматривать в текстовом редакторе. Файл содержит SQL-подобные операторы, которые анализируются и выполняются процедурой запуска основного пакета LogMiner. Теперь, при наличии файла словаря, можно посмотреть, какая информация содержится в представлении V$LOGMNR_CONTENTS: tkyte@TKYTE816> begin 2 sys.dbms_logmnr.add_logfile 3 ('C:\oracle\oradata\tkyte816\archive\TKYTE816T001S01263.ARC1, 4 sys.dbms_logmnr.NEW);

5 end;

6/ PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 sys.dbms_logmnr.start_logmnr 3 (dictFileName=> 'c:\temp\miner_dictionary.dat');

4 end;

Приложение А / PL/SQL procedure successfully completed. tkyte@TKYTE816> column sql_redo format a30 tkyte@TKYTE816> column sql_undo format a30 tkyte@TKYTE816> select scn, sql_redo, sql_undo from v$logmnr_contents 2/ SCN SQL_REDO SQL_UNDO 6.4430E+12 6.4430E+12 set transaction read write;

6.4430E+12 update TKYTE.EMP set ENAME = ' update TKYTE.EMP set ENAME = ' xxx' where ROWID = 'AAAHdXAAGA SMITH' where ROWID = 'AAAHdXAA AAAJKAAA';

GAAAAJKAAA';

6.4430E+12 6.4430E+12 commit;

Теперь все гораздо понятнее: можно прочитать SQL-операторы, сгенерированные пакетом LogMiner, и повторяющие (или отменяющие) изучаемую транзакцию. Теперь можно переходить ко второму этапу — использованию средств LogMiner.

Этап 2: использование средств LogMiner Используем только что сгенерированный файл словаря для анализа содержимого архивных файлов журнала повторного выполнения. Перед загрузкой журнала повторного выполнения сгенерируем такой файл, все транзакции в котором будут известны. Это упростит интерпретацию результатов в первый раз. Мы сможем сопоставлять содержимое представления V$LOGMNR_CONTENTS с только что выполненными транзакциями. Для этого важно организовать тестовую базу данных, с которой будет работать только один пользователь. Это позволит искусственно ограничить содержимое журнала повторного выполнения. Для этой базы данных также понадобится привилегия ALTER SYSTEM, чтобы можно было принудительно вызвать архивирование файла журнала. Наконец, все гораздо проще, если база данных работает в режиме автоматического архивирования журналов. При этом очень легко найти соответствующий файл журнала повторного выполнения (это будет только что заархивированный файл — ниже я покажу, как его найти). При использовании базы данных в режиме NOARCHWELOGMODE, вам придется найти активный журнал и определить, какой файл журнала был активным непосредственно перед ним. Итак, чтобы сгенерировать транзакцию-образец, выполним: tkyte@TKYTE816> alter system archive log current;

System altered. tkyte@TKYTE816> update emp set ename = lower(ename);

14 rows updated. tkyte@TKYTE816> update dept set dname = lower(dname);

4 rows updated.

Пакет DBMS_LOGMNR tkyte@TKYTE816> commit;

Commit complete. tkyte@TKYTE816> alter system archive log current;

System altered. tkyte@TKYTE816> column name format a80 tkyte@TKYTE816> select name 2 from v$archived_log 3 where completion_time = (select max(completion_time) 4 from v$archived_log) 5/ NAME C:\ORACLE\ORADATA\TKYTE816\ARCHrVE\TKYTE816T001S01267.ARC Теперь, с учетом того, что я — единственный пользователь, изменяющий базу данных, в сгенерированном архивном журнале окажутся только два выполненных изменения. Последний запрос к представлению V$ARCHIVED_LOG возвращает имя архивного файла журнала повторного выполнения, который необходимо анализировать. Его можно загрузить в LogMiner и анализировать с помощью приведенных ниже PL/SQL-блоков. Они добавят последний архивный файл журнала повторного выполнения к списку обрабатываемых, а затем запустят LogMiner: tkyte@TKYTE816> declare 2 l_name v$archived_log.name%type;

3 begin 4 5 select name into l_name 6 from v$archived_log 7 where completion_time = (select max(completion_time) 8 from v$archived_log);

9 10 sys.dbms_logmnr.add_logfile(l_name, sys.dbms_logmnr.NEW);

11 end;

12 / PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 sys.dbms_logmnr.start_logmnr 3 (dictFileName => 'c:\temp\miner_dictionary.dat');

4 end;

5/ PL/SQL procedure successfully completed. Первый вызов процедуры, DBMS_LOGMNR.ADD_LOGFILE, загрузил архивный файл журнала повторного выполнения в LogMiner. Я передал имя архивного файла журнала повторного выполнения и опцию DBMS_LOGMNR.NEW. Поскольку этот файл добавляется первым, надо указывать опцию DBMS_LOGMNR.NEW. Поддерживаются также опции ADDFILE для добавления еще одного файла журнала в существующий Приложение А список файлов, а также REMOVEFILE — для удаления файла из списка рассматриваемых. После загрузки нужных файлов журнала можно вызывать процедуру DBMS_LOGMNR.START_LOGMNR, передавая ей имя созданного файла словаря. При вызове START_LOGMNR передается минимум информации — только имя файла словаря. Другие опции процедуры START_LOGMNR мы рассмотрим в разделе "Опции и использование". Теперь, после загрузки файла журнала и запуска LogMiner, все готово для просмотра содержимого представления V$LOGMNR_CONTENTS. Представление V3LOGMNR_CONTENTS содержит большое количество информации;

для начала рассмотрим только небольшую ее часть. В частности, выберем значения столбцов SCN, SQL_REDO и SQL_UNDO. Если вы этого еще не знаете, SCN (порядковый номер системного изменения) — это простой механизм отсчета времени, который сервер Oracle используетдля упорядочения транзакций и восстановления базы данных после сбоев. Эти номера также используются для обеспечения согласованности по чтению и при обработке контрольных точек в базе данных. SCN можно рассматривать как счетчик: при фиксации каждой транзакции значение SCN увеличивается на единицу. Вот запрос из предыдущего примера, в котором имена в таблицах ЕМР и DEPT переводятся в нижний регистр: tkyte@TKYTE816> column sql_redo format a20 word_wrapped tkyte@TKYTE816> column sql_undo format a20 word_wrapped tkyte@TKYTE816> select scn, sql_redo, sql_undo from v$logmnr_contents 2/ SCN SQL_REDO 6.4430E+12 set transaction read write;

6.4430E+12 update TKYTE.EMP set update TKYTE.EMP set ENAME = 'smith' ENAME - 'SMITH' where ROWID = where ROWID = 'AAAHdYAAGAAAAJKAAA' 'AAAHdYAAGAAAAJKAAA' 6.4430E+12 6.4430E+12 update TKYTE.EMP set update TKYTE.EMP set ENAME = 'allen' ENAME = 'ALLEN' where ROWID = where ROWID = 'AAAHdYAAGAAAAJKAAB' 'AAAHdYAAGAAAAJKAAB'...(несколько аналогичных строк выброшено)... 6.4430E+12 update TKYTE.DEPT update TKYTE.DEPT set DNAME = 'sales' set DNAME = 'SALES' where ROWID = where ROWID = 'AAAHdZAAGAAAAKKAAC' 'AAAHdZAAGAAAAKKAAC' ;

;

SQL_UNDO 6.4430E+12 update TKYTE.DEPT set DNAME = update TKYTE.DEPT set DNAME = Пакет DBMS_LOGMNR 'operations' where 'OPERATIONS' where ROWID = ROWID = 'AAAHdZAAGAAAAKKAAD' 'AAAHdZAAGAAAAKKAAD' ;

;

6.4430E+12 commit;

22 rows selected. Как видите, SQL-операторы сгенерировали в журнале повторного выполнения не две строки, а намного больше. Журнал повторного выполнения содержит измененные биты и байты, а не SQL-операторы. Поэтому многострочный оператор UPDATE EMP SET ENAME = LOWER(ENAME) представляется средствами LogMiner в виде набора однострочных изменений. В настоящее время LogMiner не позволяет получить реально выполненные SQL-операторы. Можно создать только делающие то же самое SQL-операторы, но в виде набора отдельных операторов. В этом примере мы пойдем на шаг дальше. В представлении V$LOGMNR_CONTENTS есть столбцы-заместители ("placeholder" columns). Столбцы-заместители позволяют найти изменения, выполненные в пяти (но не более) столбцах таблицы. Столбцы-заместители позволяют узнать имя измененного столбца, а также значение столбца до и после изменения. Поскольку эти столбцы выделены из текста SQL-операторов, очень легко найти транзакцию, изменившую, скажем, значение в столбце ENAME (в столбце-заместителе имени будет значение ENAME) с KING (в столбце предварительного значения будет KING) на king. Чтобы продемонстрировать это, выполним еще один небольшой пример с оператором UPDATE и создадим файл сопоставления столбцов (column mapping file). Файл сопоставления столбцов (будем сокращенно называть его файл colmap) позволяет указать средствам LogMiner, какие столбцы в таблице вас интересуют. Можно сопоставитьдо пяти столбцов в каждой таблице со столбцами-заместителями. Файл colmap имеет следующий формат:

c o l m a p = TKYTE DEPT ( 1, DEPTNO, 2, DNAME, 3, LOC);

colmap = TKYTE EMP ( 1, EMPNO, 2, ENAME, 3, JOB, 4, MGR, 5, HIREDATE);

При просмотре строки в таблице DEPT столбцу DEPT DEPTNO будет сопоставлен первый столбец-заместитель. А при просмотре строки в таблице ЕМР этому столбцузаместителю будет сопоставлен столбец ЕМР EMPNO. Файл сопоставления столбцов в общем случае состоит из строк следующей структуры (полужирным выделены константы, представляет обязательный пробел) c o l m a p < s p > - < s p > В Л А Д Е Л Е Ц < s p > И М Я _ Т А Б Л И Ц Ы < s p > ( 1, ИМЯ_СТОЛБЦА [, < s p > 2, < s p > ->ИМЯ СТОЛБЦА]... ) ;

Регистр символов везде имеет значение: ВЛАДЕЛЬЦА надо задавать в верхнем регистре, имя таблицы — с соблюдением регистра (обычно — в верхнем регистре, если только при создании объекта не использовались идентификаторы в кавычках). Пробелы указывать тоже обязательно. Для того чтобы упростить использование файла сопоставления столбцов я использую следующий сценарий: set linesize 500 set trimspool on set feedback off Приложение А set heading off set embedded on spool logmnr.opt select 'colmap = ' || user || ' ' || table_name || ' (' || max(decode(column_id, 1, column_id, null)) || max(decode(column_id, 1, ', '||column_name, null)) || max(decode(column_id, 2, ', '||column_id, null)) || max(decode(column_id, 2, ', '||column_name, null)) || max(decode(column_id, 3, ', '||column_id, null)) || max(decode(column_id, 3, ', '||column_name, null)) || max(decode(column_id, 4, ', '||column_id, null)) || max(decode(column_id, 4, ', '||column_name, null)) || max(decode(column_id, 5, ', '||column_id, null)) || max(decode(column_id, 5, ', '||column_name, null)) || ' ) ;

' colmap from user_tab_columns group by user, table_name / spool off в SQL*Plus для автоматической генерации файла logmnr.opt. Например, если выполнить этот сценарий в схеме, где имеются только таблицы ЕМР и DEPT, аналогичные тем, что принадлежат пользователю SCOTT/TIGER, вы получите:

tkyte@TKYTE816> @ c o l m a p colmap = TKYTE DEPT ( 1, DEPTNO, 2, DNAME, 3, LOC);

c o l m a p = TKYTE EMP ( 1, EMPNO, 2, ENAME, 3, JOB, 4, MGR, 5, HIREDATE);

Я всегда сопоставляю первых пять столбцов таблицы. Если вы хотите использовать другой набор пяти столбцов, отредактируйте полученный при выполнении этого сценария файл logmnr.opt, изменив имена столбцов. Например, в таблице ЕМР есть еще три столбца, не представленные в полученном файле colmap — SAL, COMM и DEPTNO. Если необходимо просматривать изменения столбца SAL, a не JOB, файл colmap должен выглядеть так:

tkyte@TKYTE816> @ c o l m a p c o l m a p = TKYTE DEPT ( 1, DEPTNO, 2, DNAME, 3, LOC);

c o l m a p = TKYTE EMP ( 1, EMPNO, 2, ENAME, 3, SAL, 4, MGR, 5, HIREDATE);

Помимо использования соответствующего регистра символов и количества пробелов при работе с файлом colmap важно также следующее: • Файл должен называться logmnr.opt. Другое имя использовать нельзя. • Этот файл должен быть в том же каталоге, что и файл словаря. • Файл colmap можно использовать только вместе с файлом словаря. Итак, мы сейчас изменим все столбцы в таблице DEPT. Я использую четыре различных оператора UPDATE, каждый из которых будет изменять другую строку и набор столбцов. Это позволит увидеть результат сопоставления столбцов-заместителей: tkyte@TKYTE816> alter system archive log current;

tkyte@TKYTE816> update dept set deptno = Пакет DBMS_LOGMNR 2 where deptno = 40 / tkyte@TKYTE816> update dept set dname = initcap(dname) 2 where deptno = 10 3/ tkyte@TKYTE816> update dept set loc = initcap(loc) 2 where deptno = 20 3/ tkyte@TKYTE816> update dept set dname = initcap(dname), 2 loc = initcap(loc) 3 where deptno = 30 4/ tkyte@TKYTE816> commit;

tkyte@TKYTE816> a l t e r system archive log c u r r e n t ;

Теперь можно найти изменения в каждом из столбцов, загрузив вновь сгенерированный архивный файл журнала повторного выполнения и запустив LogMiner с опцией USE_COLMAP. Обратите внимание, что я сгенерировал файл logmnr.opt с помощью представленного ранее сценария и поместил этот файл в тот же каталог, где находится словарь данных: tkyte@TKYTE816> declare 2 l_name v$archived_log.name%type;

3 begin 4 5 select name into l_name 6 from v$archived_log 7 where completion_time = (select max(completion_time) 8 fromv$archived_log);

9 10 sys.dbms_logmnr.add_logfile(l_name, sys.dbms_logmnr.NEW);

11 end;

12 / PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 sys.dbms_logmnr.start_logmnr 3 (dictFileName => 'c:\temp\miner_dictionary.dat', 4 options => sys.dbms_logmnr.USE_COLMAP);

5 end;

6/ PL/SQL procedure successfully completed. tkyte@TKYTE816> select scn, phl_name, phl_undo, phl_redo, 2 ph2_name, ph2_undo, ph2_redo, 3 ph3_name, ph3_undo, ph3_redo 4 from v$logmnr_contents 5 where seg_name = 'DEPT' 6/ Приложение А SCN PHl_NA PH1 PH1 PH2_N PH2_0NDO PH2_REDO PH3 PH3_UNDO PH3_RED 6.4430E+12 DEPTNO 40 11 6.4430E+12 DNAME accounting Accounting 6.4430E+12 LOC DALLAS Dallas 6.4430E+12 DNAME sales Sales LOC CHICAGO Chicago Итак, этот результат ясно показывает (в первой строке, например), что в столбце DEPTNO значение 40 (PH1) было изменено на 11. Это понятно, поскольку мы выполняли SET DEPTNO = 11 WHERE DEPTNO = 40. Обратите внимание, что остальные столбцы в перовой строке — пустые. Причина в том, что сервер Oracle записывает только измененные байты;

для этой строки нет предварительного и окончательного образов столбцов DNAME и LOC. Вторая строка показывает изменение значения в столбце DNAME с accounting на Accounting и отсутствие изменений в столбцах DEPTNO или LOC, поскольку они не были затронуты. Последняя строка показывает, что при изменении двух столбцов одним оператором UPDATE они будут представлены в столбцахзаместителях. Как видите, использование столбцов-заместителей может пригодиться при поиске конкретной транзакции в большем объеме данных повторного выполнения. Если известно, что транзакция изменила таблицу X, поменяв в столбце Y значение с а на b, найти ее очень легко.

Опции и использование Функциональные возможности LogMiner реализуются двумя пакетами DBMS_LOGMNR и DBMS_LOGMNR_D. Пакет DBMS_LOGMNR_D (_D в названии обозначает "словарь" — "dictionary") содержит всего одну процедуру, BUILD. Она применяется для создания словаря данных, используемого пакетом DBMS_LOGMNR при загрузке файла журнала повторного выполнения. Он позволяет сопоставить идентификаторам объектов имена таблиц, определить имена и типы данных столбцов по порядковому номеру и т.д. Использовать процедуру DBMS_LOGMNR_D.BUILD очень просто. Она имеет два параметра: • • DICTIONARY_FILENAME. Имя файла словаря, который необходимо создать. В наших примерах использовался файл miner_dictionary.dat. DICTIONARY_LOCATION. Каталог, в котором этот файл будет создан. Процедура использует для создания файла средства пакета UTL_FILE, так что каталог должен быть перечислен среди допустимых каталогов, задаваемых параметром инициализации utl_file_dir. Подробнее о конфигурировании пакета UTL_FILE cм. в соответствующем разделе в конце приложения А.

Вот и все, что нужно знать о процедуре BUILD. Оба параметра указывать обязательно. Если при вызове этой процедуры получено сообщение об ошибке, подобное следующему: tkyte@TKYTE816> exec sys.dbms_logmnr_d.build('x.dat', ' c : \ n o t _ v a l i d \ ' ) ;

BEGIN sys.dbms_lognmr_d.build('x.dat', ' c : \ n o t _ v a l i d \ ' ) ;

END;

Пакет DBMS_LOGMNR * ERROR at line 1: ORA-01309: specified dictionary file cannot be opened ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793 ORA-06512: at line 1 значит, указанный каталог не указан в параметре инициализации utl_file_dir. Пакет DBMS_LOGMNR состоит из трех процедур. • ADD_LOGFILE. Зарегистрировать набор файлов журнала для анализа. • START_LOGMNR. Заполнить данными представление V$LOGMNR_CONTENTS.

• END_LOGMNR. Освободить все ресурсы, выделенные при работе LogMiner. Эта процедура вызывается для корректного освобождения ресурсов перед завершением сеанса или при окончании работы с пакетами LogMiner. Процедура ADD_LOGFILE, как было сказано ранее, вызывается еще до запуска LogMiner. Она создает список файлов журнала, которые будут обрабатываться при выполнении процедуры START_LOGMNR для заполнения представления VSLOGMNR_CONTENTS. Процедура ADD_LOGFILE принимает следующие параметры: • LOGFILENAME. Полное имя файла архивного журнала повторного выполнения, который необходимо проанализировать. • OPTIONS. Задает, добавлять указанный файл или удалять. В качестве значения задаются следующие константы пакета DBMS_LOGMNR: DBMS_LOGMNR.NEW. Начать новый список. Если список уже существует, он очищается. DBMS_LOGMNR.ADD. Добавить файл в уже существующий или пустой список. DBMS_LOGMNR.REMOVEFILE. Удалить файл из списка. Если необходимо проанализировать последние два архивных файла журнала повторного выполнения, процедура ADD_LOGFILE вызывается дважды. Например: tkyte@TKYTE816> declare 2 l_cnt number default 0;

3 begin 4 for x in (select name 5 from v$archived_log 6 order by completion_time desc) 7 loop 8 l_cnt := l_cnt+l;

9 exit when (l_cnt > 2 ) ;

10 11 sys.dbms_logmnr.add_logfile(x.name);

12 end loop;

13 14 sys.dbms_logmnr.start_logmnr 15 (dictFileName => 'c:\temp\miner_dictionary.dat'.

16 17 Приложение А options => sys.dhms_logmnr.USE_COLMAP);

end;

/ PL/SQL procedure successfully completed.

В одном сеансе после запуска LogMiner можно вызывать процедуру ADD_LOGFILE для добавления дополнительных файлов журнала, удаления тех из них, которые больше не представляют интереса, или (если указана опция DBMS_LOGMNR.NEW) для сброса списка файлов журнала так, чтобы он включал только один указанный новый файл. При вызове DBMS_LOGMNR.START_LOGMNR после изменения списка файлов содержимое представления V$LOGMNR_CONTENTS, по сути, сбрасывается и создается заново на основе информации в журнальных файлах, входящих в список. Процедура DBMS_LOGMNR.START_LOGMNR принимает много параметров. В рассмотренных ранее примерах мы использовали только два из шести имеющихся. Мы задавали имя файла словаря и опции (чтобы указать, что необходимо использовать файл colmap). В общем случае поддерживаются следующие параметры: • STARTSCN и ENDSCN. Если точно известен диапазон интересующих номеров системных изменений, можно поместить в представление V$LOGMNR_CONTENTS только соответствующие строки. Это пригодится после загрузки всего файла журнала и определения максимального и минимального номера системных изменений, которые представляют интерес. Можно перезапустить LogMiner, указав этот диапазон, чтобы уменьшить объем данных в представлении V$LOGMNR_CONTENTS. По умолчанию эти параметры имеют значение 0 и не используются. • STARTTIME и ENDTIME. Вместо указания диапазона SCN можно задать отрезок времени. Только записи журнала, попадающие в указанный отрезок времени, окажутся в представлении V$LOGMNR_CONTENTS. Эти значения игнорируются, если указаны значения STARTSCN и ENDSCN. По умолчанию используется отрезок времени с 1 января 1988 года по 1 января 2988 года. • DICTFILENAME. Полное имя файла словаря, созданного процедурой DBMS_LOGMNR_D.BUILD. • OPTIONS. B настоящее время поддерживается только одна опция процедуры DBMS_LOGMNR.START_LOGMNR - опция DBMS_LOGMNR.USE_COLMAP. Она задает поиск файла logmnr.opt в том же каталоге, что и файл DICTFILENAME. Важно помнить, что файл colmap может иметь только имя logmnr.opt и должен находиться в том же каталоге, что и файл словаря. Последняя процедура в пакете DBMS_LOGMNR-DBMS_LOGMNR.END_LOGMNR Она завершает сеанс LogMiner и очищает представление V$LOGMNR_CONTENTS. После вызова DBMS_LOGMNR.END_LOGMNR любые попытки обратиться к этому представлению дадут следующий результат:

tkyte@TKYTE816> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed. tkyte@TKYTE816> select count(*) from v$logmnr_contents;

select count(*) from v$logmnr_contents Пакет DBMS_LOGMNR * ERROR at line 1: ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents Определение с помощью LogMiner, когда...

Это наиболее типичный вариант использования пакетов LogMiner. Кто-то удалил таблицу. Надо восстановить ее или выяснить, кто это сделал. Другой пример: изменены данные в важной таблице, виновник не признается. Это произошло при отключенной проверке, но база данных работала в режиме архивирования журналов, и все резервные копии доступны. Хотелось бы восстановить данные из резервной копии до момента непосредственно перед определенным изменением (например, перед выполнением DROP TABLE). Можно восстановить таблицу, прекратив восстановление в нужный момент (чтобы таблица не была удалена снова), экспортировать эту таблицу в восстановленной базе данных, а затем импортировать в текущей. Это позволит восстановить таблицу и оставить в силе остальные изменения. Для этого необходимо знать либо точное время, либо значение SCN для оператора DROP TABLE. Поскольку часы у всех обычно показывают время неточно, а пользователи паникуют, они могут предоставить неверную информацию. Можно загрузить архивные файлы журнала за тот период, когда был выполнен оператор DROP TABLE, и найти точное значение SCN, до которого должно выполняться восстановление. Рассмотрим еще один небольшой пример, показывающий, какие операторы может выдать LogMiner при удалении таблицы. Я использую локально управляемые табличные пространства, так что, если вы используете табличные пространства, управляемые по словарю, у вас может получиться больше SQL-операторов, чем показано далее. Появление дополнительных SQL-операторов в случае управляемых по словарю табличных пространств связано с возвратом экстентов системе и освобождению выделенного таблице пространства. Итак, переходим к удалению таблицы: tkyte@TKYTE816> alter system archive log current;

System altered. tkyte@TKYTE816> drop table dept;

Table dropped. tkyte@TKYTE816> alter system archive log current;

System altered. Теперь необходимо найти значение в столбце SQL_REDO, представляющее удаление таблицы (оператор DROP TABLE). Если помните, LogMiner выдает неточный список выполненных SQL-операторов. Выдаются эквивалентные по действию SQL-операторы. Оператор DROP TABLE в результатах работы LogMiner отсутствует: мы увидим только изменения в словаре данных. В частности, нас интересует оператор DELETE, примененный к таблице SYS.OBJ$ — базовой для всех объектов. При удалении таблицы необходимо удалить соответствующую строку из таблицы SYS.OBJ$. K счастью, при создании значения столбца SQL_REDO для оператора DELETE LogMiner включает в Приложение А него значения столбцов и идентификатор соответствующей строки. Это можно использовать для поиска оператора DELETE, удаляющего строку для DEPT из таблицы OBJ$, Вот как это делается: tkyte@TKYTE816> declare 2 l_name v$archived_log.name%type;

3 begin 4 select name into l_name 5 from v$archived_log 6 where completion_time = (select max(completion_time) 7 from v$archived_log) ;

8 9 sys.dbms_logmnr.add_logfile(l_name, sys.dbms_logmnr.NEW);

10 end;

11 / 12 PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 sys.dbms_logmnr.start_logmnr 3 (dictFileName => 'c:\temp\miner_dictionary.dat', 4 options => sys.dbms_logmnr.USE_COLMAP);

5 end;

6/ PL/SQL procedure successfully completed. tkyte@TKYTE816> select scn, sql_redo 2 from v$logmnr_contents 3 where sql_redo like 'delete from SYS.OBJ$ %''DEPT''%' 4/ SCN SQL_REDO 6442991097246 delete from SYS.OBJ$ where OBJ# = 30553 and DATAOBJ# = 30553 and OWNER# = 337 an d NAME = 'DEPT' and NAMESPACE = 1 and SU BNAME IS NULL and TYPE# = 2 and CTIME = TO_DATE('29-APR-2001 12:32:11', 'DD-MONYYYY HH24:MI:SS1) and MTIME = TO_DATE('2 9-APR-2001 12:32:11', 'DD-MON-YYYY HH24: MI:SS') and STIME = TO_DATE('29-APR-2001 12:32:11', 'DE-MON-YYYY HH24:MI:SS') an d STATUS = 1 and REMOTEOWNER IS NULL and LINKNAME IS NULL and FLAGS = 0 and OID$ IS NULL and SPARE1 = 6 and ROWID = 'AAA AASAABAAAFz3AAZ';

Вот и все, что нужно. Теперь, получив значение SCN, 6442991097246, можно выполнить восстановление до соответствующего момента, экспортировать таблицу и восстановить ее в системе. Восстановить ее можно в состоянии, непосредственно предшествующем удалению.

Пакет DBMS_LOGMNR Использование области PGA Для выполнения своих функций пакеты LogMiner используют память в области PGA. Как я уже говорил, это означает, что пакет DBMS_LOGMNR нельзя использовать при подключении в режиме MTS. Мы не рассматривали, какой объем памяти в области PGA фактически может использовать LogMiner. Все файлы журнала в моей системе имеют размер 100 Мбайт. Я загрузил два из них для анализа, определив размер используемой области PGA до и после этого: tkyte@TKYTE816> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a. s t a t i s t i c # = b. s t a t i s t i c # 4 and lower(a.name) like '%pga%' 5/ NAME session pga memory session pga memory max VALUE 454768 tkyte@TKYTE816> declare 2 l_name varchar2(255) default 3 'C:\oracle\ORADATA\tkyte816\archive\TKYTE816T001S012';

4 begin 5 for i in 49.. 50 6 loop 7 sys.dbms_logmnr.add_logfile(l_name || i || '.ARC');

8 end loop;

9 10 sys.dbms_logmnr.start_logmnr 11 (dictFileName => 'c:\temp\miner_dictionary.dat', 12 options => sys.dbms_logmnr.USE_COLMAP);

13 end;

14 / PL/SQL procedure successfully completed. tkyte@TKYTE816> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a. s t a t i s t i c # = b. s t a t i s t i c # 4 and lower(a.name) like '%pga%' 5/ NAME session pga memory session pga memory max VALUE 11748180 Итак, 200 Мбайт архивных журналов повторного выполнения сейчас занимают около 11,5 Мбайт в области PGA. Это означает, что либо большая часть архивного журнала повторного выполнения ничего полезного не содержит, либо сервер Oracle кэширует в памяти не весь файл журнала повторного выполнения. Действительно, сервер Oracle на самом деле не кэширует весь файл журнала повторного выполнения в оперативной па Приложение А мяти. Он подчитывает данные с диска по мере надобности. В оперативной памяти кэшируется только часть информации. Если выполнить запрос к представлению V$LOGMNR_CONTENTS и после этого определить объем используемой памяти в области PGA, мы увидим, что по мере обращения к данным объем используемой памяти растет: tkyte@TKYTE816> create table tmp_logmnr_contents unrecoverable 2 as 3 select * from v$logmnr_contents 4/ Table created. tkyte@TKYTE816> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) like '%pga%' 5/ NAME session pga memory session pga memory max VALUE 19965696 Как видите, теперь сеансу надо почти 20 Мбайт памяти в области PGA.

Ограничения пакетов LogMiner Пакеты LogMiner имеют ряд ограничений, о которых вы должны знать. Ограничения связаны с использованием объектных типов Oracle и переносом строк.

Объектные типы Oracle Объектные типы лишь частично поддерживаются средствами LogMiner. Пакеты LogMiner не могут восстановить SQL-операторы, обычно используемые для доступа к данным объектных типов, и поддерживают не все объектные типы. Ограничения в этой области лучше всего продемонстрировать на примере. Начнем с небольшой схемы, в которой есть данные таких популярных объектных типов, как VARRAY, и вложенные таблицы: tkyte@TKYTE816> create or replace type myScalarType 2 as object 3 (x i n t, y date, z varchar2(25));

4/ Type created. tkyte@TKYTE816> create or replace type myArrayType 2 as varray(25) of myScalarType 3/ Type created. tkyte@TKYTE816> create or replace type myTableType 2 as table of myScalarType Пакет DBMS_LOGMNR / Type created. tkyte@TKYTE816> drop table t;

Table dropped. tkyte@TKYTE816> create table t (a int, b myArrayType, c myTableType) 2 nested table c store as c_tbl 3 / Table created. tkyte@TKYTE816> begin 2 sys.dbms_logmnr_d.build('miner_dictionary.dat', 3 'c:\temp');

4 end;

5 / PL/SQL procedure successfully completed. tkyte@TKYTE816> alter system switch logfile;

System altered. tkyte@TKYTE816> insert into t values (1, 2 myArrayType(myScalarType(2, sysdate, 'hello')), 3 myTableType(myScalarType(3, sysdate+1, 'GoodBye')) 4 );

1 row created. tkyte@TKYTE816> alter system switch logfile;

System altered. Итак, в представленном выше примере мы создали ряд объектных типов, добавили таблицу, использующую эти типы, снова экспортировали словарь данных, а затем выполнили один оператор ЯМД для этой таблицы. Теперь посмотрим, что скажут о выполненных действиях средства LogMiner: tkyte@TKYTE816> begin 2 sys.dbms_logmnr.add_logfile('C:\oracle\rdbms\ARC00028.001', 3 dbms_logmnr.NEW);

4 end;

5/ PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 sys.dbms_logmnr.start_logmnr 3 (dictFileName=> 'c:\temp\miner_dictionary.dat');

4 end;

5/ PL/SQL procedure successfully completed. tkyte@TKYTE816> select scn, sql_redo, sql_undo 2 from v$logmnr_contents 3/ Приложение А SCN SQL_REDO 824288 824288 824288 824288 set transaction read write;

SQL_UNDO 824288 insert into delete from TKYTE.C_TBL(NESTED_T TKYTE.C_TBL where ABLE_ID,X,Y,Z) NESTED_TABLE_ID = values HEXTORAW('252cb5fad8 (HEXTORAW('252cb5fad 784e2ca93eb432c2d35b 8784e2ca93eb432c2d35 7c') and X = 3 and Y b7c'),3,TO_DATE('23- = JAN-2001 16:21:44', TO_DATE('23-JAN-2001 'DD-MON-YYYY 16:21:44', HH24:MI:SS'),'GoodBy 'DD-MON-YYYY e');

HH24:MI:SS') and Z = 'GoodBye' and ROWID 'AAAFaqAADAAAAGzAAA' 824288 824288 824288 824288 insert into TKYTE.T(A,B,SYS_NC00 00300004$) values (l,Unsupported Type,HEXTORAW('252cb 5fad8784e2ca93eb432c 2d35b7c'));

delete from TKYTE.T where A = 1 and B = Unsupported Type and SYS_NC0000300004$ = HEXTORAW('252cb5fad8 784e2ca93eb432c2d35b 7c') and ROWID = ' AAAFapAADAAAARjAAA' ;

10 rows selected.

Как видите, один исходный оператор INSERT:

tkyte@TKYTE816> insert into t values (1, 2 myArrayType(myScalarType(2, sysdate, 'hello')), 3 myTableType(myScalarType(3, sysdate+1, 'GoodBye')) 4 );

1 row created. был преобразован в два оператора INSERT: один — для подчиненной (вложенной) таблицы, другой — для главной таблицы Т. LogMiner не воспроизвел один оператор INSERT он выдал эквивалентный набор SQL-операторов. Посмотрев на результат внимательнее, можно обнаружить в тексте оператора INSERT INTO T конструкцию Unsupported Type вместо одного из значений столбцов. Возвращаясь к исходному оператору INSERT, Пакет DBMS_LOGMNR можно выяснить, что не поддерживается столбец типа VARRAY. Средства LogMiner не позволяют воспроизвести эту конструкцию. Это не делает пакеты LogMiner полностью бесполезными при работе с объектами. Просто результат нельзя использовать для отмены или повторного выполнения транзакций, поскольку соответствующие SQL-операторы воспроизводятся не полностью. Однако можно использовать результат для анализа тенденций, проверки и т.п. Более интересно, пожалуй, то, что пакеты позволяют увидеть, как сервер Oracle внутренне осуществляет поддержку объектных типов. Например, рассмотрим вставку строки в таблицу T:

insert into t k y t e. t (a, b, SYS_NC0000300004$) values...

Вполне понятно, что такое А и В. Это столбцы типа INT и MyArrayType (VARRAY). Однако куда делся столбец С и что за столбец SYS_NC0000300004$? Столбец С — это вложенная таблица, а вложенные таблицы хранятся в отдельной, подчиненной таблице. Столбец С не хранится в таблице T;

он хранится в отдельной таблице. Столбец SYS_NC0000300004$ — суррогатный первичный ключ для таблицы T, использующийся как внешний ключ во вложенной таблице C_TBL. Если рассмотреть оператор INSERT для вставки данных во вложенную таблицу: i n s e r t i n t o tkyte.c_tbl(nested_table_id, x, у, z) values... можно увидеть, что во вложенную таблицу добавлен столбец NESTED_TABLE_ID, использующийся для соединения со столбцом T.SYS_NC0000300004$. Изучив значение, вставленное в оба эти столбца: HEXTORAW('252cb5fad8784e2ca93eb432c2d35b7c') можно выяснить, что сервер Oracle по умолчанию использует для соединения таблиц C_TBL и T сгенерированное системой 16-байтовое значение типа RAW. Поэтому анализ действий с помощью LogMiner позволяет понять, как реализованы возможности сервера Oracle. В данном случае мы узнали, что тип вложенной таблицы реализуется как пара таблиц главная/подчиненная с суррогатным ключом в главной таблице и внешним ключом в подчиненной.

Перемещенные или фрагментированные строки Средства LogMiner в настоящий момент не позволяют работать с перемещенными строками (migrated row) или фрагментированными строками (chained row). Фрагментированной называют строку, расположенную в нескольких блоках. Перемещенной называют строку, вставленную в один блок, а затем в результате изменения выросшую настолько, что она уже не вмещается в исходном блоке и поэтому перемещена в другой блок. Идентификатор у перемещенной строки остается прежним, а в блоке, куда она первоначально вставлялась, остается указатель на новое местонахождение строки. Перемещенные строки являются специальным случаем фрагментированных строк. Это фрагментированная строка, в первом блоке которой нет данных — все данные находятся во втором блоке. Чтобы разобраться, как средства LogMiner обрабатывают фрагментированные строки, создадим одну такую строку. Начнем с таблицы, содержащей девять столбцов типа Приложение А CHAR(2000). Я использую в базе данных блоки размером 8 Кбайт, так что, если во всех девяти столбцах будут непустые значения, строка будет иметь размер 18000 байт, что слишком много для одного блока. Эта строка будет фрагментирована не менее чем на три блока. Для демонстрации этого используем следующую таблицу: tkyte@TKYTE816> create table t (x int primary key, 2 a char(2000), 3 b char(2000), 4 c char(2000), 5 d char(2000), 6 e char(2000), 7 f char(2000), 8 g char(2000), 9 h char(2000), 10 i char(2000));

Table created. Теперь, чтобы продемонстрировать проблему, я вставлю строку в таблицу T со значениями только в столбцах X и А. Размер этой строки будет составлять чуть больше 2000 байт. Поскольку столбцы В, С, D и т.д. пусты, они не будут занимать места. Эта строка поместится в один блок. Затем изменим строку, задав значения для столбцов В, С, D и E. Поскольку значения типа CHAR всегда дополняются до заданной длины пробeлами, размер строки увеличится с немногим более 2000 байт до примерно 10000 байт, в результате чего будет фрагментирована на два блока. Изменим значение всех столбцов строки, увеличив ее размер до 18 Кбайт и вызвав фрагментацию на три блока. Затем загрузим содержимое журнала повторного выполнения с помощью LogMiner и посмотоим. как оно будет обработано: tkyte@TKYTE816> begin 2 sys.dbms_logmnr_d.build('miner_dictionary.dat', 3 'c:\temp');

4 end;

5/ PL/SQL procedure successfully completed. tkyte@TKYTE816> alter system archive log current;

System altered. tkyte@TKYTE816> insert into t (x, a) values (1, 'non-chained');

1 row created. tkyte@TKYTE816> commit;

Commit complete. tkyte@TKYTE816> update t set a = 'chained row', 2 b = 'x', c = 'x', 3 d = 'x', e = 'x' 4 where x = 1;

1 row updated. tkyte@TKYTE816> commit;

Пакет DBMS_LOGMNR Commit complete. tkyte@TKYTE816> update t set a 2 b = 'x', c = 3 d = 'x', e = 4 f = 'x', g = 5 h = 'x', i = 6 where x = 1;

1 row updated. tkyte@TKYTE816> commit;

Commit complete. tkyte@TKYTE816> alter system archive log current;

System altered. Создав состояние, которое мы хотим проанализировать, можно загружать соответствующий журнал с помощью LogMiner. Не забудьте: после создания таблицы T мы должны пересоздать файл словаря данных, или результат нельзя будет проанализировать! tkyte@TKYTE816> declare 2 l_name v$archived_log.name%type;

3 begin 4 5 select name into l_name 6 from v$archived_log 7 where completion_time = (select max(completion_time) 8 fromv$archived_log);

9 10 sys.dbms_logmnr.add_logfile(l_name, dbms_logmnr.NEW);

11 end;

12 / PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 sys.dbms_logmnr.start_logmnr 3 (dictFileName=> 'c:\temp\miner_dictionary.dat');

4 end;

5/ PL/SQL procedure successfully completed. tkyte@TKYTE816> select scn, sql_redo, sql_undo 2 from v$logmnr_contents 3 where sql_redo is not null or sql_undo is not null 4/ SCN SQL_REDO SQL_UNDO = 'chained row', 'x', 'x', 'x', 'x' 6442991118354 set transaction read write;

6442991118354 insert into TKYTE.T(X,A) va delete from TKYTE.T where X lues (1,'non-chained = 1 and A = 'non-chained Приложение А ');

= ' a n d ROWID 'AAAHdgAAGAAAACKAAA';

6442991118355 commit;

6442991118356 set transaction read write;

6442991118356 Unsupported (Chained Row) Unsupported (Chained Row) 6442991118356 Unsupported (Chained Row) Unsupported (Chained Row) 6442991118357 commit;

6442991118358 set transaction read write;

6442991118358 Unsupported (Chained Row) Unsupported (Chained Row) 6442991118358 Unsupported (Chained Row) Unsupported (Chained Row) 6442991118358 Unsupported (Chained Row) Unsupported (Chained Row) 6442991118359 commit;

12 rows selected. Как видите, исходный оператор INSERT представлен средствами Log Miner, как ожидалось. А оператор UPDATE, фрагментировавший строку, в результатах LogMiner не значится. Вместо этого выдано Unsupported (Chained Row). Интересно отметить, что эта конструкция выдана дважды для первого оператора UPDATE и трижды — для второго. Пакет LogMiner выдает информацию об изменениях в базе данных по блокам. Если строка находится в двух блоках, в представлении V$LOGMNR_CONTENTS будет две записи об изменениях. Если строка фрагментирована на три блока, будет три записи. Поэтому следует учитывать, что средства LogMiner не позволяют полностью воспроизвести SQL-операторы для повторного выполнения или отмены изменений во фрагментированных и перемещенных строках.

Другие ограничения Кроме рассмотренных выше, средства LogMiner имеют ряд других ограничений. В настоящее время также не поддерживается: • анализ таблиц, организованных по индексу;

• анализ таблиц и индексов кластеров.

Представление V$LOGMNR_CONTENTS Представление V$LOGMNR_CONTENTS содержит по одной строке для каждого логического изменения в базе данных, выбранного из обработанных файлов журнала повторного выполнения. Мы уже неоднократно использовали это представление, но обращались лишь к отдельным его столбцам. В следующей таблице, созданной на основе документации Oracle, описаны все столбцы этого представления, включая доступную в них информацию:

Столбец Описание SCN Номер системного изменения для транзакции, выполнившей изменение.

Пакет DBMS_LOGMNR Столбец TIMESTAMP Описание Дата генерации записи повторного выполнения. Временнue отметки не могут влиять на упорядочение записей повторного выполнения Поскольку значение SCN присваивается при фиксации, только SCN можно использовать для упорядочения записей повторного выполнения. Упорядочение по столбцу TIMESTAMP в многопользовательской системе даст неверный порядок Идентифицирует поток, сгенерировавший запись повторного выполнения Идентифицирует файл журнала в представлении V$LOGMNR_FILES, содержащий данную запись повторного выполнения. Этот столбец является внешним ключом для представления V$LOGMNR_FILES Номер сегмента отмены (Undo Segment Number - USN) идентификатора транзакции (Transaction ID - XID). Идентификатор транзакции создается по значениям столбцов XIDUSN, XIDSLOT и XIDSQN и используется для определения транзакции, выполнившей изменение. Вместе взятые, эти три поля однозначно идентифицируют транзакцию Номер слота идентификатора транзакции. Задает номер записи в таблице транзакций Порядковый номер идентификатора транзакции Однозначно определяет журнал, содержавший рассматриваемую запись повторного выполнения. Значение RBA (Redo Block Address - адрес блока повторного выполнения) состоит из значений столбцов RBASQN, RBABLK и RBABYTE Номер блока в файле журнала Смещение от начала блока, задаваемого предыдущим столбцом Номер файла UBA (Undo Block Address - адрес блока отмены), идентифицирующий файл, содержащий соответствующий блок отмены. Значение UBA создается на основе значений столбцов UBAFIL, UBABLK, UBASQN и UBAREC и используется для идентификации данных отмены, сгенерированных в процессе изменения Номер блока UBA Индекс записи UBA Порядковый номер блока отмены UBA Абсолютный номер файла блока данных. Значение ABS_FILE# вместе со значениями REL_FILE#, DATA_BLOCK#, DATA_OBJ# и DATA_DOBJ идентифицирует блок, измененный транзакцией Относительный номер файла блока данных. Задается относительно табличного пространства, в котором создан объект Номер блока данных THREAD# LOG_ID XIDUSN XIDSLOT XIDSQN RBASQN RBABLK RBABYTE UBAFIL UBABLK UBAREC UBASQN ABS_FILE# REL_FILE# DATA_BLOCK# Приложение А Столбец DATA_OBJ# DATA_DOBJ# SEG_OWNER SEG_NAME Описание Номер объекта блока данных Номер объекта блока данных, идентифицирующий объект в табличном пространстве Имя пользователя, которому принадлежит объект Имя структуры, которой был выделен сегмент (другими словами, имя таблицы, имя кластера и т.п.). Имя сегмента для фрагментированных таблиц состоит из двух частей: после имени таблицы через запятую выдается имя фрагмента (например, (ИмяТаблицы.ИмяФрагмента)) Тип сегмента в виде числа Тип сегмента в виде строки (другими словами, TABLE, INDEX и т.п.) В первой версии будет поддерживаться единственный тип сегмента, TABLE. Сегменты остальных типов будут обозначаться как UNSUPPORTED Идентификатор строки Идентификатор сеанса, сгенерировавшего данные повторного выполнения. Если номер сеанса из журнала повторного выполнения получить не удается, выдается пустое значение Порядковый номер сеанса, сгенерировавшего данные повторного выполнения. Значения SESSION# и SERIAL# позволяют однозначно определить соответствующий сеанс сервера Oracle. Если порядковый номер сеанса из журнала повторного выполнения получить не удается, выдается пустое значение Имя пользователя, который выполнил действие, сгенерировавшее запись повторного выполнения. Вместо имени пользователя всегда будет выдаваться пустое значение, если только не включена опция проверки при архивировании. Проверка включается с помощью параметра инициализации TRANSACTION_AUDITING Строка, содержащая регистрационное имя пользователя, информацию о клиенте, имя пользователя операционной системы, имя машины, терминал операционной системы и имя программы в операционной системе Значением 1 (ИСТИНА) обозначаются действия и SQLоператоры, сгенерированные в результате запроса отката. В противном случае в этом столбце содержится значение 0 (ЛОЖЬ) Тип операции SQL. Будут выдаваться только значения INSERT, DELETE, UPDATE, COMMIT и BEGIN_TRANSACTION. Все остальные операции будут представлены как UNSUPPORTED или INTERNAL OPERATION SEG_TYPE SEG_TYPE_NAME TABLE_SPACE_NAME Имя табличного пространства ROW_ID SESSION# SERIAL# USERNAME SESSION_INFO ROLLBACK OPERATION Пакет DBMS_LOGMNR Столбец SQL_REDO, SQL_UNDO Описание Столбцы SQL_REDO и SQL_UNDO содержат SQL-подобные операторы, представляющие логические операции повторного выполнения и отмены, построенные на основе одной или нескольких записей архивного журнала повторного выполнения. Пустое значение показывает, что для этой записи повторного выполнения нельзя сгенерировать допустимый SQL-оператор. Некоторые записи повторного выполнения нельзя преобразовать в SQL-операторы. В этом случае в столбцах SQL_REDO и SQL UNDO будут содержаться пустые значения, а в столбце STATUS - строка UNSUPPORTED Значение RS_ID (Record Set ID - идентификатор набора записей) однозначно определяет набор записей, использованных для генерации SQL-оператора (набор может состоять из одной записи). Это значение можно использовать для выявления ситуаций, когда несколько записей генерируют один SQL-оператор. Во всех записях соответствующего набора будет одинаковое значение RS_ID. SQL-оператор будет выдан только в последней строке набора. Столбцы SQL_REDO и SQL_UNDO во всех остальных строках набора будут пустыми. Учтите, что пара значений RS_ID/SSN уникально идентифицирует сгенерированный SQL-оператор (см. описание столбца SSN) Значение SSN (SQL Sequence Number - порядковый номер SQLоператора) можно использовать для идентификации нескольких строк с допустимыми операторами SQL_REDO, сгенерированных по одной записи повторного выполнения (при множественных вставках или непосредственной загрузке). Все такие строки будут иметь одинаковое значение RS_ID, но уникальные значения SSN. Значение SSN увеличивается, начиная с 1 для каждого нового значения RS_ID Значение 1 (ИСТИНА) в столбце CSF (Continuation SQL Flag флаг продолжающегося SQL-оператора) показывает, что сгенерированный средствами LogMiner оператор REDO_SQL или UNDO_SQL длиннее, чем максимальный размер данных типа VARCHAR2 (в настоящее время - 4000 символов). SQLоператоры, длина которых превышает это ограничение, будут занимать несколько строк. Остальная часть SQL-оператора будет содержаться в следующей строке. Пара значений RS_ID, SSN у всех продолжающихся строк, которые соответствуют одному SQL-оператору, будет одинаковой. В последней строке значение в столбце CSF будет равно 0 (ЛОЖЬ), что обозначает завершение SQL-оператора Показывает состояние преобразования. Пустое значение означает успешное преобразование, а значение UNSUPPORTED означает, что эта версия пакетов LogMiner не поддерживает преобразование в SQL-операторы. Значение READ_FAILURE свидетельствует о внутреннем сбое операционной системы при попытке прочитать данные из файла журнала. Значение TRANSLATION_ERROR показывает, что LogMiner не смог полностью выполнить преобразование (это может быть связано с повреждением журнала или устаревшим файлом словаря данных) RS_ID SSN CSF STATUS Приложение А Столбец PH1_NAME Описание Имя столбца-заместителя (Placeholder column name). Столбцызаместители - это общие столбцы, с которыми можно связать любой столбец таблицы базы данных с помощью файла сопоставления LogMiner Значение повторного выполнения для столбца-заместителя Значение отмены для столбца-заместителя Имя столбца-заместителя Значение повторного выполнения для столбца-заместителя Значение отмены для столбца-заместителя Имя столбца-заместителя Значение повторного выполнения для столбца-заместителя Значение отмены для столбца-заместителя Имя столбца-заместителя Значение повторного выполнения для столбца-заместителя Значение отмены для столбца-заместителя Имя столбца-заместителя Значение повторного выполнения для столбца-заместителя Значение отмены для столбца-заместителя PH1_REDO PH1_UNDO PH2_NAME PH2_REDO PH2_UNDO PH3_NAME PH3_REDO PH3_UNDO PH4_NAME PH4_REDO PH4_UNDO PHS_NAME PH5_REDO PH5_UNDO Резюме Средства LogMiner используются не каждый день. Я не могу вспомнить ни одного приложения, которому бы они понадобились по ходу работы. Однако они позволяют определить, что происходило в базе данных, и с этой задачей справляются прекрасно. Вы видели, как пакеты LogMiner помогают при поиске "кто и когда это сделал" постфактум — именно для этого средства LogMiner используются. Или программа с ошибкой выполняется неправильно, или привилегированный пользователь сделал не то, что нужно (и не признается в содеянном). Если не была включена проверка, нет другого способа вернуться в прошлое и разобраться в том, что произошло. Средства LogMiner можно использовать и для отмены ошибочной транзакции, если удастся получить операторы SQL дляm отмены и повторного выполнения. В общем случае LogMiner — хорошее средство для "скрытой" работы. Процедуры этих пакетов не попадут в список десяти наиболее часто используемых, но иногда без них не обойтись.

Пакет DBMS_OBFUSCATION_TOOLKIT В этом разделе рассматривается шифрование данных. Мы обсудим использование стандартного пакета DBMS_OBFUSCATION_TOOLKIT в версиях Oracle 8.1.6 и 8.1.7, а также другую реализацию (оболочку), которую можно создать на базе этого пакета для расширения его функциональных возможностей. Мы затронем ряд проблем, связанных с использованием этого пакета, и очень важный аспект управления ключами. Пакеты для поддержки шифрования в базе данных появились в Oracle 8.1.6. Они были расширены в версии Oracle 8.1.7 и включают поддержку ключей шифрования большего размера и алгоритма хеширования MD5. В Oracle 8.1.6 поддерживается одинарный алгоритм шифрования DES (Data Encryption Standard) с 56-байтовым ключом. В Oracle 8.1.7 поддерживаются одинарное и тройное шифрование DES с использованием 56-, 112- и 168-битовых ключей. Алгоритм DES реализует шифрование с симметричным ключом. Это означает, что для шифрования и дешифрования данных используется один и тот же ключ. Алгоритм DES шифрует данные 64-битовыми (8-байтовыми) блоками с помощью 56-битового ключа. Ниже мы рассмотрим, как размер блока, 8 байт, должен учитываться пользователями подпрограмм шифрования. Алгоритм DES игнорирует 8 бит переданного 64-битового ключа. Однако разработчики должны передавать 64-битовый (8-байтовый) ключ. Тройной DES (3DES) гораздо устойчивей к взлому по сравнению с DES. Зашифрованные данные сложно расшифровать с помощью полного перебора. Потребуется 2**112 попыток при использовании двойного (с 16-байтовым ключом) алгоритма 3DES или 2**168 попыток при использовании тройного (с 24-байтовым ключом) алгоритма 3DES и всего лишь 2**56 попыток для обычного алгоритма DES. Как сказано в заключительной части рабочего документа rfcl321 (полный текст рабочего документа rfcl321 можно найти на сайте http://www.ietf.org/rfc.html), новый алгоритм MD5:

Приложение А... принимает сообщение произвольной длины и выдает 128-битовый "отпечаток" или резюме сообщения ("message digest"). Предполагается, что задача создания двух сообщений с одинаковым резюме или сообщения с заданным резюме эффективно неразрешима. Алгоритм MD5 предназначен для приложений, использующих цифровую подпись, в которых большой файл необходимо безопасно "сжать", прежде чем шифровать приватным (секретным) ключом в системе шифрования с открытым ключом, например, RSA По сути, алгоритм MD5 — это способ проверки целостности данных, намного более надежный, чем контрольные суммы и другие популярные методы. Для выполнения представленных ниже примеров использования алгоритмов DES3 и MD5 необходим доступ к серверу Oracle 8.1.7. Примеры, в которых используется алгоритм DES, работают во всех версиях, начиная с Oracle 8.1.6. При использовании подпрограмм шифрования и резюмирования по алгоритму MD5 пакета DBMS_OBFUSCATION_TOOLKIT должны выполняться следующие требования, что несколько затрудняет их применение. • Длина шифруемых данных должна быть кратна 8. 9-байтовое поле типа VARCHAR2, например, надо будет дополнять до 16 байт. Попытка зашифровать или расшифровать фрагмент данных, длина которого не кратна 8, завершится выдачей сообщения об ошибке. • Ключ, используемый для шифрования данных, должен иметь длину 8 байт для процедуры DESEncrypt и 16 или 24 байт для процедур DES3Decrypt. • В зависимости от используемого вида шифрования необходимо вызывать разные подпрограммы. Например, если используется 56-битовое шифрование, вызываются подпрограммы DESENCRYPT и DESDECRYPT, если 112/168-битовое подпрограммы DES3ENCRYPT и DES3DECRYPT. Лично я предпочитаю использовать один набор подпрограмм для всех трех вариантов. • Подпрограммы шифрования в версии Oracle 8.1.6 являются процедурами, поэтому их нельзя использовать в SQL-операторах (процедуры нельзя вызывать в SQLоператорах непосредственно). • Стандартные подпрограммы шифрования позволяют непосредственно шифровать данные объемом до 32 Кбайт. Они не обеспечивают шифрование/дешифрование больших объектов. • Среди подпрограмм шифрования в версии Oracle 8.1.7 есть функции. Однако эти функции перегружены так (см. представленные далее примеры), что использовать их в SQL-операторах тоже нельзя. • Подпрограммы, реализующие алгоритм MD5, перегружены аналогично и тоже не могут использоваться в SQL-операторах. По моему опыту, первое требование, связанное с необходимостью использовать данные, длина которых кратна 8, в приложениях выполнить труднее всего. Мне не хотелось бы заниматься тем, чтобы длина шифруемых данных, например зарплаты или других конфиденциальных данных, была кратна 8 байтам. К счастью, можно легко реализовать пакет-оболочку, обеспечивающий шифрование без учета этого и большин Пакет DBMS_OBFUSCATION_TOOLKIT ства других требований. А вот обеспечение длины ключа 8, 16 или 24 байт должен взять на себя разработчик. Я собираюсь представить здесь пакет-оболочку, который будет работать в версиях 8.1.6 и выше, поддерживать все возможности шифрования и обеспечивать: • возможность вызова функций в SQL-операторах;

• использование одной и той же функции независимо от длины ключа;

• возможность шифрования/дешифрования больших объектов в PL/SQL и в SQLоператорах;

• успешную установку независимо от используемой версии сервера (8.1.6 или 8.1.7) — другими словами, независимость от наличия процедур DES3Encrypt/Decrypt и поддержки алгоритма MD5.

Пакет-оболочка Начнем со спецификации пакета. Зададим функциональный интерфейс, обеспечивающий шифрование и дешифрование данных типа VARCHAR, RAW, BLOB и CLOB. Используемый алгоритм (DES или 3DES с 16- или 24-байтовым ключом) будет выбираться в зависимости от длины ключа. В нашем интерфейсе длина ключа будет задавать алгоритм. Интерфейс устроен так, что ключ можно передавать при каждом вызове или установить на уровне пакета с помощью подпрограммы SETKEY. Преимущество использования подпрограммы SETKEY связано с тем, что проверка длины ключа и определение соответствующего алгоритма требует определенных ресурсов. Если ключ устанавливается один раз, а функции шифрования вызываются многократно, можно избежать повторного выполнения одних и тех же действий. Еще одна особенность при задании ключа состоит в том, что при работе с данными типа RAW или BLOB надо использовать ключ типа RAW. Если в качестве ключа для данных типа RAW/BLOB желательно использовать данные типа VARCHAR2, необходимо привести ключ к типу RAW c помощью средств пакета UTL_RAW, который рассматривается далее в этом приложении. С другой стороны, при работе с данными типа VARCHAR2 и CLOB, ключ должен быть типа VARCHAR2. Помимо организации дополнительного уровня интерфейса к средствам шифрования этот пакет обеспечивает доступ к подпрограммам CHECKSUM c алгоритмом MD5, если они установлены (используется сервер версии 8.1.7 и выше). Этот пакет-оболочка добавляет несколько возможных сообщений об ошибках к тем, что имеются в пакете DBMS_OBFUSCATION_TOOLKIT и описаны в документации (эти сообщения наш пакет просто передает вызывающему). В процессе работы с сервером версии 8.1.6 выдаются такие, не известные ранее сообщения: • PLS-00302: component 'MD5' must be declared • PLS-00302: component 'DES3ENCRYPT' must be declared • PLS-00302: component 'THREEKEYMODE' must be declared Приложение А Эти сообщения об ошибках генерируются при попытке использовать средства версии 8.1.7, шифрование по алгоритму DES3 или хеширование с помощью алгоритма MD5, на сервере версии 8.1.6. Вот предполагаемая спецификация пакета-оболочки. Описание процедур и функций представлено после кода: create or replace package crypt_pkg as function encryptString(p_data in varchar2, p_key in varchar2 default NULL) return varchar2;

function decryptString(p_data in varchar2, p_key in varchar2 default NULL) return varchar2;

function encryptRaw(p_data in raw, p_key in raw default NULL) return raw;

function decryptRaw(p_data in raw, p_key in raw default NULL) return raw;

function encryptLob(p_data in clob, p_key in varchar2 default NULL) return clob;

function encryptLob(p_data in blob, p_key in raw default NULL) return blob;

function decryptLob(p_data in clob, p_key in varchar2 default NULL) return clob;

function decryptLob(p_data in blob, p_key in raw default NULL) return blob;

subtype checksum_str is varchar2(16);

subtype checksum_raw is raw(16);

function md5str(p_data function md5raw(p_data function md51ob(p_data function md51ob(p_data procedure setKey(p_key end;

/ Функции ENCRYPTSTRING и DECRYPTSTRING используются для шифрования/ дешифрования любых данных типа STRING, DATE или NUMBER длинной до 32 Кбайт. Максимальный размер PL/SQL-переменной — 32 Кбайт, что существенно превышает максимальный размер строки, сохраняемой в базе данных, — 4000 байт. Эти функции можно вызывать непосредственно из SQL-операторов, так что, можно шифровать данные в базе с помощью операторов INSERT или UPDATE и выбирать уже расшифрованные данные с помощью оператора SELECT. Параметр KEY — необязательный. Если ключ установлен с помощью процедуры SETKEY, передавать его при каждом вызове необязательно. Имеются также функции ENCRYPTRAW и DECRYPTRAW. Они реализуют для данных типа RAW те же возможности, что и функции для данных типа VARCHAR2. Я намеренно избегаю перегрузки функций шифрования/дешифрования для данных типа R W A и VARCHAR2, давая им разные имена. Это делается для того, чтобы избежать следующей проблемы: in in in in in varchar2) return checksum_str;

raw) return checksum_raw;

clob) return checksum_str;

blob) return checksuro_raw;

varchar2);

Пакет DBMS_OBFUSCATION_TOOLKIT tkyte8TKYTE816> create or replace package overloaded 2 as 3 function foo(x in varchar2) return number;

4 function foo(x in raw) return number;

5 end;

6/ Package created. tkyte@TKYTE816> select overloaded.foo('hello') from dual;

select overloaded.foo('hello') from dual * ERROR at line 1: ORA-06553: PLS-307: too many declarations of 'P001 match this call tkyte@TKYTE816> select overloaded.foo(hextoraw('aa')) from dual;

select overloaded.foo( hextoraw('aa')) from dual * ERROR at line 1: ORA-06553: PLS-307: too many declarations of 'F00' match this call Сервер не различает типы данных RAW и VARCHAR2 в сигнатурах перегруженных функций. Вызвать эти функции в SQL-операторах невозможно. Даже если использовать другие имена параметров функций (как в пакете DBMS_OBFUSCATlON_TOOLKIT), нельзя вызвать эти функции из SQL-операторов, поскольку ключевая передача параметров в языке SQL не поддерживается. Единственно возможное решение — использовать функции с уникальными именами для идентификации необходимого типа данных параметра. Функции ENCRYPTLOB и DECRYPTLOB — это перегруженные функции, предназначенные для работы с данными типа CLOB или BLOB. Сервер Oracle позволяет различать эти типы в сигнатурах, и этим можно воспользоваться. Поскольку нельзя шифровать более 32 Кбайт с помощью подпрограмм пакета DBMS_OBFUSCATION_TOOLKIT, эти функции будут использовать алгоритм шифрования больших объектов частями по 32 Кбайт. Полученный большой объект будет представлять собой набор зашифрованных фрагментов данных размером по 32 Кбайт. Оболочка дешифрования, которую мы реализуем, учитывает упаковку данных подпрограммами шифрования больших объектов, дешифруя фрагменты и собирая из них исходный большой объект. Затем в пакете идут подпрограммы для вычисления контрольных сумм по алгоритму MD5. Чтобы точнее задать тип возвращаемых ими значений, я создал подтипы: subtype checksum_str is varchar2(16);

subtype checksum_raw is raw(16);

и указал, что эти функции возвращают именно эти типы. Пользователи пакета могут объявлять переменные этих типов: tkyte@TKYTE816> declare 2 checksum_variable 3 begin 4 null;

5 end;

crypt_pkg.checksum_str;

Приложение А / PL/SQL procedure successfully completed. Это позволяет не гадать, каким будет размер возвращаемых контрольных сумм. Для данных типа VARCHAR2 предлагается четыре функции CHECKSUM (включая типы DATE и NUMBER, для которых выполняется неявное преобразование), RAW, CLOB и BLOB. Следует помнить, что контрольная сумма по алгоритму MD5 будет вычисляться только по первым 32 Кбайтам данных типа CLOB или BLOB, поскольку с переменными большего размера в языке PL/SQL работать нельзя. Представленная далее реализация пакета не только дает более удобные в использовании средства шифрования, но и демонстрирует несколько полезных приемов. Во-первых, она показывает, как легко создать свою оболочку со специализированным интерфейсом для стандартных пакетов базы данных. В данном случае мы обходим ряд очевидных ограничений пакета DBMS_OBFUSCATION_TOOLKIT. Во-вторых, показан один из методов разработки пакета, защищенного от будущих изменений в реализации стандартных пакетов. Хотелось бы создать один пакет-оболочку, который будет работать в версиях 8.1.6 и 8.1.7 и обеспечивать при этом полный доступ к возможностям версии 8.1.7. Если бы для доступа к подпрограммам DESENCRYPT, DES3DECRYPT и средствам поддержки алгоритма MD5 использовался статический SQL, пришлось бы создавать отдельную версию пакета для версии 8.1.6 сервера, поскольку функций для алгоритмов MD5 и DES3 в версии 8.1.6 нет. Использованный в реализации динамический вызов позволяет создать пакет, который можно использовать в обеих версиях сервера. При этом также сокращается объем кода, который необходимо написать. Вот реализация пакета CRYPT_PKG с описанием выполняемых действий. create or replace package body crypt_pkg as — глобальные переменные пакета g_charkey varchar2(48);

g_stringFunction varchar2(1);

g_rawFunction varchar2(1);

g_stringWhich varchar2(75);

g_rawWhich varchar2(75);

g_chunkSize CONSTANT number default 32000;

Пакет начинается с объявления глобальных переменных и констант. • G_CHARKEY. В ней хранится ключ типа RAW или VARCHAR2 для использования подпрограммами шифрования. Ее длина — 48 байт, чтобы можно было хранить 24-байтовый ключ типа RAW (при этом длина удваивается из-за преобразования в шестнадцатеричный вид данных типа RAW при записи в переменную типа VARCHAR2).

• G_STRINGFUNCTION и G_RAWFUNCTION. Содержит после вызова SETKEY пустое значение либо строку '3'. Мы будем динамически добавлять эту строку к имени подпрограммы при выполнении, чтобы вызывать DESENCRYPT либо DES3ENCRYPT, в зависимости от размера ключа. Другими словами, она используется для формирования имени вызываемой функции.

Пакет DBMS_OBFUSCATION_TOOLKIT • G_STRINGWHICH и G_RAWWHICH. Используется только для функций DES3ENCRYPT/DES3DECRYPT. Добавляет четвертый необязательный параметр, требующий использования тройного ключа, когда алгоритм 3DES работает в этом режиме. Тогда как предыдущая строковая переменная определяет, какую из функций вызывать: DESENCRYPT или DES3ENCRYPT, эта переменная задает, какое значение должно быть передано для размера ключа: для двойного или тройного. • G_CHUNKSIZE. Константа, задающая размер фрагмента шифруемого/дешифруемого большого объекта. Она также задает максимальный размер данных, посылаемых функциям вычисления контрольной суммы по алгоритму MD5 при работе с большими объектами. Важно, чтобы это значение было кратно 8, — это предполагается в представленной далее реализации. Далее имеется шесть небольших служебных подпрограмм. Они являются вспомогательными и используются другими подпрограммами пакета: function padstr(p_str in varchar2) return varchar2 as l_len number default length(p_str);

begin return to_char(l_len,'fm00000009') || rpad(p_str, (trunc(l_len/8)+sign(mod(l_len,8)))*8, chr(0));

end;

function padraw(p_raw in raw) return raw as l_len number default utl_raw.length(p_raw);

begin return utl_raw.concat(utl_raw.cast_to_raw(to_char(l_len,'fm00000009')), p_raw, utl_raw.cast_to_raw(rpad(chr(0), (8-mod(l_len,8))*sign(mod(l_len,8)), chr(0))));

end;

При описании алгоритма шифрования DES было сказано, что DES шифрует данные блоками по 64 бита (8 байт). Значит, пакет DBMS_OBFUSCATION_TOOLKIT работает только с данными, длина которых кратна 8 байтам. Если шифруется строка длиной 7 байт, ее надо дополнить до 8. 9-байтовую строку необходимо дополнить до 16 байт. Две представленных выше подпрограммы кодируют и дополняют до нужной длины строки и данные типа RAW. Кодирование происходит путем помещения значения исходного размера данных перед данными в строку. Затем строка дополняется двоичными нулями (CHR(0)) до длины, кратной 8 байтам. Например, строка Hello World будет закодирована следующим образом: tkyte@TKYTE816> select length(padstr), padstr, dump(padstr) dump 2 from 3 (selectto_char(l_len,'fm00000009') || 4 zpad(p_str, 5 (trunc(l_len/8)+sign(mod(l_len,8)))*8, Приложение А 6 chr(0)) padstr 7 from (select length('Hello World') l_len, 8 'Hello World' p_str 9 from dual 10 ) 11 ) 12 / LENGTH(PADSTR) PADSTR 24 00000011Hello World DUMP Typ=l Len=24: 48,48,48,48,48,4 8,49,49,72,101,108,108,111,32, 87,111,114,108,100,0,0,0,0, Окончательная длина закодированной строки — 24 байта (LENGTH(PADSDTR)), а исходная длина была 11 байт (это видно в первых восьми символах значения PADSTR). В столбце DUMP, где выданы десятичные значения байтов строки, можно увидеть, что строка завершается пятью двоичными нулями. Нам пришлось добавить 5 байт, чтобы дополнить 11-байтовую строку Hello World до длины, кратной 8. Далее идут подпрограммы, "отменяющие" выполненное выше дополнение нулевыми байтами: function unpadstr(p_str in varchar2) return varchar2 is begin return substr(p_str, 9, to_number(substr(p_str,l,8)));

end;

function unpadraw(p_raw in raw) return raw is begin return utl_raw.substr(p_raw, 9, to_number(utl_raw.cast_to_varchar2(utl_raw.substr(p_raw, 1,8)))) ;

end;

Они достаточно понятны. Предполагается, что в первых восьми байтах строки или данных типа RAW находится исходная длина строки, и возвращается соответствующая подстрока закодированных данных. Осталось две вспомогательные процедуры: procedure wa(p_clob in out clob, p_buffer in varchar2) is begin dbms_lob.writeappend(p_clob, length(p_buffer), p_buffer);

end;

procedure wa(p_blob in out blob, p_buffer in raw) is begin dbms_lob.writeappend(p_blob, utl_raw.length(p_buffer), p_buffer);

end;

Они упрощают вызов DBMS_LOB.WRITEAPPEND, сокращая имя до двух букв (WA) и передавая длину записываемого фрагмента буфера — она всегда совпадает с полной длиной буфера.

Пакет DBMS_OBFUSCATION_TOOLKIT Теперь рассмотрим первую общедоступную процедуру, SETKEY: procedure setKey(p_key in varchar2) as begin if (g_charkey = p_key OR p_key is NULL) then return;

end if;

g_charkey := p_key;

if (length(g_charkey) not in (8, 16, 24, 16, 32, 48)) then raise_application_error(-20001, 'Key must be 8, 16, or 24 bytes');

end if;

select decode(length(g_charkey),8,'','3'), decode(length(g_charkey),8,'',16,'', 24, ', which=>dbms_obfuscation_toolkit.ThreeKeyMode'), decode(length(g_charkey),16, '', '3'), decode(length(g_charkey),16,'•,32,'', 48,', which=>dbms_obfuscation_toolkit.ThreeKeyMode') into g_stringFunction, g_stringWhich, g_rawFunction, g_rawWhich from dual;

end;

Процедура используется независимо от того, вызывали вы ее или нет. Остальные общедоступные подпрограммы вызывают процедуру SETKEY всегда. Она сравнивает переданный ключ P_KEY с тем, что хранится в глобальной переменной G_CHARKEY. Если они совпадают или ключ не задан, процедура завершает работу. Если же значение P_KEY отличается от значения G_CHARKEY, процедура продолжит работу. Сначала она проверит, допустима ли длина ключа и кратна ли 8. Ключ должен быть длиной 8, 16 или 24 байт. Поскольку процедуре могут быть переданы данные типа RAW, что вызывает представление каждого байта двухбайтовым шестнадцатиричным кодом, допускается также длина ключа 16, 32 и 48. Такая проверка, однако, не гарантирует, что ключ можно использовать. Например, можно передать четырехбайтовый ключ типа RAW, который в процедуре будет иметь длину 8 байт. В этом случае при дальнейшем выполнении подпрограмм пакета DBMS_OBFUSCATION_TOOLKIT будет получено сообщение об ошибке. Оператор SELECT с функцией DECODE используется для установки значений остальных глобальных переменных. Поскольку мы пока не можем различить типы данных RAW и VARCHAR2, то устанавливаем значения всем четырем переменным. Главное в этом фрагменте кода то, что если длина ключа — 8 байт (16 байт, если он типа RAW), то переменная FUNCTION получит значение пустой строки. Если же длина ключа — 16 или 24 байт (32 или 48 байт для ключа типа RAW), в переменную FUNCTION записывается строка ' 3 '. Именно это в дальнейшем позволит вызвать подпрограмму DESENCRYPT или DES3Encrypt. Обратите внимание также на установку значения глобальной переменной WHICH. Она используется для передачи необязательного параметра подпрограмме DES3ENCRYPT. Если длина ключа — 8 или 16 байт (16 или 32 байта для ключа типа RAW), переменная получает значение Null, — параметр не передается. Если Приложение А длина ключа — 24 байта (48 байт для ключа типа RAW), она получает значение THREEKEYMODE, требующее от процедур ENCRYPT/DECRYPT использовать ключ большего размера. Теперь мы готовы к рассмотрению функций, выполняющих основные действия: function encryptString(p_data in varchar2, p_key in varchar2 default NULL) return varchar2 as l_encrypted long;

begin setkey(p_key);

execute immediate 'begin dbms_obfuscation_toolkit.des' || g_StringFunction || 'encrypt (input_string => :1, key_string => :2, encrypted_string => :3' || g_stringWhich || ' ) ;

end;

' using IN padstr(p_data), IN g_charkey, IN OUT l_encrypted;

return l_encrypted;

end;

function encryptRaw(p_data in raw, p_key in raw default NULL) return raw as l_encrypted long raw;

begin setkey(p_key);

execute immediate 'begin dbms_obfuscation_toolkit.des' || g_RawFunction || 'encrypt (input => :1, key => :2, encrypted_data => :3' || g_rawWhich || ' ) ;

end;

' using IN padraw(p_data), IN hextoraw(g_charkey), IN OUT l_encrypted;

return l_encrypted;

end;

Функции ENCRYPTSTRING и ENCRYPTRAW действуют одинаково. Они обе динамически вызывают процедуру DESENCRYPT либо DES3ENCRYPT. Этот динамический вызов не только сокращает объем необходимого кода (поскольку избавляют от оператора IF THEN ELSE для статического вызова процедур), но и позволяют устанавливать пакет без изменений в версии 8.1.6 или 8.1.7. Поскольку мы не ссылаемся на подпрограммы пакета DBMS_OBFUSCATION_TOOLKIT статически, то сможем скомпилировать функцию в любой версии. Этот прием с динамическим вызовом пригодится в том случае, когда точно не известно, что будет установлено в базе данных. Я использовал его ранее при написании утилит, которые должны были устанавливаться на серверах версии 7.3, 8.0 и 8.1. Со временем в базовых пакетах появляются новые функции, и, если код работает в версии 8.1, хотелось бы их использовать. В версии 7.3 код тоже будет работать;

в нем просто нельзя будет использовать новые функциональные возможное Пакет DBMS_OBFUSCATION_TOOLKIT ти. В нашем случае именно так и происходит. При установке пакета на сервере версии 8.1.7 представленный выше код будет вызывать процедуру DES3ENCRYPT. При установке пакета на сервере версии 8.1.6любая попытка вызвать процедуру DES3ENCRYPT приведет к ошибке времени выполнения (но не помешает установить пакет). Вызовы процедуры DESENCRYPT будут работать так же, как в версии 8.1.6. Эти функции динамически формируют строку на основе значений глобальных переменных FUNCTION и WHICH, которые мы установили в процедуре SETKEY. Мы либо добавим цифру 3 к имени процедуры, либо нет. Мы добавим необязательный четвертый параметр к вызову процедуры DES3ENCRYPT, если необходимо использовать тройной ключ. Затем выполняем строку, посылая данные и ключ для шифрования, и получаем данные в зашифрованном виде. Обратите внимание, как к исходным данным подставляются результаты применения функций PADSTR или PADRAW. Шифруется закодированная строка, дополненная до соответствующей длины. Теперь рассмотрим функции, выполняющие обратные действия: function decryptString(p_data in varchar2, p_key in varchar2 default NULL) return varchar2 as l_string long;

begin setkey(p_key);

execute immediate 'begin dbms_obfuscation_toolkit.des' || g_StringFunction || 'decrypt (input_string => :1, key_string => :2, decrypted_string => :3' || g_stringWhich || ' ) ;

end;

' using IN p_data, IN g_charkey, IN OUT l_string;

return unpadstr(l_string);

end;

function decryptRaw(p_data in raw, p_key in raw default NULL) return raw as l_string long raw;

begin setkey(p_key) ;

execute immediate 'begin dbms_obfuscation_toolkit.des' || g_RawFunction || 'decrypt (input => :1, key => :2, decrypted_data => :3 ' || g_rawWhich || ' ) ;

end;

' using IN p_data, IN hextoraw(g_charkey), IN OUT l_string;

return unpadraw(l_string);

end;

Функции DECRYPTSTRING и DECRYPTRAW работают аналогично представленным ранее функциям ENCRYPT. Единственное отличие в том, что они вызывают из пакета DBMS_OBFUSCATION_TOOLKIT процедуры DECRYPT вместо ENCRYPT и Приложение А используют функцию UNPAD для декодирования полученной строки или данных типа RAW. Перейдем к функциям для шифрования больших объектов: function encryptLob(p_data in clob, p_key in varchar2) return clob as l_clob clob;

l_offset number default 1;

l_len number default dbms_lob.getlength(p_data);

begin setkey(p_key);

dbms_lob.createtemporary (l_clob, TRUE) ;

while (l_offset <= l_len) loop wa(l_clob, encryptString( dbms_lob.substr(p_data, g_chunkSize, l_offset)));

l_offset := l_offset + g_chunksize;

end loop;

return l_clob;

end;

function encryptLob(p_data in blob, p_key in raw) return blob as l_blob blob;

l_offset number default 1;

l_len number default dbms_lob.getlength(p_data);

begin setkey(p_key);

dbms_lob.createtemporary(l_blob, TRUE);

while (l_offset <= l_len) loop wa(l_blob, encryptRaw( dbms_lob.substr(p_data, g_chunkSize, l_offset)));

l_offset := l_offset + g_chunksize;

end loop;

return l_blob;

end;

Это перегруженные функции для данных типа BLOB и CLOB. Вначале они создают временный большой объект, в который будут записываться зашифрованные данные. Поскольку при шифровании мы изменяем длину строки/данных типа RAW, обеспечивая сохранение исходной длины и дополнение до необходимой, делать это "на месте", используя существующий большой объект, не представляется возможным. Например, при наличии большого объекта размером 64 Кбайт мы "увеличим" первые 32 Кбайт. Теперь необходимо сместить остальные 32 Кбайта большого объекта, чтобы обеспечить пространство для записи увеличенного фрагмента данных. Кроме того, это не позволит вызывать данные функции из SQL-операторов, поскольку локатор большого объекта придется передавать в режиме IN/OUT, а при наличии параметров в режиме IN/OUT Пакет DBMS_OBFUSCATION_TOOLKIT вызывать функцию в SQL-операторах нельзя. Поэтому мы просто копируем зашифрованные данные в новый большой объект, который затем можно использовать где угодно, в том числе в операторе INSERT или UPDATE. Для шифрования и кодирования данных большого объекта используется следующий алгоритм. Начиная с байта 1 (L_OFFSET), мы шифруем G_CHUNKSIZE байт данных. Они добавляются к ранее созданному временному большому объекту. Добавляем к смещению значение G_CHUNKSIZE и продолжаем выполнять тело цикла, пока не обработаем весь большой объект. Возвращаем временный большой объект вызывающему. Теперь перейдем к дешифрованию данных больших объектов: function decryptLob(p_data in clob, p_key in varchar2 default NULL) return clob as l_clob clob;

l_offset number default 1;

l_len number default dbms_lob.getlength(p_data);

begin setkey(p_key);

dbms_lob.createtemporary(l_clob, TRUE);

loop exit when l_offset > l_len;

wa(l_clob, decryptString( dbms_lob.substr(p_data, g_chunksize+8, l_offset)));

l_offset := l_offset + 8 + g_chunksize;

end loop;

return l_clob;

end;

function decryptLob(p_data in blob, p_key in raw default NULL) return blob as l_blob blob;

l_offset number default 1;

l_len number default dbms_lob.getlength(p_data);

begin setkey(p_key);

dbms_lob.createtemporary(l_blob, TRUE);

loop exit when l_offset > l_len;

wa(l_blob, decryptRaw( dbms_lob.substr(p_data, g_chunksize+8, l_offset)));

l_offset := l_offset + 8 + g_chunksize;

end loop;

return l_blob;

end;

В этих функциях мы снова, по тем же причинам, что и ранее, используем временный большой объект для дешифрования. На этот раз, однако, есть еще одна причина для использования временного большого объекта. Если не использовать временный большой объект для записи дешифрованных данных, данные будут дешифроваться непосредственно в базе. Последующие операторы SELECT будут выдавать уже дешифро Приложение А ванные данные, если мы не скопируем их в новый большой объект. В данном случае использовать временный большой объект еще важнее. Проходим в цикле по фрагментам большого объекта. Начав со смещения 1 (с первого байта) большого объекта, выбираем с помощью SUBSTR из него G_CHUNKSIZE+8 байт. Эти 8 байт добавлены к данным функциями PADSTR/PADRAW при кодировании. Итак, обрабатываем большой объект фрагментами размером G_CHUNKSIZE+8 байт, дешифруем данные и добавляем их к временному большому объекту. Этот объект затем возвращается клиенту. Теперь рассмотрим последнюю часть пакета CRYPT_PKG — интерфейс к подпрограммам, реализующим алгоритм MD5: function md5str(p_data in varchar2) return checksum_str is l_checksum_str checksum_str;

begin execute immediate 'begin :x := dbms_obfuscation_toolkit.md5(input_string => :y);

end;

' using OUT l_checksum_str, IN p_data;

return l_checksum_str;

end;

function md5raw(p_data in raw) return checksum_raw is l_checksum_raw checksum_raw;

begin execute immediate 'begin :x := dbms_obfuscation_toolkit.md5(input => :y);

end;

' using OUT l_checksum_raw, IN p_data;

return l_checksum_raw;

end;

function md51ob(p_data in clob) return checksum_str is l_checksum_str checksum_str;

begin execute immediate 'begin :x := dbms_obfuscation_toolkit.md5(input_string => :y);

end;

' using OUT l_checksum_str, IN dbms_lob.substr(p_data,g_chunksize,l);

return l_checksum_str;

end;

function md51ob(p_data in blob) return checksum_raw is l_checksum_raw checksum_raw;

begin execute immediate 'begin :x := dbms_obfuscation_toolkit.md5(input => :y);

end;

' using OUT l_checksum_raw, IN dbms_lob.substr(p_data,g_chunksize,l);

return l_checksum_raw;

end;

end;

/ Пакет DBMS_OBFUSCATION_TOOLKIT Эти функции просто передают данные исходным функциям пакета DBMS_OBFUSCATION_TOOLKIT. При этом они, правда, не перегружены, что позволяет их использовать непосредственно в SQL-операторах. Следует помнить, что функции MD5 для больших объектов вычисляют контрольные суммы только по первым G_CHUNKSIZE байтам данных. Это связано с ограничением языка PL/SQL на максимальный размер переменных. Теперь я продемонстрирую функциональные возможности пакета. Следующие примеры выполнялись на сервере Oracle 8.1.7. Такие примеры с использованием алгоритмов DES3 и MD5 в версии 8.1.6 не выполнятся. tkyte@DEV817> declare 2 l_str_data varchar2(25) := 'hello world';

3 l_str_enc varchar2(50);

4 l_str_decoded varchar2(25);

5 6 l_raw_data raw(25) :=utl_raw.cast_to_raw('Goodbye');

7 l_raw_enc raw(50);

8 l_raw_decoded raw(25);

9 10 begin 11 crypt_pkg.setkey('MagicKey');

12 13 l_str_enc := crypt_pkg.encryptString(l_str_data);

14 l_str_decoded := crypt_pkg.decryptString(l_str_enc);

15 16 dbros_output.put_line('Encoded In hex = ' || 17 utl_raw.cast_to_raw(l_str_enc));

18 dbms_output.put_line('Decoded = ' || l_str_decoded);

19 20 crypt_pkg.setkey(utl_raw.cast_to_raw('MagicKey'));

21 22 l_raw_enc := crypt_pkg.encryptRaw(l_raw_data);

23 l_raw_decoded := crypt_pkg.decryptRaw(l_raw_enc);

Pages:     | 1 |   ...   | 19 | 20 || 22 | 23 |   ...   | 24 |



© 2011 www.dissers.ru - «Бесплатная электронная библиотека»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.