Oracle. Администрирование и разработка.

Закостенелость мышления

2012-03-13T15:55:00.000+04:00

Каким же сложным может оказаться для многих решение простейшей задачи: определение наличия подстроки в строке, но в немного нестандартной ситуации.

Задача: выбрать из таблицы строки, в которых поле попадает в список значений, перечисленных в строке в виде набора значений через запятую.

Пример:

SQL> create table comparison (serialnum varchar2(10), 
                                   name varchar2(50));

SQL> insert into comparison values ('TST0010021','Table 01');

SQL> insert into comparison values ('MON0000001','Monitor');

SQL> insert into comparison values ('COMP000002','Desktop computer');

SQL> insert into comparison values ('COMP000003','Notebook');

SQL> insert into comparison values ('COMP000004','Notebook for travel');

SQL> select * from comparison;

SERIALNUM  NAME
---------- --------------------
TST0010021 Table 01
MON0000001 Monitor
COMP000002 Desktop computer
COMP000003 Notebook
COMP000004 Notebook for travel

Самое простое решение "в лоб" - искать подстроку в строке, но большинство даже не подумает сделать обратное сравнение: не "serialnum LIKE '%MP%'", а наоборот:

SQL> var nums varchar2(33)

SQL> exec :nums := 'MON0000001,COMP000004,TST0010021'

PL/SQL procedure successfully completed.

SQL> select * from comparison where :nums like '%'||serialnum||'%';

SERIALNUM  NAME
---------- --------------------
TST0010021 Table 01
MON0000001 Monitor
COMP000004 Notebook for travel

Не говорите только, что в таком подходе огромное пространство для разработки SQL-инъекций. Это не так, если использовать связанные (bind) переменные. Но в любом случае, данный пример предназначен для демонстрации обратного сравнения.

Не думайте шаблонами, разминайте мозги!

Производительность NVL против COALESCE

2012-03-12T11:48:00.000+04:00

На днях, в поисках способа оптимизировать долгоиграющую процедуру расчета одной модели наткнулся в очередной раз на сравнение COALESCE против NVL.
Для тех, кто не в курсе, у Oracle есть следующие SQL-функции для обработки значений NULL:

NVL(expr1, expr2) – в случае, если expr1 IS NOT NULL, выводится expr1, в противном случае – expr2
NVL2 (expr1, expr2, expr3) - в случае, если expr1 IS NOT NULL, выводится expr2, в противном случае – expr3
COALESCE (expr1, expr2, … exprN) – возвращает первый NOT NULL аргумент, т.е. для трех аргументов аналогично NVL(expr1,NVL(expr2, expr3))

По своей сути COALESCE с двумя параметрами полностью аналогичен NVL, однако COALESCE «умнее». NVL обязательно расчитывает оба аргумента перед выдачей результата, а COALESCE расчитывает аргументы в порядке проверки. Смотрим тесты на 10`000`000 проходах.

Пример 1. Статичные аргументы:

SQL> 
BEGIN
  FOR i IN 1..10000000 LOOP
    IF NVL('y','x')='x' THEN
      NULL;
  END IF;
  END LOOP;
END;
/

PL/SQL procedure successfully completed

Executed in 0,562 seconds

BEGIN
  FOR i IN 1..10000000 LOOP
    IF COALESCE('y','x')='x' THEN
      NULL;
    END IF;
  END LOOP;
END;
/

PL/SQL procedure successfully completed

Executed in 0,156 seconds

Пример 2. Первый аргумент вычисляемый, второй статичен:

SQL> 
BEGIN
  FOR i IN 1..10000000 LOOP
    IF NVL(sys_context('USERENV','HOST'),'x')='x' THEN
      NULL;
  END IF;
  END LOOP;
END;
/

PL/SQL procedure successfully completed

Executed in 19,484 seconds

BEGIN
  FOR i IN 1..10000000 LOOP
    IF COALESCE(sys_context('USERENV','HOST'),'x')='x' THEN
      NULL;
    END IF;
  END LOOP;
END;
/

PL/SQL procedure successfully completed

Executed in 18,36 seconds

Пример 3. Оба аргумента вычисляемые:

SQL> 
BEGIN
  FOR i IN 1..10000000 LOOP
    IF NVL(sys_context('USERENV','HOST'),sys_context('USERENV','HOST'))='x' THEN
      NULL;
  END IF;
  END LOOP;
END;
/

PL/SQL procedure successfully completed

Executed in 36,656 seconds

BEGIN
  FOR i IN 1..10000000 LOOP
    IF COALESCE(sys_context('USERENV','HOST'),sys_context('USERENV','HOST'))='x' THEN
      NULL;
    END IF;
  END LOOP;
END;
/

PL/SQL procedure successfully completed

Executed in 18 seconds

Цифры лучше любых слов показывают разницу.

Но стоп! Почему только PL\SQL? Забыли про SQL?

Нет, далее смотрим разные варианты при использовании запросов. В тестовой таблице 10`000`000 записей, в поле val1 25% значений NULL.
Для любителей использовать DECODE (ну не правильно это делать, неправильно!) вместо NVL или COALESCE примеры тоже добавлены. Кроме того для полной картины добавлен и вариант с CASE:

-- Статические параметры

SELECT MAX(nvl(val1, val2)) AS RESULT FROM test_performance;
 
    RESULT
