12 апр. 2016 г.

Real-Time Database Operation Monitoring в Oracle Database 12c

Технология мониторинга SQL-запросов в режиме реального времени (Real-Time SQL Monitoring ), которая появилась в Oracle Database 11g, позволяет администраторам и разработчикам взглянуть внутрь каждого долго выполняющего запроса.
Для каждого долго выполняющегося запроса можно увидеть его реальный план выполнения, количество обработанных строк, расхождение ожидаемой и фактической кардинальности (количество ожидаемых и количество фактических строк) на каждом шаге плана запроса. Также можно увидеть различные метрики SQL-запроса: CPU, I/O requests, throughput, PGA, temp space.
В том числе можно производить мониторинг статистики ввода-вывода для каждого шшага запроса.

В свое время технология Real-Time SQL Monitoring была большим шагом вперед для ответа на вопрос "Что реально происходит в базе данных?".

Рис 1. Мониторинг выполнения запроса с помощью Real-Time SQL Monitoring

Вместе с тем, вышеописанная технология позволяет производить мониторинг только на уровне отдельного запроса. Очень часто необходимо отслеживать выполнение на уровне отдельных бизнес-операций в приложении. Поскольку бизнес-операция, в приложении состоит из нескольких запросов, приходиться вручную сопоставлять выполняющиеся запросы к конкретной бизнес-операции, например: "Расчет зарплаты". Особенно актуальной эта задача становится при возникновении проблем с производительностью кричичных для бизнеса операций.

Рассмотрим такой типичный случай.
При возникновении проблемы с производительностью расчета зарплаты, конечный пользователь обращается к администратору СУБД. DBA должен среды сотен (возможно и тысяч!) выполняющихся запросов, выделить запросы относящиеся к расчету зарплаты, выяснить какие из них сейчас выполняются и, затем, наконец, разобраться в причинах медленной работы этой бизнес-операции.
Для идентификации запросов часто приходится обращаться к разработчикам приложения и анализировать его исходный код. Все это часто приводит к взаимным обвинениям администраторов и разработчиков.
Не правда-ли: знакомая картина?

СУБД Oracle Database 12c предлагает решение и этой проблемы, позволяя производить мониторинг на уровне составных бизнес-операций.
Эта технология получила название Real-Time Database Operation Monitoring, и позволяет осуществлять мониторинг в режиме реального времени в терминах бизнес-процессов (бизнес-операций) выполняющихся в данный момент в СУБД.
Очевидно, что СУБД ничего "не знает" про бизнес-процессы, и для этого приложению необходимо каким-либо образом сообщить об этом базе данных: сгруппировать обращения к СУБД в бизнес-операции.

Для того, чтобы выделить в потоке обращений к СУБД бизнес-операцию, предлагается специальный API (Application Program Interface), реализованный в виде PL/SQL-пакета DBMS_SQL_MONITOR.
Пакет DBMS_SQL_MONITOR стандартно поставляется в составе СУБД и устанавливается по умолчанию - то есть входит в состав стандартных системных пакетов Oracle Database 12с.
Для выделения начала бизнес-операции предназначена процедура BEGIN_OPERATION данного пакета, для окончания бизнес-операции - END_OPERATION.

Возвращаясь к вышеописанному примеру с расчетом заработной платы, ее мониторинг в исходном коде хранимой PL/SQL-процедуры может быть оформлен следующим образом:
 CREATE PROCEDURE calc_salary(v_pDep IN NUMBER) IS
   v_xDBOpId          NUMBER;
 BEGIN
   v_xDBOpId := dbms_sql_monitor.begin_operation('Расчет зарплаты',
                                                 forced_tracking => 'Y');
   prepare_calc();
   main_calc();
   finish_calc();

   dbms_sql_monitor.end_operation('Расчет зарплаты', v_xDBOpId); 
 END;
Параметр forced_tracking процедуры BEGIN_OPERATION аналогичен хинту MONITOR в запросе: при его установке (forced_tracking => 'Y'), мониторинг бизнес-операции производится в любом случае. В противном случае - мониторинг производится только в случае, если бизнес-операции по продолжительности выполнения занимает 5 и более секунд ЦПУ или ввода-вывода. Запустим наш модифицированный бизнес-процесс расчета зарплаты и посмотрим, как он теперь отображается на экране SQL Monitoring консоли администрирования Enterprise Manager 12c. В списке выполняющихся запросов появился запрос c "говорящим" именем "Расчет зарплаты":

