Oracle 12c. Новые фичи приносят новые подводные камни

Всем привет.

Разбирая новые возможности Oracle 12c, то тут, то там сталкиваюсь с подводными камнями, когда не всё работает так, как ожидалось, падает или просто не очевидно. Конечно, это стандартная ситуация когда в первом релизе новой версии много сырого… но, как известно, предупреждён — значит вооружен. Вероятно, кому-то пригодится, чтобы не повторять мои грабли.

Пишу грабли в порядке наступления на них.

Знакомим PMON с локальным listener

Начнём с простого, что даже не совсем бага, а вопрос конфигурирования.
Инсталлировали в стандартной установке и настройке Oracle 12c (в моём случае — на Oracle Linux, но, думаю, не критично).
— Создаём CDB базу.
— Поключаем/создаём PDB-базы.
По-умолчанию добавленные базы не видится listener-ом. «Из коробки» PMON не подружился с listener-ом, живущим на том же сервере и автоматическая регистрация не происходит. Не трагедия, однако руками добавлять записи в listener.ora, как-то совсем не интересно в контексте новой фичи multitenant architecture.
Давайте познакомим PMON и локально живущий listener:

ALTER SYSTEM SET local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1521))' scope=both; ALTER SYSTEM REGISTER; 

База зарегистрировалась, listener увидел, радость наступила. Нельзя сказать что это особенность именно 12-ки, но именно в ней появилась возможность буквально на ходу добавлять / управлять базами, и в рамках Multitenant architecture автоматическая регистрация баз в listener, как никогда актуальна. Раньше создание новой базы было намного большим «событием» и добавить пару строк в listener.ora не вызывало у меня каких-либо предубеждений.

Автозапуск PDB после перезагрузки

Перезапускаем сервер. CDB поднялась. PDB — не открыты (при подключении ошибка database shutdown or startup in progress).
Не знаю, может это и правильно, что после перезагрузки сервера администратор должен подключиться к CDB базе и сказать

alter pluggable database all open; 

Вроде как просто так базы не перезагружаются (не должны)… Но что-то мне подсказывает, что это, мягко говоря, не удобно. А DBA, которые поддерживают десятки и сотни баз одновременно — явно спасибо не скажут.
Настройки или команды которая бы убедила Оракл что автостартовать PDB-шки всё таки нужно, не обнаружилось (может плохо искал. Подскажите в коммантариях если кто нашёл).
Во всезнающем интернете этот момент уже давно не новость и наиболее распространённая рекомендация:

create or replace trigger open_all_pdb after startup on database  BEGIN       execute immediate 'alter pluggable database all open';  END open_all_pdbs; 

По личным убеждениям я не очень люблю триггеры, но в данном случае, это, похоже, самое малое из доступных зол.

Invisible columns

Особенность не Оракла, а PL/SQL developer-а (версия10.0.1.1694 — скачан с сайта PL/SQL Developer-а буквально недавно), но всё же.
Сравним поведение на невидимой колонке sqlplus (ведёт в соответсвии с документацией):

SQL> descr test_invisible;  Name                     Null?    Type  ------------------ -------- -----------  ID                             NUMBER(38)  THIRD_COL                     NUMBER(38) 

И PL/QSL Developer command window:

SQL> descr test_invisible; Name      Type         Nullable Default Comments  --------- ------------ -------- ------- --------  ID        INTEGER      Y                          THIRD_COL INTEGER      Y                          INV_COL   VARCHAR2(20) Y                          

Не знает пока что Pl/Sql developer про новую фичу, что не удивительно. Но не все осознают что command window у PL/SQL-девелопера — это не честный sql*plus через какой-нибудь pipe, а просто псевдо-подобный интерфейс.
Думаю скоро образумятся, но в первый момент несколько удивился и задумался.

PL/SQL support in with