----------
         4
 
Executed in 13,297 seconds

SELECT MAX(coalesce(val1, val2)) AS RESULT FROM test_performance;
 
    RESULT
----------
         4
 
Executed in 13,312 seconds

SELECT MAX(decode(val1, NULL, val2, val1)) AS RESULT FROM test_performance;
 
    RESULT
----------
         4
 
Executed in 15,703 seconds

SELECT MAX(CASE 
             WHEN val1 IS NULL THEN
              val2
             ELSE
              val1
             END) AS RESULT
  FROM test_performance;
 
    RESULT
----------
         4
 
Executed in 8,766 seconds

-- Второй параметр вычисляемый

SELECT MAX(nvl(val1, dbms_random.value)) AS RESULT
  FROM test_performance;
 
    RESULT
----------
         4
 
Executed in 34,031 seconds

SELECT MAX(coalesce(val1, dbms_random.value)) AS RESULT
  FROM test_performance;
 
    RESULT
----------
         4
 
Executed in 16,828 seconds

SELECT MAX(decode(val1, NULL, dbms_random.value, val1)) AS RESULT
  FROM test_performance;
 
    RESULT
----------
         4
 
Executed in 19,688 seconds

SELECT MAX(CASE 
             WHEN val1 IS NULL THEN
              dbms_random.value
             ELSE
              val1
             END) AS RESULT
  FROM test_performance;
 
    RESULT
----------
         4
 
Executed in 15,062 seconds

-- Оба параметра вычисляемые

SELECT MAX(nvl(nullif(round(dbms_random.value * 5), 3),
               dbms_random.value)) AS RESULT
  FROM test_performance;
 
    RESULT
----------
         5
 
Executed in 91,313 seconds

SELECT MAX(coalesce(nullif(round(dbms_random.value * 5), 3),
                    dbms_random.value)) AS RESULT
  FROM test_performance;
 
    RESULT
----------
         5
 
Executed in 72,016 seconds

SELECT MAX(decode(nullif(round(dbms_random.value * 5), 3),
                  NULL,
                  dbms_random.value,
                  nullif(round(dbms_random.value * 5), 3))) AS RESULT
  FROM test_performance;
 
    RESULT
----------
         5
 
Executed in 123,844 seconds

SELECT MAX(CASE
             WHEN nullif(round(dbms_random.value * 5), 3) IS NULL THEN
              dbms_random.value
             ELSE
              nullif(round(dbms_random.value * 5), 3)
             END) AS RESULT
  FROM test_performance;
 
    RESULT
----------
         5
 
Executed in 121,797 seconds

UPDATE: Спасибо +Sayan Malakshinov за комментарии и отдельное сообщение с разъяснениями касательно бонусов NVL по сравнению с COALESCE. Основной смысл в том, что оптимизатор Oracle при построении плана запроса умеет разворачивать выполнение функции в конкатенацию с фильтрацией по каждому аргументу.

СРОЧНО! Oracle вводит аналог TOP и LIMIT

2012-03-11T11:48:00.001+04:00

Невероятно, но, проскочила информация о том, что Oracle в версии СУБД 12c введет конструкцию для ограничения выдачи, являющуюся аналогом TOP в MS SQL Server или LIMIT в MySQL:

In Database 12c, to limit the number of rows in a query can be simplified by ANSI fetch first/offset keyword via Oracle SQL row limiting clause.

For example, fetch the top 3 "oldest" employees:
 select *

 from employees

 order by hire_date

 fetch first 3 rows only;
And, keep the ties:
 select *

 from employees

 order by hire_date

 fetch first 3 rows with ties;
Or skip the first 3 employees, what we need is rank 4 to 6:
 select *

 from employees

 order by hire_date

 offset 3 rows

 fetch next 3 rows only;
May be limiting row count is way too precise, percentage style is preferred sometime =)
 select * 

 from employees

 order by hire_date

 fetch first 10 percent rows only;
Todd

Источник: https://blogs.oracle.com/toddbao/entry/oracle_database_12c_row_limiting

Как провести полное обновление матвью на узком канале?

2011-10-28T12:10:00.000+04:00

Предыстория
Мигрировали базу на новый сервер одновременно с обновлением версии Oracle (10.2 -> 11.2). Было решено использовать дамп для переноса, т.к. так уж сложилось, что на исходной базе размер блока был нестандартный (6К) и клонирование было невозможно. Кроме того, клонирование, даже если бы оно работало, занимало лишь на 15 минут меньше времени чем экспорт/импорт, время поджимало, а этими 15 минутами можно было пожертвовать.
Тестовый перенос данных прошел нормально, но когда совершил реальный переезд на новый сервак, обнаружил страшное (увы, лишь спустя часа два) - данные в снэпшотах не обновляются, основанная на матвью репликация не работает, а вместо самих матвью на новой базе имеются лишь таблицы с теми же именами. Мой прокол, надо решать, перезаливка не помогла, в дампе данные лишь о таблицах, старый сервер уже отключен. Пересоздание 90% материализованных представлений удалось, но оставались 3 таких, которые занимают больше 1Гб, а реплицируются с удаленного сервера по забитому каналу, средняя скорость по которому иногда доходит до 200КБит/с. Нереально...