Рис 2. "Расчет зарплаты" в списке запросов на экране SQL Monitoring

Перейдя внутрь выполняющейся бизнес-операции - для этого нужно кликнуть мышью на SQL_ID операции (в нашем случае: "Расчет зарплаты"), можно увидеть страницу детальной информации: какие запросы из состава бизнес-операции уже выполнились, и какой запрос является текущим - выполняется в данный момент:

Рис 3. Детальная информация о выполнении бизнес-операции

"Провалившись" внутрь любого запроса в бизнес-операции, можно увидеть знакомый по предыдущим версиям экран SQL-мониторинга запросов.

После завершения бизнес-операции входящие в него запросы точно также доступы для анализа, как это и было раньше (в СУБД Oracle Database 11g) для уже завершившихся SQL-запросов.
Рис 4. Бизнес-операция "Расчет зарплаты" завершена

Для того, чтобы сгруппировать запросы в бизнес-операции из приложений Java и С++/C-приложений, соответствующий клиентский API доступа к БД (JDBC и OCI, соответственно) также был расширен.
Вот так, например, выглядит выделение бизнес-операции в Java-приложении:

Connection conn = DriverManager.getConnection(myUrl, myUsername, myPassword);
conn.setClientInfo("E2E_CONTEXT.DBOP", “Расчет зарплаты");
Statement stmt = conn.createStatement();
stmt.execute(v_xSQLQuery1);
... ... ... ...
... ... ... ...

conn.setClientInfo("E2E_CONTEXT.DBOP", null);
Если Вы разрабатываете приложение сразу для разных версий СУБД Oracle Database, то можете воспользоваться условной компиляцией PL/SQL, чтобы иметь один и тотже код в независимости от версии БД:
 create procedure calc_salary(v_pDepartmentId in number) is
    v_xDBOpId          NUMBER;
  begin
    $if dbms_db_version.ver_le_11_2 $then

    $else
      v_xDBOpId := dbms_sql_monitor.begin_operation('Расчет зарплаты',
                                                    forced_tracking => 'Y');
    $end
  
    v_gCurrentDepartmentId := v_pDepartmentId;
  
    prepare_calc();
    main_calc();
    finish_calc();
    
    $if dbms_db_version.ver_le_11_2 $then
    $else 
      dbms_sql_monitor.end_operation('Расчет зарплаты', v_xDBOpId);  
    $end
  end;
Объявление переменной v_xDBOpId нет смысла обрамлять в символ условной компиляции,поскольку при компиляции в СУБД Oracle Database 11g, PL/SQL-оптимизатор удалит её из исходного кода, поскольку она является "мертвой" - нигде не используется.

Продолжение следует ...

2 мая 2015 г.

Новые возможности в динамическом SQL в Oracle 12c (передача переменных PL/SQL типов)

И, наконец, последняя новая возможность в динамическом SQL, которая появилась в Oracle Database 12c.

Последняя, не по значимости, а по порядку изложения!
Стоит отметить, что она имеет очень большую практическую ценность, - и, по большому счету, ее очень не хватало.

Существенным ограничением до Oracle Database 12c являлась невозможность передачи в динамический код переменных, которые имеют PL/SQL-типы.
Если рассмотреть нижеприведенный анонимный блок, выполненный в среде Oracle Database 11g (11.2.0.4):
[oracle@localhost]$ sqlplus rscott/rtiger

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 3 00:07:27 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DECLARE
  b BOOLEAN;
BEGIN
  EXECUTE IMMEDIATE
    'begin :x := true; end;'
  USING
    OUT b;
END;
/
    OUT b;
        *
ERROR at line 7:
ORA-06550: line 7, column 9:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

SQL> 
можно увидеть, что ошибка при передаче в динамический блок кода возникает даже когда переменная типа BOOLEAN передается именно в PL/SQL-блок.

Очевидно, что исполняющая среда PL/SQL предполагает, что исполняться будет именно SQL-выражение.

Для того, чтобы обойти это ограничение, приходилось применять вот примерно такой дорогостоящий workaround, связанный с преобразованиями значения из PL/SQL-типа в SQL-тип, и затем обратно:

SQL> create function CharToBoolean(v_pValue in char) return boolean is
begin
  if v_pValue = 'Y' then
    return true;
  end if;

  return false;
end;
/

Function created.

SQL> create function BooleanToChar(v_pValue in boolean) return char is
begin
  if v_pValue then
    return 'Y';
  end if;

  return 'N';
end;
/

Function created.

SQL> DECLARE
  b BOOLEAN;
  s char(1);
BEGIN
  EXECUTE IMMEDIATE
    'begin :x := BooleanToChar(true); end;'
  USING
    OUT s;

  b := CharToBoolean(s);
END;
/
PL/SQL procedure successfully completed.

SQL> 
Конечно-же, это усложняло понимание кода и дальнейшее его сопровождение.

Наконец-то, это ограничение снято в Oracle Database 12c, и приведеный выше первоначальный код теперь выполняется без ошибок:

[oracle@localhost Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 3 00:10:28 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> DECLARE
  b BOOLEAN;
BEGIN
  EXECUTE IMMEDIATE
    'begin :x := true; end;'
  USING
    OUT b;
END;
/

PL/SQL procedure successfully completed.

SQL> 
Причем теперь, стало возможным передавать в динамический блок кода не только переменные типа BOOLEAN, но также PL/SQL-записи и PL/SQL-массивы:
SQL> create or replace package Account_service is
  type
    TArrayOfVarchar2 is table of Varchar2(32) index by pls_integer;

end;
/
Package created.

set serveroutput on
DECLARE
  v_xArray Account_service.TArrayOfVarchar2;
BEGIN
  v_xArray(1) := 'First'; v_xArray(2) := 'Second';

  EXECUTE IMMEDIATE
    'begin
       for v_xIndex in 1..:v_xCount
       loop
         dbms_output.put_line(:v_pArray(v_xIndex));
       end loop;
     end;'
  USING
    IN v_xArray.Count,v_xArray;
END;
/

First
Second

PL/SQL procedure successfully completed.
 
SQL>
Также поддерживается ситуация, когда передача параметра, имеющего PL/SQL, производится в SQL-выражение:
SQL> create or replace function printArray(v_pArray in Account_service.TArrayOfVarchar2) return number is
begin
  for v_xIndex in 1..v_pArray.count
  loop
    dbms_output.put_line(v_pArray(v_xIndex));
  end loop;

  return 1;
end;
/

Function created.

SQL>DECLARE
  v_xArray Account_service.TArrayOfVarchar2;
  n        number; 
BEGIN
  v_xArray(1) := 'First'; v_xArray(2) := 'Second';

  EXECUTE IMMEDIATE
    'SELECT printArray(:v_pArray) FROM dual'
  INTO 
    n
  USING
    IN v_xArray;
END;
/

First
Second

PL/SQL procedure successfully completed.

SQL>
К сожалению, хэш-массивы (PL/SQL-массивы c INDEX BY VARCHAR2) пока не поддерживаются.

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

Поддержка передачи (и возвращения тоже!) в динамический SQL переменных имеющих PL/SQL-типы - это новая впечатляющая возможность Oracle Database 12c, которая позволяет упростить Ваш код, тем самым сделать его более читабельным и легким в сопровождении.

24 янв. 2015 г.

Новые возможности в динамическом SQL в Oracle 12c (возврат "неявного" курсора)

В некоторых СУБД есть возможность вернуть из хранимой функции непосредственно набор данных. Например, в СУБД MS SQL Server возможен такой код хранимой процедуры на T-SQL:
CREATE PROCEDURE my_proc1
AS
BEGIN
 SELECT * FROM accounts_amounts WHERE Amount >= 20000
END
Далее, клиентские библиотеки, на стороне приложения, могут такой "неявный" курсор прочитать.

Как вы хорошо знаете, такой возможности в СУБД Oracle Database нет: хранимая процедура не может вернуть просто запрос. Необходим возврат ссылки на курсор (ref cursor): через OUT-переменную, либо как результат возвращаемый хранимой функцией.

Например:
CREATE OR REPLACE PACKAGE my_types IS
  TYPE
   Taccounts_amounts_cur is ref cursor;
END;
/

CREATE PROCEDURE my_proc1(v_xRes in out my_types.Taccounts_amounts_cur) IS
BEGIN
 OPEN v_xRes FOR 
   SELECT * FROM accounts_amounts WHERE Amount >= 20000;
END;
/

В общем-то, отсутствие в СУБД Oracle Database возможности вернуть из хранимой процедуры просто курсор, никак не мешала разработчикам приложений.
Тем не менее, это значительно усложняло процедуру миграции приложений на СУБД Oracle Database, с тех СУБД, где такая возможность присутствовала.

В СУБД Oracle Database 12c такая возможность появилась!
Это, конечно, потребовало адаптации клиентских библиотек доступа в Oracle Client. Начиная с Oracle Database Client 12c приложения, которые используют любой интерфейс доступа к СУБД (Oracle Call Interface, ODP.Net, JDBC), могут получать из СУБД такие "неявные" курсоры. Собственно на стороне СУБД, чтобы вернуть такой курсор, необходимо использовать новую процедуру RETURN_RESULT из пакета DBMS_SQL.

Эта процедура перегружена, и присутствует в двух вариантах:
  • для возврата слаботипизированной ссылки на курсор -
    PROCEDURE RETURN_RESULT(rc IN OUT SYS_REFCURSOR, to_client IN BOOLEAN DEFAULT TRUE);
  • для возврата курсора отрытого с помощью динамического SQL в пакете DBMS_SQL -
    PROCEDURE RETURN_RESULT(rc IN OUT INTEGER, to_client IN BOOLEAN DEFAULT TRUE);
Параметр to_client определяет возможность возвращения курсора непосредственно сразу клиентcкому приложению, либо в другую хранимую процедуру PL/SQL. Хранимая процедура может вернуть таким образом не один курсор, а несколько - может вызвать процедуру RETURN_RESULT несколько раз. Определим хранимую процедуру, которая возвращает курсор подобным образом в клиентское приложение:
SQL> create or replace procedure test_dyn3 is
  v_xCursor    integer;
  v_xRowCount  integer;
begin
  v_xCursor := dbms_sql.open_cursor;

  dbms_sql.parse(c                  => v_xCursor,
                 statement          => 'select * from dual',
                 language_flag      => DBMS_SQL.NATIVE);

  v_xRowCount := dbms_sql.execute(c => v_xCursor);

  dbms_sql.return_result(v_xCursor,true);
end;
/

Procedure created.

Попробуем вызвать нашу процедуру в среде утилиты SQL*Plus:
SQL> exec test_dyn3;

PL/SQL procedure successfully completed.

ResultSet #1

D
-
X

SQL>
Как Вы можете убедиться: утилита SQL*Plus версии 12с "понимает" такой вид курсора, и просто выводит его содержимое на свою консоль.
Эта возможность появилась в версии 12с этой утилиты. Если курсор возвращается таким образом из динамического SQL, то чтобы его получить внутри вызываемого блока PL/SQL, предназначена еще одна новая процедура пакета DBMS_SQL - GET_NEXT_RESULT. Эта процедура также перегружена и имеет два варианта:
  • для получения слаботипизированной ссылки на курсор -
    PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT SYS_REFCURSOR);
  • для получения курсора отрытого с помощью динамического SQL в пакете DBMS_SQL -
    PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT INTEGER);;