Как утверждает Оракл, эта фича была сделана в первую очередь для поднятия производительности (детальное рассмотрение фичи оставим за скобками, ибо оффтопик к теме поста), как и умолчим, что pragma UDF работает в этих целях не хуже, но…
«НО» заключается в баге обнаруженном Johnathan Lewis и описанном в его блоге.
Добавиви одну performance «фичу» поламали (в некоторых случаях) — другую — DETERMINISTIC.

Рассмотрим на примере кода:

-- Создали для примера таблицы с данными только из единичек. CREATE TABLE all_ones AS SELECT 1 AS ID FROM dual CONNECT BY LEVEL<100;  SET TIMING ON ARRAYSIZE 15  WITH   FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS   BEGIN DBMS_LOCK.sleep(1); RETURN p_id; END; SELECT slow_function(ID) FROM   all_ones WHERE  ROWNUM <= 10; / …. 10 rows selected. Elapsed: 00:00:10.02 

Хотя, справедливости ради, проявляется это не во всех случаях:

WITH FUNCTION slow_function(p_id IN NUMBER)  RETURN NUMBER DETERMINISTIC IS BEGIN    DBMS_LOCK.sleep(1);    RETURN p_id;  END; SELECT slow_function(1) FROM   all_ones WHERE  ROWNUM <= 10; / 10 rows selected. Elapsed: 00:00:01.01  WITH FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN    DBMS_LOCK.sleep(1);    RETURN p_id;  END; SELECT (SELECT slow_function(id) FROM dual) FROM   all_ones WHERE  ROWNUM <= 10; / Elapsed: 00:00:01.02 

SQL Text expansion

Ещё одно приятное нововведение — новая процедура DBMS_UTILITY.EXPAND_SQL_TEXT — я её уже описывал раньше на хабре.
Когда её испытывал, она замечательно отработала как на моих view и таблицах с VPD…, так и к примеру, на all_users… однако попытка применить её к all_objects привела к ошибке в пакете dbms_utility. Предполагаю, причина в том, что даже у пользователя с ролью DBA не обнаружилось доступа к каким-то совсем внутренним системным объектам… а может просто баг в коде.

DECLARE x CLOB; BEGIN   dbms_utility.expand_sql_text(input_sql_text => 'select * from all_objects', output_sql_text => x );   dbms_output.put_line(x); END; ORA-00904: : invalid identifier ORA-06512: at "SYS.DBMS_UTILITY", line 1581 ORA-06512: at line 3 

И вот ещё пара вещей, с которыми столкнулся не сам, но тоже было интересно почитать у других:

DBMS_METADATA and session sequence

Найдено в одной из тем на sql.ru.
Похоже, DBMS_METADATA пока что не в курсе про новую фичу:

-- создадим сессионный sequence create sequence seq session;  DECLARE   x CLOB; begin   x:=dbms_metadata.get_ddl( 'SEQUENCE', 'SEQ');   dbms_output.put_line(x); end; /  -- Получаем обычный seqence, без аттрибута session. Так вот. CREATE SEQUENCE  "DENKREP"."SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOPARTITION; 

Pagination, массивы и run-time расчёт количества строк для fetch

Нашёл у человека в блоге

 SQL> declare   2      type table_tt is table of employees%rowtype;   3      v_tt table_tt;   4   5      v_limit_nr number:=10;   6      v_counter_nr number:=0;   7  begin   8      select *   9      bulk collect into v_tt  10      from employees  11      offset v_counter_nr*v_limit_nr rows  12      fetch next v_limit_nr rows only; -- variable   13  end;  14  /     type table_tt is table of employees%rowtype;                               * ERROR at line 2: ORA-03113: end-of-file on communication channel Process ID: 3060 Session ID: 20 Serial number: 35307 SQL>  -- Причём если прописать количество получаемых строк явно, то всё работает.   ...   12      fetch next 10 rows only; -- hard-code  13  end;  14  / PL/SQL procedure successfully completed. SQL> 

PS: В комментариях к источнику написали что о баге оракл уже знает и на металинке заведён bug #17404511

Пока что всё. Надеюсь было интересно.