Задача
В итоге стала задача: сделать complete refresh для соответствующих матвью, master table, для которых занимают более 1Гб, а канал узкий до нельзя. Как?
Для простоты обозначим следующие названия:

исходная база: master_db
хост исходной базы: master_host
целевая база: target_db
хост целевой базы: target_host
линк с целевой базы на исходную: repl_link
таблицы пусть называются passes, acts

Решение
Последовательность следующая:

(если были матвью) удалить матвью на целевой базе;
(в моем случае либо mview on prebuilt table) удалить таблицы на целевой базе;
сделать дамп таблиц на исходной базе;
сжать дамп, передать на целевой сервер;
залить таблицы на целевую базу;
создать матвью на целевой базе;
обновить с FAST REFRESH.

Если подробнее, то выглядит это так:

(если были матвью) Удаление матвью

MASTER_DB SQL> drop materialized view passes;

MASTER_DB SQL> drop materialized view acts;

(в моем случае либо mview on prebuilt table) Удаление таблиц

```
MASTER_DB SQL> drop table passes;
```
```
MASTER_DB SQL> drop table acts;
```

Делаем дамп

MASTER_HOST#  expdp username/password directory=data_pump_dir dumpfile=replication.dmp tables=(passes,acts)

Сжатие и передача

сжимаем дамп архиватором, неважно каким, в моем случае 1Гб содержал по большей части цифровые и строковые значения, поэтому ужалось до 14Мб
передаем на target_host обычным копированием и складываем в директорию, куда указывает DATA_PUMP_DIR в TARGET_DB
распаковываем архив на целевом сервере

Заливка дампа (необходимо залить только таблицы и, возможно, индексы, т.к. триггеры и констрейнты нам тут не нужны)

TARGET_HOST#  impdp username/password directory=data_pump_dir dumpfile=replication.dmp tables=(passes,acts) include=TABLE,INDEX

Создание матвью

TARGET_DB SQL>  create materialized view acts on prebuilt table refresh fast on demand as select * from acts@repl_link;

TARGET_DB SQL>  create materialized view passes on prebuilt table refresh fast on demand as select * from passes@repl_link;

Обновление матвью

exec dbms_mview.refresh('acts,passes','fast');

В итоге мы получаем полностью обновленные MATERIALIZED VIEW на целевой базе, а траффика на это ушло примерно в 100 раз меньше чем при обновлении COMPLETE REFRESH.

Уместное замечание от SergINI: можно опустить пункт "сжатие" при использовании Advanced Compression, т.к. в этом случае появляется возможность использовать DataPump с параметром COMPRESSION в значениях DATA_ONLY либо ALL, что позволит сжать дамп средствами Oracle. Кроме того, весь этот процесс значительно упрощается при использовании DataPump через DBLink.

--
P.S: что касается новых шаблонов, вернулся к старому виду, и проще и статистику по просмотрам будет реальную показывать, да и виджеты вернутся на место.

Новый интерфейс для блогов на Blogspot

2011-10-21T11:13:00.000+04:00

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

Oracle Gateway 11.2 для Oracle Database 10.2 для WinX64

2011-09-21T13:34:00.002+04:00

Многие из тех, кому по той или иной причине приходится администрировать СУБД Oracle на платформе Windows Server, кто переехал на 64-битную платформу, были неприятно удивлены, что такая простая штука как hsodbc теперь не работает (касается версий Oracle выше 9.2).

Весьма неприятное известие для тех, кто собирает данные из множества сторонних баз, MS SQL Server, DB2 и даже, если у вас кто-то предоставляет данные в виде регулярно обновляемого Excel-файла или базы в Access или dBase, было проще всего организовать ODBC-коннект через HSODBC. Но халява кончилась...

Итак, какие у нас есть возможности решения данной проблемы:

рядом с Oracle Database 10.1-11.2 для Windows x64 поставить 32-битную версию Oracle Database, настроить в ней hsodbc, настроить Listener на нестандартный порт;
извращаться с инициацией передачи данных со стороны источника;
установить Oracle Database Gateway 11.2 и организовать коннект через него.

Сейчас речь пойдет о последнем варианте как наиболее правильном. Хотя то, что этот вариант наиболее правильный, не значит, что с ним не будет проблем.

Последовательность действий следующая:

Качаем дистрибутив Oracle Database Gateway 11.2 отсюда: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
Устанавливаем Gateway в отдельный Oracle Home, назовем его OraGtw_home. При установке обязательно выбираем Gateway for ODBC и Net Listener.
Правим системную переменную PATH так, чтобы путь к OraGtw_home\bin находился после пути к bin от основной базы.
Настраиваем ODBC-коннектор для 64-битной системы:

Настраиваем системный DSN через классическую админку ODBC.
Запускаем regedit и ищем ключ созданного DSN, пусть это будет MSDB, находиться он будет примерно по такому пути: HKLM\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\MSDB
Экспортируем этот ключ и редактируем полученный файл и убираем из пути ключа Wow6432Node чтобы путь получился примерно следующим: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MSDB
Теперь необходимо этот ключ импортировать, но не делайте это по привычке двойным щелчком на reg-файле, он запихнет ключ на исходное место в Wow6432Node, делать это надо через File->Import в regedit.
ODBC-коннектор готов.

Настраиваем гетерогенный сервис:

