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

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

noreply@blogger.com (Дмитрий Богомолов) — Mon, 07 Nov 2011 04:42:50 PST

Предыстория
Мигрировали базу на новый сервер одновременно с обновлением версии 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

noreply@blogger.com (Дмитрий Богомолов) — Fri, 28 Oct 2011 01:19:31 PDT

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

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

noreply@blogger.com (Дмитрий Богомолов) — Wed, 21 Sep 2011 02:38:25 PDT

Многие из тех, кому по той или иной причине приходится администрировать СУБД 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.

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

noreply@blogger.com (Дмитрий Богомолов) — Wed, 01 Jun 2011 04:07:42 PDT

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 в разных ситуациях

noreply@blogger.com (Дмитрий Богомолов) — Mon, 18 Apr 2011 07:08:04 PDT

А знаете ли вы что... функция 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

noreply@blogger.com (Дмитрий Богомолов) — Thu, 14 Apr 2011 08:08:06 PDT

В феврале 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?

noreply@blogger.com (Дмитрий Богомолов) — Wed, 13 Apr 2011 00:04:22 PDT

Сегодня при изучении 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

noreply@blogger.com (Дмитрий Богомолов) — Fri, 26 Feb 2010 23:07:06 PST

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

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

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

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

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

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

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

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

noreply@blogger.com (Дмитрий Богомолов) — Tue, 07 Jul 2009 04:41:30 PDT

Была у меня когда-то заметочка на тему подъема БД в Краевом Суде:
Неспящие админы на службе у Краевого Суда. Но все дело в том, что история не то чтобы повторилась, но восстанавливать базу пришлось там же. Теперь уже была версия 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

noreply@blogger.com (Дмитрий Богомолов) — Thu, 21 May 2009 02:44:45 PDT

Теперь проверял знания 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!

noreply@blogger.com (Дмитрий Богомолов) — Mon, 20 Apr 2009 06:38:55 PDT

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

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

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

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

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

noreply@blogger.com (Дмитрий Богомолов) — Thu, 12 Mar 2009 23:10:01 PDT

Наверняка у многих возникала необходимость внутри 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

noreply@blogger.com (Дмитрий Богомолов) — Thu, 23 Oct 2008 03:39:33 PDT

В некоторых случаях 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

noreply@blogger.com (Дмитрий Богомолов) — Wed, 15 Oct 2008 00:11:45 PDT

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

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

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

noreply@blogger.com (Дмитрий Богомолов) — Wed, 15 Oct 2008 00:12:11 PDT

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

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

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

noreply@blogger.com (Igogo) — Thu, 16 Oct 2008 03:16:59 PDT

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

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

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

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

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

noreply@blogger.com (Дмитрий Богомолов) — Thu, 12 Mar 2009 05:43:33 PDT

Можете кидать в меня тухлыми яйцами, но я только полчаса назад открыл для себя встроенную процедуру генерации 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 года, но большую часть можно выложить.

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

noreply@blogger.com (Дмитрий Богомолов) — Wed, 21 May 2008 05:06:33 PDT

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

Сегодня столкнулся с такой проблемой, не удаляются старые, вернее даже устаревшие, бэкапы. Топаю в 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

noreply@blogger.com (Дмитрий Богомолов) — Wed, 21 May 2008 05:07:06 PDT

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

Сегодня хотелось бы обратиться не непосредственно к программированию в 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

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

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

noreply@blogger.com (Дмитрий Богомолов) — Thu, 12 Mar 2009 05:44:39 PDT

Наткнулся сегодня на заметку на 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

noreply@blogger.com (Igogo) — Wed, 12 Nov 2008 22:52:21 PST

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

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

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

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

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

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

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

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

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

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

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

noreply@blogger.com (Dinka) — Mon, 21 Apr 2008 12:01:14 PDT

На сайте 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г.

Пользовательские функции аггрегации

noreply@blogger.com (Дмитрий Богомолов) — Thu, 12 Mar 2009 05:47:41 PDT

Встретил сегодня вопрос на SQL.RU "Как запросом получить произведение значений всех предыдущих строк?", и решил попробовать создать собственную функцию аггрегации. Задача на самом деле проще простого, но редко когда решаемая. Итак...

Вступление.
Все используют функции аггрегации, такие как MAX, MIN, AVG, SUM и прочие, которые выполняют действие над некоторым массивом данных. Однако время от времени возникает необходимость, скажем, не сложить, а перемножить все значения поля в выборке. В данном случае нам на помощь приходит интерфейс ODCIAggregate.

Краткое описание.
Интерфейс ODCIAggregate имеет 4 метода:

Initialize
Iterate
Merge
Terminate

Чем же они занимаются? По порядку:
1. Initialize. Данный метод выполняет определенные действия перед началом вычислений.
2. Iterate. Собственно метод, выполняющий операцию над очередным значением из массива.
3. Merge. Метод, необходимый в случае распараллеливания выполнения расчета, когда весь массив бьется на части, а потом расчитанные по отдельности они объединяются. Так вот этот метод выполняет объединение пары результатов.
4. Terminate. Метод, заканчивающий расчеты и выдающий результат.

Кроме того необходимо объявить функцию, которая будет вызывать расчет аггрегатной функции.

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

-- Создаем тип для вычислений
create type MultiImpl as object
(
multires NUMBER, -- результат
static function ODCIAggregateInitialize(sctx IN OUT MultiImpl)
 return number,
member function ODCIAggregateIterate(self IN OUT MultiImpl,
 value IN number) return number,
member function ODCIAggregateTerminate(self IN MultiImpl,
 returnValue OUT number, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT MultiImpl,
 ctx2 IN MultiImpl) return number
);
/