Для того чтобы, иметь возможность получения таких "неявных" курсоров, вызов процедуры OPEN_CURSOR должен быть выполнен с новым параметром treat_as_client_for_results равным значению TRUE, по умолчанию значение этого параметра равно FALSE. Немного перепишем нашу процедуру test_dyn3 указав в функции возврата курсора значение параметра to_client равным FALSE:
SQL> create or replace procedure test_dyn3 is
  v_xCursor    integer;
  v_xRowCount  integer;
begin
  v_xCursor := dbms_sql.open_cursor;

  dbms_sql.parse(c                  => v_xCursor,
                 statement          => 'select * from dual',
                 language_flag      => DBMS_SQL.NATIVE);

  v_xRowCount := dbms_sql.execute(c => v_xCursor);

  dbms_sql.return_result(v_xCursor,false);
end;
/

Procedure created.
Теперь вызовем нашу процедуру через динамический SQL, и попробуем получить и вывести на экран содержимое курсора:
SQL> DECLARE
  v_xCursor          integer;
  v_xRowCount        integer;

  v_xReturnCursor    integer;
  v_xResult          dual.dummy%type;
  v_xReturnRefCursor sys_refcursor;
BEGIN
  v_xCursor := dbms_sql.open_cursor(treat_as_client_for_results => true);

  dbms_sql.parse(v_xCursor, 'BEGIN test_dyn3(); END;', dbms_sql.native);

  v_xRowCount := DBMS_SQL.EXECUTE(v_xCursor);
 
  dbms_sql.get_next_result(v_xCursor, v_xReturnCursor);