Идем в OraGtw_home\hs\admin и создаем файл initmsdb.ora с одной лишь строкой:
HS_FDS_CONNECT_INFO=MSDB
Настраиваем Listener (тут нет разницы, какой использовать, из Oracle Home от БД или из OraGtw_home, я использовал от БД) как при классической гетерогенке:

Имя листенера должно отличаться от основного, скажем LISTENERGTW
HOST=<имя хоста с гейтом>
PORT=<отличный от порта основного слушателя, например 1522>
SID_NAME=MSDB

В итоге у нас должен получиться такой конфиг (либо его часть при использовании уже существующего):
# Заменить выделенное на свои значения
LISTENERGTW =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <имя хоста>)(PORT = <порт>))
    )
)

SID_LIST_LISTENERGTW=
(SID_LIST=
      (SID_DESC=
         (SID_NAME=msdb)
         (ORACLE_HOME=<OraGtw_home>)
         (PROGRAM=<OraGtw_home>\bin\dg4odbc.exe )
      )
)
lsnrctl start LISTENERGTW

Настраиваем подключение из Oracle Database:

Делаем запись в TNSNAMES.ORA в Oracle Home базы данных:
MSDB.CPCPIPE.RU =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <имя хоста>)(PORT = <порт>))
    (CONNECT_DATA =
      (SID = msdb)
    )
    (HS = OK)
)
Создаем Database Link:
CREATE DATABASE LINK msdb_link CONNECT TO
msuser IDENTIFIED BY mspass
USING 'msdb';

Проверяем:
SQL> select * from dual@msdb_link;

DUMMY
-----
X

Возможные проблемы

Ситуация 1

Ошибка:
ORA-28513: internal error in heterogeneous remote agent
ORA-02063: preceding line from MSDB_LINK
Источник проблемы в неправильной настройке гетерогенного сервиса, как правило, это означает, что:

в OraGtw_home\hs отсутствует файл init<SID>.ora, т.е. в нашем примере initmsdb.ora;
либо в этом файле указан неверный DSN;
либо DSN отсутствует в настройках ODBC для 64-битных приложений.

Детали проблемы прекрасно видны в логах listenergtw.log.

Ситуация 2

Ошибка:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-02063: preceding line from TEST_NAVI
Источник проблемы где-то в TNSNAMES, скорее всего просто нужно пересчитать скобки в конфигурационном файле и перепроверить положение записи (HS=OK).

Если кто знает способ редактирования ODBC для 64-битных программ без возни с реестром, пожалуйста, подскажите.

Буду рад, если это поможет кому-нибудь безболезненно переехать с HSODBC на Database Gateway for ODBC.

Деление на ноль? Легко!

2011-06-01T15:07:00.000+04:00

Q: can oracle divide by zero?
A: no
Q: are you absolutely sure?
A: yes
Q: then why does this work?
select 1 from dual where exists (select 1/0 from dual)
A: ????

Отсюда

P.S: реально работает и я действительно не понимаю, почему, вернее догадываюсь, но...

ROWNUM в разных ситуациях

2011-04-18T17:57:00.001+04:00

А знаете ли вы что... функция ROWNUM в разных ситуациях ведет себя по-разному. Так вот, не так давно наткнулись на интересный момент, о котором расскажу ниже.

Все, кто хоть раз пытался использовать ROWNUM вместе с сортировкой выборки, прекрасно себе представляет, какая каша выдается в итоге. Большинство прекрасно понимает, а Oracle об этом прямо заявляет, что указанная функция не гарантирует верную нумерацию при проведении сортировки.
На всякий случай показываю, как оно бывает:

SQL> SELECT rownum, t.employee_id, t.first_name, t.last_name

  2  from hr.employees t

  3  order by last_name;



ROWNUM EMPLOYEE_ID FIRST_NAME      LAST_NAME

------ ----------- --------------- ------------

    75         174 Ellen           Abel

    67         166 Sundar          Ande

    31         130 Mozhe           Atkinson

     6         105 David           Austin

   105         204 Hermann         Baer

    17         116 Shelli          Baida

    68         167 Amit            Banda

    73         172 Elizabeth       Bates

    93         192 Sarah           Bell

    52         151 David           Bernstein

    30         129 Laura           Bissot
...

107 rows selected.





Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=107 Bytes=2461)




   1    0   SORT (ORDER BY) (Cost=4 Card=107 Bytes=2461)

   2    1     COUNT

   3    2       TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=3 Card=107 Bytes=2461)

В данном случае можно увидеть по плану, что ROWNUM считается до сортировки (да, COUNT - оно самое), в итоге получаем ту самую кашу.
А теперь эксперимент, сделаем "ложную" деревянную выборку одного уровня:

SQL> SELECT rownum, t.employee_id, t.first_name, t.last_name

  2  from hr.employees t

  3  connect by 1=2

  4  order siblings by last_name;



ROWNUM EMPLOYEE_ID FIRST_NAME      LAST_NAME

------ ----------- --------------- ------------

     1         174 Ellen           Abel

     2         166 Sundar          Ande

     3         130 Mozhe           Atkinson

     4         105 David           Austin

     5         204 Hermann         Baer

     6         116 Shelli          Baida

     7         167 Amit            Banda

     8         172 Elizabeth       Bates

     9         192 Sarah           Bell

    10         151 David           Bernstein

    11         129 Laura           Bissot

...

107 rows selected.



Execution Plan

