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

Всем привет.

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

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

PS: edited: добавил в конце баг о котором забыл написать сразу (использование в PL/SQL, SQL-конструкций в WITH которых испоьзован PL/SQL).

Знакомим 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

PPS: добавлено позже (вспомнил ещё)

PL/SQL support in SQL with in PL/SQL

Название — масло масляное. Давайте разберёмся.
Похоже, PL/SQL пока не в курсе о расширении SQL-языка, и пока не поддерживает таких SQL конструкций:

SQL> DECLARE
  dummy NUMBER;
BEGIN
  WITH FUNCTION test_with (n_id IN NUMBER) RETURN NUMBER IS
    BEGIN dbms_output.put_line(n_id); RETURN n_id; END;
   SELECT test_with(ID)
     INTO dummy
     FROM t1
   WHERE ROWNUM < 2;
   dbms_output.put_line(dummy);
END;
/
  WITH FUNCTION test_with (n_id IN NUMBER) RETURN NUMBER IS
                *
ERROR at line 4:
ORA-06550: line 4, column 17:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 4:
PLS-00103: Encountered the symbol "SELECT"

При этом нормально работает в dynamic SQL (ожиданно, но всё-же):

DECLARE
  dummy NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'WITH FUNCTION test_with (n_id IN NUMBER) RETURN NUMBER IS
    BEGIN dbms_output.put_line(n_id); RETURN n_id; END;
   SELECT test_with(ID)
     FROM t1
   WHERE ROWNUM < 2' INTO dummy;  
END;

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