--Для удобства превращаем полученный курсор в ссылку на курсор (sys_refcursor)
  v_xReturnRefCursor := dbms_sql.to_refcursor(v_xReturnCursor);

  FETCH v_xReturnRefCursor 
    INTO v_xResult;
 
  dbms_output.put_line('Result: ' || v_xResult);
 
  dbms_sql.close_cursor(v_xCursor);
END;
/
Result: X

PL/SQL procedure successfully completed.

SQL>
Мы рассмотрели еще одну новую возможность Oracle Database 12c в области динамического SQL - возможность непосредственного возврата курсоров из хранимых процедур.
На мой взгляд, эта новая возможность в основном востребована при миграции приложений с других СУБД, которые уже поддерживают эту технологию, на СУБД Oracle Database. Поскольку в этом случае значительно снижается объем работ по адаптации кода приложения под СУБД Oracle.

9 янв. 2015 г.

Новые возможности в динамическом SQL в Oracle 12c (определение схемы по умолчанию)

Следующая новая возможность в динамическом SQL тоже связана с процедурой PARSE пакета DBMS_SQL.
До Oracle Database 12c, если в тексте запроса или анонимного PL/SQL-блока в имени объекта не была явна указана схема, то будет использована текущая схема.
Для программных единиц PL/SQL (пакетов, процедур, функций и т.д.) созданных с правами создателя, это будет схема в которой происходила компиляция, для созданных с правами вызывающего - схема пользователя, под которым происходит вызов динамического SQL.