----------------------------------------------------------


   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=107 Bytes=2461)


   1    0   COUNT

   2    1     CONNECT BY (WITHOUT FILTERING)

   3    2       TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=3 Card=107 Bytes=2461)

Забавно, но имея деревянный запрос, дающий такую же выборку (дерево никогда не получит второго уровня по условию 1=2), при ORDER SIBLINGS BY мы получаем корректные значения ROWNUM. Кроме того, в случае деревянной выборки сортировка производится как бы без сортировки (где-то в CONNECT BY (WITHOUT FILTERING) или еще где, надо трейсы смотреть), а ROWNUM (все тот же COUNT) выполняется последним перед выдачей клиенту. В результате мы получаем правильную нумерацию.

Обращаю внимание на то, что проход все также один и стоимость запроса остается неизменной.

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

P.S: По факту правильней использовать функцию из аналитики: ROW_NUMBER() OVER (ORDER BY column_name), которая гарантированно даст нужный порядок.

Deadlock vs Resource Busy

2011-04-14T19:08:00.000+04:00

В феврале 2010 года в Москву приезжал знаменитый Томас Кайт. Многие это помнят, но не многие запомнили один вопрос Тому в конце встречи, вопрос, на который он не смог ответить.
Вопрос заключался в ошибке, когда возникает deadlock в одной сессии внутри автономной транзакции, а именно почему при "select .. for update wait N" в случае N<6 возникает ошибка ORA-30006, а при N>=6 - ORA-00060. Тогда Томас был озадачен прилично, в итоге заявив только, что "it is feature".
В апреле 2010 он опубликовал эту ситуевину в своем блоге и среди возгласов поклонников, пораженных наличием пробелов в знаниях Тома, проскочило несколько комментариев по теме.

Итак... тест (версия 11.2.0.1):

SQL> create table t (x number);
SQL> insert into t values (1);
SQL> insert into t values (2);

SQL> update t set x = x+1;

2 rows updated.

SQL> declare
pragma autonomous_transaction;
l_rec t%rowtype;
begin
select * into l_rec from t for update wait 5;
commit;
end;
/
declare
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-06512: at line 5

SQL> declare
pragma autonomous_transaction;
l_rec t%rowtype;
begin
select * into l_rec from t for update wait 7;
commit;
end;

/
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 5

Интересно? Не то слово. Но вот что было в комментариях?

Порядок обнаружения deadlocks продолжает срабатывать каждые 3 секунды (после таймаута), но он не активируется в случае запроса в параметре WAIT значения меньше либо равном 5 секундам.

Этот момент управляется скрытым параметром:

_enqueue_deadlock_time_sec "requests with timeout <= this will not have deadlock detection", этот параметр по-умолчанию имеет значение 5 секунд.

Таким образом, если WAIT установлен в значение более этого параметра, то проверка наличия неразрешаемых блокировок активируется и срабатывает каждые 3 секунды.

То есть, не магические 5 секунд управляют типом ошибки, а именно скрытый параметр, которого, кстати, как бы и нет, но можно установить и в 11.2. Логика, кстати, вполне понятна: блокировку ресурса определить значительно проще, чем запускать процесс обнаружения неразрешаемых взаимных блокировок, т.е. deadlocks. Будете искать параметр - не ищите, ни в v$parameter, ни в memory Вы его не найдете, но вполне срабатывает

alter system set "_enqueue_deadlock_time_sec"=15 scope=spfile;

... с последующим перезапуском инстанса. Но напоминаю, что не стоит без крайней необходимости скрытые параметры, ведь неспроста они скрыты.

P.S: почему я описал это спустя год? Изучаем 11.2, проверяли взаимные блокировки, вспомнился тот случай. Итог: в случае двух разных сессий всегда ORA-00054, а автономная транзакция обрабатывается все также.

С уважением,
Дмитрий Богомолов

SecureLOB?

2011-04-12T11:37:00.001+04:00

Сегодня при изучении SecureFiles у одного товарища возник вопрос: "Вот мы можем указывать способы хранения LOB'ов через
LOB (col_name) STORE AS [SECUREFILE | BASICFILE],
где поведение очевидно, но можно указать SECURELOB. Что мы получим в результате?"

А в результате мы получим все тот же BASICFILE. Разработчики Oracle не сильно заморачивались со строгостью новых фишек в DDL и в коде, похоже, поставили "IF SECUREFILE THEN .. ELSE .. END;". В результате, вместо BASICFILE можно указывать все, чего душа пожелает, и любое значение кроме SECUREFILE будет указывать на BASICFILE.

FAIL: в комментах ткнули пальцем в доку, а именно в то место, где говорится про segment_name в STORE AS. Таким образом мы можем именовать сегменты (вместо названий типа SYS_LOB0000093350C00023$$), выделяемые под LOB'ы. А именно:

SQL> create table tttt(a number, cl clob) lob (cl) store as fg;

SQL> insert into tttt values (1,'sdfsd fas dfasdff');

SQL> insert into tttt values (2,'sdfsssssssdfsd fas dfasdff');

SQL> select segment_name, segment_type from dba_segments where owner='OE' and segment_name='FG';

SEGMENT_NAME SEGMENT_TYPE
------------------------------ --------------------
FG LOBSEGMENT

AskTom in Moscow

2010-02-26T09:44:00.002+03:00