-- Создаем тело типа

create or replace type body MultiImpl is
-- Функция, выполняющая инициализацию объекта
static function ODCIAggregateInitialize(sctx IN OUT MultiImpl)
return number is
begin
sctx := MultiImpl(1);
return ODCIConst.Success;
end;

-- Функция, выполняющая расчет
member function ODCIAggregateIterate(self IN OUT MultiImpl, value IN number)
return number is
begin
  -- умножаем текущий результат на очередное значение
  self.multires := self.multires*value;
  return ODCIConst.Success;
end;

-- Функция, заканчивающая расчет
member function ODCIAggregateTerminate(self IN MultiImpl, returnValue OUT
number, flags IN number) return number is
begin
  returnValue := self.multires; -- Передаем результат в выходной параметр
  return ODCIConst.Success;
end;

-- На случай распараллеливания
member function ODCIAggregateMerge(self IN OUT MultiImpl, ctx2 IN MultiImpl) return number IS
begin
  -- Просто перемножаем результаты
  self.multires := self.multires*ctx2.multires;
  return ODCIConst.Success;
end;
end;
/

-- Ну и создаем функцию, которая будет вызывать аггрегацию
CREATE FUNCTION Multi (input NUMBER) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING MultiImpl;
/

Итог.
Теперь пробуем использовать то, что получилось:

SQL> SELECT multi(col)
2    FROM (SELECT 2 AS col
3            FROM dual
4          UNION ALL
5          SELECT 3 AS col
6            FROM dual
7          UNION ALL
8          SELECT 4 AS col
9            FROM dual
10          UNION ALL
11          SELECT NULL AS col
12            FROM dual
13          UNION ALL
14          SELECT 7 AS col
15            FROM dual
16          UNION ALL
17          SELECT 8 AS col FROM dual) t;

MULTI(COL)
----------
1344

Спасибо Apex за поправки.

Вставка в несколько таблиц

noreply@blogger.com (Дмитрий Богомолов) — Thu, 12 Mar 2009 06:10:14 PDT

"Multi table insert" или вставка одним запросом в несколько таблиц. Наверняка многим не хватало такой фишки, кто как реализовывал ее, но не знали, что это можно реализовать средствами Oracle.

Сегодня встретил заметку в блоге "System Engineering and RDBMS", в которой описывались конструкции, позволяющие делать вставки в несколько таблиц одним запросом. Сразу скажу, что все это возможно для версий 9i и выше.
Пример:

create table TAB1 (COL1 NUMBER(30), COL2 NUMBER(30) );

Table created.

create table TAB2 (COL3 NUMBER(30), COL4 DATE);

Table created.

CREATE SEQUENCE TEST_SEQ INCREMENT BY 1;

Sequence created.

insert
when mod( object_id, 2 ) = 1 then
into TAB1 ( COL1, COL2 ) values ( test_seq.nextval, object_id )
when mod( object_id, 2 ) = 0 then
into TAB2 ( COL3, COL4 ) values ( test_seq.nextval, created )
select object_id, created from all_objects
/

Т.е. между INSERT и запросом с исходными данными у нас идет конструкция:

Двумя словами можно описать следующим образом (для тех, у кого плохо с чтением подобных карт):
По некому условию (в качестве параметров условия можно брать значения из исходного запроса) мы делим вставку на несколько, т.е. в зависимости от исходных данных формируем отдельные вставки в разные таблицы либо в одну таблицу но, допустим, в разном формате. Кроме того, мы можем по одному условию делать вставки в несколько таблиц:

...
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
...

(из примера в документации)
Еще один важный момент, сразу после INSERT указывается ALL (по-умолчанию) либо FIRST, в первом случае все условия проверяются и выполняются все вставки, результат вычисления значений у которых TRUE, во втором варианте проверяются все условия в порядке их указания в запросе до первого FALSE. Вставка, указанная в ELSE, будет выполнена если не выполнено ни одно условие.
Еще несколько примеров из доки:

INSERT ALL
  WHEN order_total <> 1000000 AND order_total <> 2000000 THEN
     INTO large_orders
  SELECT order_id, order_total, sales_rep_id, customer_id
     FROM orders;

Выполняет то же самое что и

INSERT ALL
  WHEN order_total <> 1000000 AND order_total < 2000000 THEN
      INTO medium_orders
   ELSE
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;

А следующий пример вставляет заказы больше 2900000 в таблицу special_orders, но не вставляет в large_orders:

INSERT FIRST
  WHEN ottl <> 1000000 and ottl <> 2900000 THEN
     INTO special_orders
  WHEN ottl > 2000000 THEN
     INTO large_orders
        VALUES(oid, ottl, sid, cid)
  SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
     o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
     FROM orders o, customers c
     WHERE o.customer_id = c.customer_id;

Изменения в пакете FTP

noreply@blogger.com (Дмитрий Богомолов) — Wed, 21 May 2008 05:09:01 PDT

Сегодня вдруг вспомнил и решил посмотреть, был ли эффект от письма по поводу истории с таймаутами в пакете FTP, предложенном Тимом Холлом. И заметил, что мои поправочки включены в выложенный на www.oracle-base.com вариант.

P.S: немного разгреб дела, скоро сделаю парочку технических заметок. Ожидайте.