Начиная с Oracle Database 12c, можно четко контролировать схему в имени объекта, для которых она была не указана в динамическом SQL:
[oracle@rac1 ~]$ sqlplus rscott/rtiger

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 9 16:24:34 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management options

SQL> create or replace package body account_service as

  procedure deleteAccount(v_pId in number) is
    v_xCursor    integer;
    v_xRowCount  integer;
    v_xPLSQL_Str varchar2(1600);
  begin
    v_xPLSQL_Str := 'delete from accounts where id = :v_pId';

    v_xCursor := dbms_sql.open_cursor;
  
    dbms_sql.parse(c                  => v_xCursor,
                   statement          => v_xPLSQL_Str,
                   language_flag      => DBMS_SQL.NATIVE,
                   schema             => 'DATA_OWNER');
    dbms_sql.bind_variable(v_xCursor, ':v_pId', v_pId);       

    v_xRowCount := dbms_sql.execute(c => v_xCursor);

    dbms_sql.close_cursor(c => v_xCursor);    
  end;

end;
/

Package body created.
Важно отметить, что параметр SCHEMA в процедуре PARSE отвечает лишь за имя схемы, в которой происходит поиск объекта при парсинге. Выполнение динамического SQL происходит с правами той программной единицы PL/SQL, в которой происходит вызов пакета DBMS_SQL - с правами создателя, либо с правами вызывающего.

2 янв. 2015 г.

Новые возможности в динамическом SQL в Oracle 12c (выполнение кода в PDB)

Говоря о новых возможностях Oracle Database 12c, нельзя не упомянуть динамический SQL.
В этой области тоже появилось много новых и интересных возможностей.

  1. Выполнение кода в контексте произвольной PDB-базы