Вернулся с данного мероприятия, наконец добрался до блога.

Сразу хотел бы выразить благодарность Дмитрию и Сергею за организацию мероприятия такого уровня. Огромное спасибо также Тому Кайту за то, что приехал. Если не вспоминать про синхронный перевод, то организация была на высшем уровне.

О чем рассказывал Том? Worst&Best Practics. Говорил о приятных вещах версии 11.2. Ну и конечно же отвечал на вопросы.

Том оказался удивительным оратором, так умело удерживать внимание в течение 8 часов надо уметь. Интересная факты, юмор, активная жестикуляция, все это привлекает взгляд и заставляет слушать.

Из неожиданного: почему-то очень долго считал, что AskTom ведется группой специалистов, но Том убедил, что это все он :)

Увы, уже очень давно не удается писать в блог. Банально не хватает времени. Могу лишь надеяться на то, что его будет больше.

С уважением,
Дмитрий Богомолов

ГАС "Правосудие" и Краевой суд - 2

2009-07-07T13:25:00.003+04:00

Была у меня когда-то заметочка на тему подъема БД в Краевом Суде:
Неспящие админы на службе у Краевого Суда. Но все дело в том, что история не то чтобы повторилась, но восстанавливать базу пришлось там же. Теперь уже была версия 9.2.0.6 в режиме noarchivelog, без бэкапов, вырубился свет, повредились редо-логи и была ругань на system01.dbf.

Короче если рассказывать, пропуская массу всего веселого, то шаги типа:
- подмена мертвого редо вновь созданным
- пересоздание контрол-файла
- создание pfile и внесение туда пары недокументированных параметров
- поднятие с модифицированным pfile
- рекавери
- open resetlogs
- попытка экспорта
- решение проблем с недостающими объектами
- экспорт (триггеры не экспортнулись, увы...)
- создание новой базы, выключение мертвой
- инициализация ГАС "Правосудие"
- отключение всех триггеров и внешних ключей
- удаление данных
- импорт схемы с IGNORE=y
- включение внешних ключей и триггеров

В подробностях все было гораздо веселее.

P.S: Не используйте в Production базы, на которых даже архивлоги выключены, они могут умереть в любой момент!
P.P.S: База, поднятая исключительно за счет недокументированных параметров Oracle - не есть база Oracle, никто не гарантирует её стабильную работу. Не продолжайте её использование, переносите данные, которые можно выдернуть.

С уважением,
Дмитрий Богомолов

Oracle PL\SQL Fundamentals на Brainbench

2009-05-21T13:41:00.004+04:00

Теперь проверял знания Oracle PL\SQL Fundamentals.

Your Criteria:

Test Module: Oracle PL/SQL Fundamentals
Score: 4.26

Your Ranking Information:
Total Tests Completed:	10245
Your Rank (1 = top):	134
Your Percentile (99 = top):	99%

Первый раз статус MASTER! И как назло, я помню пару вопросов, на которые мог но не дал правильного ответа, мало времени было, в некоторых вопросах фразы закручены так, что было тяжко понять, чего от тебя хотят.

Oracle покупает компанию Sun!

2009-04-20T17:36:00.003+04:00

Случилось то, чего мало кто ждал, Oracle покупает Sun Microsystems за $7.4 миллиарда! Интересно то, что эту же цену ($9.50 за акцию) предлагала IBM, но Sun запросила больше ($9.55).

"Oracle и Sun являются пионерами в индустрии и тесными партнерами в течение более 20 лет", говорит президент Sun, Скотт Макнили (Scott McNealy). "Это объединение является естественным продолжением наших взаимоотношений и оно станет определяющим событием в индустрии".

Больше информации в новости на сайте Sun и на сайте Oracle.

Поговаривают, что IBM в одностороннем порядке отказалась от дальнейших переговоров с Sun потому, что получила неофициальные данные о том, что антимонопольные коммитеты США и Европы потребуют дополнительной проверки по данной сделке, что займет 6-8 месяцев.

Использование неявных курсоров при DML-операциях

2009-03-12T09:54:00.014+03:00

Наверняка у многих возникала необходимость внутри PL\SQL блока проверить, сколько строк было вставлено, удалено или обновлено последней DML-операцией. Все знают, что где-то оно должно быть, но не все представляют где. В SQL*Plus да и в различных оболочках для работы с БД мы видим надписи


SQL> UPDATE test_tab t SET t.col1=1;

3 rows updated

Но как проверить результат выполнения в коде?
Для подобных целей в СУБД Oracle предусмотрен неявный курсор, который позволяет читать информацию о последнем отработавшем курсоре. Удивительно, но имя ему SQL!
Данный неявный курсор имеет следующие атрибуты: %FOUND, %ISOPEN, %NOTFOUND, и %ROWCOUNT.
%FOUND показывает, было ли изменение строк.
%NOTFOUND - DML-операция не смогла изменить строки.
%ROWCOUNT - на какое количество строк распространялась DML-операция.
%ISOPEN - всегда FALSE, т.к. СУБД закрывает все курсоры, связанные с SQL-выражением, сразу после завершения DML-операции.

Пример из официальной документации:

CREATE TABLE employees_temp AS SELECT * FROM employees;
BEGIN
  UPDATE employees_temp 
     SET salary = salary * 1.05 
   WHERE salary < 5000;

  DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || 
                       ' salaries.');