Безусловно, самой главной новой технологией, которая пришла вместе с Oracle 12c, являются мультиарендная архитектура баз данных (Multitenant database).
Поддержка контейнерной архитектуры не могла пройти мимо динамического SQL: в пакете DBMS_SQL появилась возможность выполнить произвольный код в контексте указанной подключаемой базы данных (PDB). В процедуре PARSE появился новый параметр CONTAINER, который указывает PDB-базу, в которой нужно выполнить соответствующий динамический SQL или PL/SQL-код:
  dbms_sql.parse(c                  => v_xCursor,
                 statement          => v_xPLSQL_Str,
                 language_flag      => DBMS_SQL.NATIVE,
                 container          => 'CRM');
В вышеприведенном примере, код содержащийся в переменной v_xPLSQL_Str будет выполнен в PDB-базе CRM.
Важно отметить следующее: такой динамический код может быть выполнен только когда текущая сессия находится в БД-контейнере (CDB$ROOT), и пользователь должен иметь права на доступ к соответствующей PDB, то есть должен быть common-пользователем.

Создадим common-пользователя и дадим ему доступ к всем PDB в контейнере:
[oracle@dbim cdb1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 2 07:04:01 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE USER global$rscott1
   IDENTIFIED BY rtiger container=all;

User created.

SQL> grant connect, resource to global$rscott container=all;

Grant succeeded.
Для имени common-пользователя был использован нестандартный префикс (GLOBAL$, а не C##), поскольку параметр common_user_prefix был переопределен:
SQL> show parameter common

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix       string  global$
Возможность переопределения префикса в именах common-пользователей появилась в патчсете 12.1.0.2.

Предположим, что в контейнере находятся три PDB-базы: ERM, CRM и DW. В каждой из этих БД есть процедура создания учетной записи клиента определенная в пакете account_service:
create or replace package account_service as

  v_gLastAuditMessage varchar2(128);

  procedure newAccount(v_pName        in varchar2,
                       v_pDescription in varchar2);

end;
И после создание учетной записи клиента происходит сохранение информации аудита в глобальной переменной v_gLastAuditMessage:
create or replace package body account_service as

  procedure newAccount(v_pName        in varchar2,
                       v_pDescription in varchar2) is
  begin
    --код специфичный для каждой PDB 
    ...  ...  ...  ...  ...  ...  

    v_gLastAuditMessage := 'Account "' || v_pName || '" created in pdb ' || sys_context('USERENV', 'CON_NAME');
  end;
  
end;
В root-контейнере мы можем создать процедуру создания учетных записей клиентов сразу в нескольких PDB, при этом реально в каждой PDB-базе будет выполняться именно свой прикладной код.
Рис.1 Инкапсуляция прикладной логики в cdb$root c помощью динамического SQL

Исходный код, иллюстрирующий вышеописанный подход, может выглядеть следующим образом:
[oracle@dbim cdb1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 2 07:45:16 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>conn global$rscott/rtiger
Connected.

SQL> --in Root container:

SQL> create or replace type TArrayOfString as table of varchar(32);
/

Type created.

SQL> create or replace package account_service as

  procedure newAccount(v_pName        in varchar2,
                       v_pDescription in varchar2,
                       v_pContainers  in TArrayOfString);

end;
/

Package created.


--Бизнес-логика выполняется в каждой PDB:
SQL> create or replace package body account_service as

  procedure newAccount(v_pName        in varchar2,
                       v_pDescription in varchar2,
                       v_pContainers  in TArrayOfString) is
    v_xCursor    integer;
    v_xRowCount  integer;
    v_xPLSQL_Str varchar2(1600);
  begin
    v_xPLSQL_Str := 'begin account_service.newAccount(:v_pName, :v_pDescription); end;';

    v_xCursor := dbms_sql.open_cursor;
  
    for v_xContainerIndex in 1..v_pContainers.count
    loop
      dbms_sql.parse(c                  => v_xCursor,
                     statement          => v_xPLSQL_Str,
                     language_flag      => DBMS_SQL.NATIVE,
                     container          => v_pContainers(v_xContainerIndex));

      dbms_sql.bind_variable(v_xCursor, ':v_pName', v_pName);       
      dbms_sql.bind_variable(v_xCursor, ':v_pDescription', v_pDescription);

      v_xRowCount := dbms_sql.execute(c => v_xCursor);
    end loop;

    dbms_sql.close_cursor(c => v_xCursor);    
  end;

end;
/
Package body created.
Попробуем создать учетные записи клиентов сразу в трех подключаемых БД в контейнере:
SQL> exec account_service.newAccount('Scott Tiger','Scott Tiger demo',new TArrayOfString('ERP','CRM','DW'));

PL/SQL procedure successfully completed.
Теперь переключимся в конкретную PDB-бд и проверим контекст сессии (состояние переменной v_gLastAuditMessage):
SQL>  alter session set container=ERP; 

Session altered.

SQL>  set serveroutput on 
SQL>  exec dbms_output.put_line(account_service.v_gLastAuditMessage); 
Account "Scott Tiger" created in pdb ERP

PL/SQL procedure successfully completed.

SQL> alter session set container=CRM;

Session altered.

SQL>  set serveroutput on 
Account "Scott Tiger" created in pdb CRM

PL/SQL procedure successfully completed.
Как видите, в каждой PDB-бд поддерживается свой контекст сессии - значение глобальной переменной v_gLastAuditMessage имеет свое значение для каждой PDB. Вообще говоря, это три разные переменные, поскольку каждая PDB-бд имеет собственную реализацию пакета управления учетными записями клиентов - account_service.

В Oracle 12c динамический SQL позволяет на уровне контейнера CDB$Root проводить своеобразную "оркестровку" вашего прикладного кода реализованного внутри PDB-баз данных.

16 нояб. 2014 г.

Oracle Database 12c: функции с правами вызывающего в представлениях

До Oracle Database 12c, если мы вызываем функцию внутри представления (неважно каким способом: в предикате фразы WHERE, либо в вычисляемом столбце), этот вызов всегда происходит с правами создателя (DEFINER RIGHTS), независимо от того как была определена функция.
Рассмотрим следующий пример.
Предположим, что у нас в Oracle Database 11g есть функция определенная с правами вызывающего:

[oracle@rac1 ~]$ sqlplus rscott/rtiger

SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 10 13:09:00 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP
and Data Mining options

SQL> CREATE OR REPLACE FUNCTION getClosedAccounts(v_pBranchId IN NUMBER) RETURN VARCHAR2
AUTHID CURRENT_USER IS
  v_xRes NUMBER(9);
BEGIN
  SELECT 
    count(a.rowid)
  INTO
    v_xRes
  FROM
    accounts a
  WHERE
    a.branch_id = v_pBranchId and
    a.Status    = 'CLOSED';

  RETURN v_xRes;
END;
/

Function created.
SQL> 
Вызов этой функции под другим пользователем, как и положено, будет сделан с правами вызывающего:

SQL> grant execute on getClosedAccounts to hr;

Grant succeeded.

SQL> connect hr/hr
Connected.

SQL> select rscott.getClosedAccounts(123) from dual;
select rscott.getClosedAccounts(123) from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "RSCOTT.GETCLOSEDACCOUNTS", line 5

SQL> 
Все правильно: вызов произошел с правами вызывающего, а пользователь HR не имеет прав не чтение таблицы accounts. А теперь попробуем вызвать эту же функцию под пользователем HR, но уже внутри представления:


SQL> conn rscott/rtiger
Connected.

SQL>CREATE OR REPLACE VIEW v$Branches_Closed_Accounts AS
   SELECT
     b.Id,
     getClosedAccounts(b.Id) as Closed_Count
   FROM
     branches b;

View created.


SQL> GRANT SELECT ON v$Branches_Closed_Accounts TO hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> SELECT * FROM rscott.v$Branches_Closed_Accounts;

 ID  CLOSED_COUNT
---  ---------------------------------------------------------------------
  1  0
Функция внутри представления была вызвана без всякой ошибки! До Oracle Database 12c вызов функции внутри представления всегда происходит с правами создателя, даже если функция была определена как с правами вызывающего.

В Oracle Database 12c, в определении представления, указывается фраза BEQUEATH, которая определяет, как будут вызывать PL/SQL-функции используемые в запросе: DEFINER или CURRENT_USER.

Переключимся на Oracle Database 12c и перепишем наше представление по другому, указав что наша функция должна быть вызвана с правами вызывающего:

[oracle@localhost]$ sqlplus rscott/rtiger

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 16 20:57:49 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 16 2014 20:56:22 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE OR REPLACE VIEW v$Branches_Closed_Accounts 
  BEQUEATH CURRENT_USER AS
   SELECT
     b.Id,
     getClosedAccounts(b.Id) as Closed_Count
   FROM
     branches b;

View created.

SQL> conn hr/hr
Connected.
SQL> SELECT * FROM rscott.v$Branches_Closed_Accounts;
SELECT * FROM rscott.v$Branches_Closed_Accounts
                       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "RSCOTT.GETCLOSEDACCOUNTS", line 5
SQL> 
Теперь функция в представлении вызывается с правами вызывающего!

По умолчанию, в Oracle Database 12 представление будет создаваться с неявной фразой BEQUEATH DEFINER, то есть поведение будет как в предыдущих версиях - функции в представлениях будут вызываться с правами создателя.

Важно отметить, что если в определении функции был указан режим вызова с правами создателя, то фраза BEQUEATH CURRENT_USER в представлении игнорируется:

SQL> conn rscott/rtiger
Connected.
SQL> CREATE OR REPLACE FUNCTION getClosedAccounts(v_pBranchId IN NUMBER) RETURN VARCHAR2
AUTHID DEFINER IS
  v_xRes NUMBER(9);
BEGIN
  SELECT 
    count(a.rowid)
  INTO
    v_xRes
  FROM
    accounts a
  WHERE
    a.branch_id = v_pBranchId and
    a.Status    = 'CLOSED';

  RETURN v_xRes;
END;
/

Function created.

SQL> conn hr/hr
Connected.
SQL> SELECT * FROM rscott.v$Branches_Closed_Accounts;

 ID  CLOSED_COUNT
---  ---------------------------------------------------------------------
  1  0

4 нояб. 2014 г.

Oracle JavaVM Component 12.1.0.2.1 Database PSU

Продолжается стабилизация релиза Oracle 12c R1 (12.1.0.2). В октябре, как и положено, вышел первый PatchSet Update (PSU 1).
Вместе с знакомыми нам патчами: для БД (DB PSU) и для Grid Infrastructure (GI PSU) появился новый вид PSU - Oracle JVM Component Database PSU. Этот пакет исправлений содержит исправления ошибок для виртуальной Java-машины работающей внутри БД.
Следует заметить, что перед установкой Oracle JVM Database PSU, на базе данных предварительно уже должен стоять соответствующий Database PSU, в данном случае - Database PSU1.

Таким образом, теперь процедура установки патчей PSU на базу данных выглядит следующим образом:

  • обновляем утилиту OPatch до последней версии - здесь ничего необычного: всегда рекомендовалось скачать ее свежую версию (Patch 6880880);

  • устанавливаем DB PSU - здесь тоже все по старому: в каталоге распакованного архива с патчем запускаем утилиту opatch c параметром apply (это знает любой школьник! :-));


    Рис.1 Установка патча Oracle Database 12.1.0.2 PSU 1

  • затем необходимо обновить словарь, - теперь это делается с помощью утилиты datapatch (никаких catcpu.sql!), эта утилита выполняет все SQL-скрипты, необходимые для корректной установки патча;

    Рис.2 Обновление словаря для DB 12.1.0.2 PSU 1 с помощью команды $O_H/OPatch/datapatch -verbose


  • устанавливаем DB OJVM PSU, - точно также выполняем: opatch apply;

    Рис.3 Установка патча Oracle Database 12.1.0.2 OJVM PSU 1


  • наконец, обновляем словарь для DB OJVM PSU, - точно также выполняем: datapatch -verbose;

    Рис.4 Обновление словаря для DB 12.1.0.2 OJVM PSU 1

    Процедура установки патчей на СУБД Oracle Database 12c претерпела изменения: теперь нам не нужно вручную запускать post-install SQL-скрипты, также следует не забывать об установке нового вида PSU - Database PSU JVM Component.

    P.S. Выпущенный в сентябре большой MLR-патч для Oracle In-Memory (patch 19615705), к сожалению, конфликтует с DB PSU 12.1.0.2.1. В ближайшее время будет выпущен такой же MLR, но для 12.1.0.2.1 (on Top 12.1.0.2.1).
  •