END;
/

Решил попробовать прицепить подсветку синтаксиса от Ивана Салагаева. Посмотрим как там чего.
UPD: не получается...
UPD: получилось!

С уважением,
Дмитрий Богомолов

[напоминалка] Ошибка в скрипте создания базы с помощью DBCA

2008-10-23T09:22:00.003+04:00

В некоторых случаях Oracle Database Configuration Assistant (DBCA) при формировании скрипта по созданию базы создает некорректный батник [sid_name.bat]. Я натыкался на эту ошибку и в версии 8.1.7 и в различных 9.2.0.х. Две строки имеют вид:

[Ljava.lang.String;@2a369f
[Ljava.lang.String;@2a36d7

При этом коды ошибок могут отличаться, что затрудняет поиск быстрого решения (по крайней мере в первый раз).

На самом деле все просто до безобразия... Вот эти строки:

oradim.exe -new -sid [sid_name] -startmode m
oradim.exe -edit -sid [sid_name] -startmode a

Само собой разумеется, что вместо [sid_name] везде необходимо подставить имя базы, которую Вы создаете.

[Напоминалка] Materialized View и Database Link

2008-10-15T10:47:00.003+04:00

Необходимо запомнить такой момент:

При создании Materialized View из выборки, в которой используется обращение к удаленной базе через DBLink (неважно, непосредственно или через синоним), необходимо использовать ключ "WITH ROWID" либо "WITH PRIMARY KEY" иначе получаем ORA-00942. Почему эту ошибку, объяснения не вижу даже на горизонте, но... В общем надо принять как данность.

[напоминалка] Перевод строки в Oracle

2008-08-28T11:41:00.004+04:00

Надо запомнить!!!

Перевод строки в Oracle выполняется лишь 10м символом, независимо от платформы! А ведь промучался с динамическим PL\SQL-кодом 3 дня! Думал сначала на права, потом на свои руки, потом на индусов, потом опять на свои руки, а вот в результате оказалось, что вместо chr(13)||chr(10) надо было ставить просто chr(10). И все, джобы с динамически сформированным кодом заработали.

Oracle Metalink меняет пользовательский интерфейс

2008-07-30T15:47:00.002+04:00

Сегодня тест-драйв http://csm.oracle.com/

Хорошая новость(имхо, старый приелся уже да и не очень удобно раньше был продуман).

Новый - удобен, красиво сделано.

В общем приятное впечатление. Ждем выхода в народ.

Генерация DDL объектов базы данных

2008-05-23T10:57:00.003+04:00

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


SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
  FROM DBA_OBJECTS t
 WHERE t.object_type = 'TABLE'
   AND OWNER = 'DAD'

P.S: Извиняюсь за большую задержку с заметками про различные IDE для дизайна баз данных, на этих выходных заканчиваю переезд на новую квартиру и обязательно найду немного времени для того, чтобы откопать дипломный проект любимой жены в которой мы совместно проводили сравнительный анализ BP/ERwin, Rational Rose, ARIS, JDeveloper и еще чего-то там. Работе уже 3 года, но большую часть можно выложить.

Не удается удалить бэкапы?

2008-05-21T13:04:00.005+04:00

Всем доброго дня!

Сегодня столкнулся с такой проблемой, не удаляются старые, вернее даже устаревшие, бэкапы. Топаю в RMAN, набираю "DELETE OBSOLETE;" и получаю...

RMAN-03002: failure of delete command at 21/05/2008 10:37:34
RMAN-00600: internal error, arguments [8225] [INVALID] [] [] []

Думал, Google мне чего-нибудь подскажет, но он усиленно молчал на счет этой проблемы. Тогда полез на Metalink и обнаружил что ~~у нас снова появился к нему доступ~~ к этой проблеме есть workaround. Проблема оказалась в архивах редо логов.

В наличии имеется баг Bug 4612903 и следующее решение (надеюсь, не нанесу компании Oracle грандиозного урона перепечаткой двух команд):

1. Бэкапим все архивлоги с опцией delete input, смысл в том, чтобы.
2. Удаляем устаревшие (obsolete), можно с опцией noprompt.

Проектирование: CA ERwin Data Modeler

2008-04-29T15:35:00.004+04:00

Всем доброго дня!

Сегодня хотелось бы обратиться не непосредственно к программированию в Oracle, а к средам проектирования баз данных и не только. Ведь дизайн БД напрямую зависит от архитектуры, дизайна (забудем пока про пользовательский интерфейс) самой разрабатываемой системы. Понятное дело, что у нас, в России, до сих пор большинство систем пишется "на коленке" с проектированием лишь в мыслях. Однако многие переходят либо уже перешли к полноценному проектированию. Начну серию записей о CASE-средствах. Итак...

CA ERwin Data Modeler

Очень удобная штуковина, особенно в случаях, когда процесс проектирования информационной системы начинается с анализа бизнес-процессов.
На первом этапе анализа/проектирования используется CA ERwin Process Modeler. Если была введена достаточная информация о данных, которыми обмениваются бизнес-процессы, то ERwin Data Modeler сможет создать "почти готовую к использованию" логическую модель данных. Логическая модель будет представлять собой денормализованную структуру.
На основе логической модели данных формируется физическая модель данных. Данная среда сама произведет частичную нормализацию структуры (избавится от отношений многие-ко-многим и т.п.), предположит, какие типы данных лучше использовать в соответствии с используемой СУБД, расставит индексы и многое многое другое... Можно указывать даже способы физического размещения в соответствии с используемыми опциями.
Возможна синхронизация со структурой в существующей базе данных, имеется обратная взаимосвязь между физической и логической структурой, возможен подход с обратным инжинирингом.

Недостатки и проблемы в использовании?
Эстетические: некрасивые имена для констрейнтов, индексов и прочего, что создается автоматом, никакого соответствия с именем объекта.
Практические: создается очень много констрейнтов, поэтому надо подходить к проектированию аккуратно дабы потом не отключать пару десяткой уникальных индексов, проверок, и прочих бонусов, обеспечивающих целостность данных.

Сайт разработчика: CA.

Далее:

Rational Rose
Enterprise Architect
Oracle JDeveloper
Oracle Designer
Sybase PowerDesigner
DeZign от Datanamic
Embarcadero ER/Studio

А что используете вы?

Расчет разницы между датами

2008-04-25T15:50:00.007+04:00

Наткнулся сегодня на заметку на Oracle Tips & Tricks со схожим названием. В заметке рассматривается забавный нюанс работы с типом Date в СУБД Oracle. Рассмотрим этот нюанс, так сказать, на русском.

Банальность.
Как многие помнят, большинство сред хранит дату как разницу между этой самой датой и некой опорной датой, в виде числа. Таким образом 01.04.2008-3.111 будет представляться пользователю, конечно, как 28.03.2008 21:20:10, однако храниться дата будет с большей точностью. Т.е. при выполнении действия 01.04.2008-3.111-01.04.2008 мы получим -3.111.

Особенности.
При работе в СУБД Oracle необходимо отметить такие нюансы:

Числовые типы данных хранятся в десятичном формате.
Точности большей чем до секунд добиться при использовании типа DATE в СУБД Oracle невозможно.

Вернемся к примеру, описанному выше.

Пример.

SQL> select to_date('01.04.2008')-3.111 from dual;

TO_DATE('01.04.2008')-3.111
---------------------------
28.03.2008 21:20:10

SQL> select to_date('01.04.2008')-3.111-to_date('01.04.2008') from dual;

TO_DATE('01.04.2008')-3.111-TO
------------------------------
          -3,11099537037037

Проблема? Нет, особенность!
Рассмотрим 3.111 в виде дельты между датами. Что мы получим?
3.111*24*60*60 = 3 дня 2 часа 39 минут 50.4 секунды
При преобразовании к дате мы теряем те самые 0.4 секунды и получаем дельту3 дня 2 часа 39 минут 50 секунды. Соответственно при обратном пересчете мы получим ((50/60+39)/60+2)/24+3 = 3.11099537037037. Так что вот так.

Будьте аккуратны с неявным преобразованием данных!

Toad for Oracle 9.6

2008-04-23T21:40:00.001+04:00

Обнаружил, что на подходе (7 мая 2008 США ) версия 9.6 сего замечательного инструмента.

Кстати, вопрос: - Кто, какими инструментами пользуется в работе с любимой СУБД?

Что используют администраторы базы которые живут в линуксе?

Поправка - не считая родных решений Оракла.

Для 10-ки есть браузерная версия Enterprise Manager для 9-ки - нет, зато у каждого админа есть куча скриптов, помогающих в жизни. Захотелось не обычного типа ЕМ для 9.

Погуглил я в поиске ответа на вопрос: "Какие инструменты для администрирования есть на просторах Open source"?

Нашел ряд проектов 2/3 из которых уже не жильцы. phpOraAdmin - довольно скудный набор функций. Для мониторинга есть ZABOra от Zabbix .

Неужели никому не интересно иметь под рукой настраиваемый под свои нужды "тонкий" клиент Оракла? Или уже все ушли в 10 и 11 версии?

Я занялся изучением PHP и Ajax и захотелось сделать что-то аналогичное.

Как думаете есть смысл написать такое приложение?
Или не стоит изобретать велосипед?

Oracle залатала четыре десятка дыр в своих продуктах

2008-04-20T14:42:00.003+04:00

На сайте Oracle опубликован очередной бюллетень Critical Patch Update с информацией о новых уязвимостях, обнаруженных в программных продуктах компании.
На этот раз Oracle устранила около четырех десятков дыр в различных пакетах. Уязвимости, в частности, выявлены в сервере приложений Oracle Application Server, СУБД Oracle Database, в комплексе бизнес-приложений Oracle E-Business Suite, CRM-продукте Oracle Siebel Enterprise Suite, а также в комплексе средств для централизованного управления системами Oracle Enterprise Manager.
Наиболее опасные дыры найдены в сервере Oracle Application Server и пакете Oracle E-Business Suite. Эти уязвимости теоретически могут использоваться злоумышленниками с целью получения несанкционированного доступа к системе жертвы. Кроме того, дыры, допускающие удаленный доступ к ПК без аутентификации, устранены в продукте Oracle Siebel Enterprise.
В состав пакета обновлений, помимо заплаток, включены ряд апдейтов, не связанных с безопасностью. Дополнительную информацию об устраненных уязвимостях можно найти здесь. Следующая порция патчей, согласно графику Oracle, должна быть выпущена 15 июля 2008г.