Category: Uncategorized

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

CREATE TABLE MAIN_TABLE
(
  ID NUMBER NOT NULL,
  SUMMA NUMBER NOT NULL
,CONSTRAINT MAIN_TABLE_PK PRIMARY KEY (ID) ENABLE
);

У этой таблицы есть куча дочерних таблиц и функция расчета некоего остатка денег
CalcRest(id in number,ReportDate in date), собирающая информацию по всем дочерним
таблицам на определенную дату и довольно сложно ее обрабатывающая. Нам нужно получить
отчет содержащий ID, SUMMA и результат CalcRest.

Итерация 0

select id, summa, CalcRest(id,:ReportDate) rest
  from main_table;

Предположим, что в таком виде отчет считается 10 часов – все время уходит на запуск 
CalcRest для каждой из 10000 записей.

Итерация 1

Добавляем к таблице поле для сохранения последнего значения остатка и триггеры для всех 
дочерних таблиц для обновления этого поля:

ALTER TABLE MAIN_TABLE
  ADD ("REST" NUMBER DEFAULT 0 NOT NULL);

create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
begin
  –MAIN_ID – ID из MAIN_TABLE в дочерних таблицах
  update main_table
    set rest=CalcRest(:new.main_id, trunc(sysdate))
    where id=:new.main_id;
end;

Теперь запрос для отчета выглядит так:

select id, summa, 
(case when :ReportDate=trunc(sysdate) then rest
else CalcRest(id, :ReportDate) end) rest
from main_table;

Что существенно ускоряет работу отчета, если мы его формируем на текущую дату, для всех остальных дат – 10 часов.

Итерация 2

Добавляем поле с датой изменения остатка и модифицируем триггера:

ALTER TABLE MAIN_TABLE
  ADD ("REST_DATE" DATE DEFAULT sysdate NOT NULL);

create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
begin
  –MAIN_ID – ID из MAIN_TABLE в дочерних таблицах
  update main_table
    set rest=CalcRest(:new.main_id, trunc(sysdate)),
        rest_date=sysdate
    where id=:new.main_id;
end;

И запрос:

select id, summa,
(case when :ReportDate=trunc(sysdate) or :ReportDate>rest_date then rest
else CalcRest(id, :ReportDate) end) rest
from main_table;

В результате CalcRest вызывается только для записей у которых остаток менялся после той даты на которую мы формируем отчет.

PS: Получилось немного сумбурно, но данный алгоритм реально работает.

На досуге развлекался переделкой PowerShell чтобы он был похож на Linux’овский bash 😉 Вот что получилось…
Вставляем следующий код в $PROFILE:

cd $env:userprofile

function prompt
{
    $homedir=$env:userprofile;
    $m=$homedir.length;
    $str = $pwd.Path;
    if ($str -eq $homedir)
    {
        $str="~";
    }
    else
    {
        if ($str.length -ge $m)
        {
            if ($str.substring(0,$m) -eq $homedir)
            {
                $str="~"+$str.substring($m);
            }
        }
    }

    Write-Host -NoNewline -ForeGroundColor Cyan "PS ";

    if ($env:username -eq "Администратор")
    {
        Write-Host -NoNewline -ForeGroundColor Gray "$env:computername:";
    }
    else
    {
        Write-Host -NoNewline -ForeGroundColor Gray "$env:username@$env:computername:";
    }   
    Write-Host -NoNewline -ForeGroundColor DarkYellow "$str";
    if ($env:username -eq "Администратор")
    {
        Write-Host -NoNewLine -ForeGroundColor Cyan " #";
    }
    else
    {
        Write-Host -NoNewLine -ForeGroundColor Cyan ">";
    }
    " "
}

Получаем результат:

Суть проблемы в том,
что Control вешается на IP-адрес. Т.е. при динамическом IP или отсутствии сети
становится недоступен. Естественное решение – повесить его на localhost.

Гасим службы Oracle.
Отрубаем сеть. Удаляем каталог ORACLE_HOME192.168.1.65_ORCL. Удаляем службу
OracleDBConsoleORCL. Запускаем оставшиеся службы Oracle. Запускаем
Configuration Assistant, ставим галку Configure Database Control. Создаются
служба OracleDBConsoleORCL и каталог <имя хоста>_ORCL. Но при этом
появляется ошибка про Enterprise Manager Repository с командами как исправить.
Пробуем. Не помогает – пароль не найден в репозитории.

Решение
нашлось здесь:

After an
installation of a new Oracle Home and complete restore of the database it is
nescesary to regenerate the Database Console repository to use the Enterprise
Manager.

[oracle@centos
~]$ . oraenv

ORACLE_SID
= [orcl] ? orcl

The
Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is
/u01/app/oracle

create a
password file:

orapwd
file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<password>

and check
the parameter remote_login_password:

[oracle@centos
db_1]$ sqlplus / as sysdba

SQL*Plus:
Release 11.1.0.6.0 – Production on Thu Jan 10 19:15:47 2008

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

Connected
to:

Oracle
Database 11g Enterprise Edition Release 11.1.0.6.0 – Production

With the
Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
show parameter password

NAME TYPE
VALUE

————————————
———– ——————————

remote_login_passwordfile
string EXCLUSIVE

SQL>

if this
is not exclusive, change it and restart the database. After this you can
connect as sysdba over tns.

It is
nescesary to recreate the repository because with the reintall of oracle we
lost the repository key. which is located in the oracle_home. First thing to do
is drop the repository:

[oracle@centos
db_1]$ emca -deconfig dbcontrol db -repos drop

STARTED
EMCA at Jan 10, 2008 8:12:36 PM

EM
Configuration Assistant, Version 11.1.0.5.0 Production

Copyright
(c) 2003, 2005, Oracle. All rights reserved.

Enter the
following information:

Database
SID: orcl

Listener
port number: 1521

Password
for SYSMAN user:

Do you
wish to continue? [yes(Y)/no(N)]: y

Jan 10,
2008 8:12:46 PM oracle.sysman.emcp.EMConfig perform

INFO:
This operation is being logged at
/u01/app/oracle/cfgtoollogs/emca/orcl/emca_2008_01_10_20_12_36.log.

Jan 10,
2008 8:12:48 PM oracle.sysman.emcp.util.DBControlUtil stopOMS

INFO:
Stopping Database Control (this may take a while) …

Jan 10,
2008 8:13:01 PM oracle.sysman.emcp.EMReposConfig invoke

INFO:
Dropping the EM repository (this may take a while) …

Jan 10,
2008 8:20:33 PM oracle.sysman.emcp.EMReposConfig invoke

INFO:
Repository successfully dropped

Enterprise
Manager configuration completed successfully

FINISHED
EMCA at Jan 10, 2008 8:21:04 PM

[oracle@centos
db_1]$

Now we
can recreate the repository.

[oracle@centos
db_1]$ emca -config dbcontrol db -repos create

STARTED
EMCA at Jan 10, 2008 8:24:25 PM

EM
Configuration Assistant, Version 11.1.0.5.0 Production

Copyright
(c) 2003, 2005, Oracle. All rights reserved.

Enter the
following information:

Database
SID: orcl

Listener
port number: 1521

Password
for DBSNMP user:

Password
for SYSMAN user:

Email
address for notifications (optional): <my mail>

Outgoing
Mail (SMTP) server for notifications (optional): centos.bekijkhet.com

—————————————————————–

You have
specified the following settings

Database
ORACLE_HOME ……………. /u01/app/oracle/product/11.1.0/db_1

Local
hostname ……………. centos.bekijkhet.com

Listener
port number ……………. 1521

Database
SID ……………. orcl

Email
address for notifications …………… <my mail>

Outgoing
Mail (SMTP) server for notifications …………… centos.bekijkhet.com

—————————————————————–

Do you
wish to continue? [yes(Y)/no(N)]: y

Jan 10,
2008 8:24:50 PM oracle.sysman.emcp.EMConfig perform

INFO:
This operation is being logged at
/u01/app/oracle/cfgtoollogs/emca/orcl/emca_2008_01_10_20_24_25.log.

Jan 10,
2008 8:24:54 PM oracle.sysman.emcp.EMReposConfig createRepository

INFO:
Creating the EM repository (this may take a while) …

Jan 10,
2008 8:38:28 PM oracle.sysman.emcp.EMReposConfig invoke

INFO:
Repository successfully created

Jan 10,
2008 8:38:57 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository

INFO:
Uploading configuration data to EM repository (this may take a while) …

Jan 10,
2008 8:42:16 PM oracle.sysman.emcp.EMReposConfig invoke

INFO:
Uploaded configuration data successfully

Jan 10,
2008 8:42:25 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib

INFO:
Software library configured successfully.

Jan 10,
2008 8:42:25 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary

INFO:
Deploying Provisioning archives …

Jan 10,
2008 8:42:49 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary

INFO:
Provisioning archives deployed successfully.

Jan 10,
2008 8:42:49 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO:
Securing Database Control (this may take a while) …

Jan 10,
2008 8:43:17 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO:
Database Control secured successfully.

Jan 10,
2008 8:43:17 PM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO:
Starting Database Control (this may take a while) …

Jan 10,
2008 8:45:47 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO:
Database Control started successfully

Jan 10,
2008 8:45:47 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO:
>>>>>>>>>>> The Database Control URL is https://centos.bekijkhet.com:1158/em
<<<<<<<<<<<

Jan 10,
2008 8:46:42 PM oracle.sysman.emcp.EMDBPostConfig invoke

WARNING:

************************ 
WARNING  ************************

Management
Repository has been placed in secure mode wherein Enterprise Manager data will
be encrypted.  The encryption key has been placed in the file:
/u01/app/oracle/product/11.1.0/db_1/centos.bekijkhet.com_orcl/sysman/config/emkey.ora.  
Please ensure this file is backed up as the encrypted data will become unusable
if this file is lost.

***********************************************************

Enterprise
Manager configuration completed successfully

FINISHED
EMCA at Jan 10, 2008 8:46:42 PM

[oracle@centos
db_1]$

Be sure
to backup your system including the emkey.ora file!

Помогло!

Взято из Oracle Magazine январь-февраль 2008

Использование Oracle SQLDeveloper для локальной отладки PL/SQL означает, что вы выбираете и присоединяетесь к PL/SQL-программе, используя SQL Developer Connections Navigator. Вы устанавливаете точку останова в том месте, где отладчик должен сделать паузу, и затем нажимаете кнопку Debug. Oracle SQL Developer стартует отладочный сеанс, соединяется с ним, и останавливается, когда достигнет точки останова. При локальной отладке Oracle SQL Developer – это клиент, который инициирует отладку.
Удаленная отладка PL/SQL-кода в Oracle SQL Developer
означает, что вы инициируете отладочную акцию с клиента, внешнего по отношению к Oracle SQL Developer. Внешние клиенты могут быть PL/SQLWeb-приложениями, приложениями Oracle Application Express или сессиями SQL*Plus. Удаленная отладка требует некоторых ручных действий: запуск отладочного листенера Oracle SQL Developer
и присоединение к этому листенеру через сессию базы данных, которую требуется отладить (Удаленная отладка недоступна для баз данных, версия которых ниже Oracle9I Database Release 2).

Следующие шаги описывают пример удаленной отладочной сессии:
1. В Oracle SQL Developer присоединитесь к базе данных, где находится PL/SQL-код. Используйте ту же строку соединения HR_ORCL и пакет EMP_FETCHER, которые использовались ранее.
2. Выберите соединение HR_ORCL в Connections Navigator,
и затем щелкните правой кнопкой мыши и выберите Remote Debug. Откроется диалоговое окно Listen for JPDA.
3. Введите порт и IP-адрес сервера. Теперь вы видите новое окно Run Manager, на котором отображается информация о сервере Debug Listener. (Можно установить Tools -> Preferences -> Debugger -> Prompt for Debugger Host для отладки, когда соединение выполняется через бранмауэр или сессии виртуальных частных сетей [virtual private network – VPN] ).
4. В Connections Navigator выберите тело пакета EMP_FETCHER, затем щелкните правой кнопкой мыши и выберите Edit…, чтобы открыть редактор кода Oracle PL/ SQL. Кликните в левом поле на FUNCTION GET_EMP…, чтобы установить точку останова.
5. Кликните Compile for Debug как показано на Рис. 2.
6. Теперь вы готовы к тому, чтобы начать удаленный процесс отладки со стороны Oracle SQL Developer. Для этого необходимо, чтобы был готов внешний клиент, в данном случае SQL*Plus. Запустите сессию в SQL*Plus для того же самого пользователя базы данных, и введите IP-адрес и порт, заменив на ваши собственные, которые использовались для удаленного подключения в Oracle SQL Developer:
exec DBMS_DEBUG_JDWP.CONNECT_TCP (‘127.0.0.1’, 4000)
7. В SQL*Plus используйте анонимный блок для вызова функции:
DECLARE
EMP_NO NUMBER;
v_Return HR.EMP_REC;
BEGIN
v_Return := EMP_FETCHER.GET_EMP(201);
END;
/
8. Теперь управление передано Oracle SQL Developer и вы можете выполнять код по шагам. Начиная с этого момента процесс такой же, как при локальной отладке.
Стали активны различные отладочные окна. Все они имеют свое назначение, однако важными сразу же становятся:
• Окно Debugging, которое позволяет управлять выполнением программы. Начните отладку, нажимая пиктограмму Step Into.
• Редактор кода, который показывает точку выполнения. Когда отладка выполняется по шагам, при перемещении мыши всплывающие подсказки отображают название и значение переменной под указателем. Продолжайте нажимать Step Into до тех пор пока не достигните строки EMP_RTN:= EMP_REC…. В этой точке дайте мыши переместиться к переменной EMP_FOUND, чтобы увидеть всплывающую подсказку, как показано на Рис. 3.
• Окно Data отображает все переменные, которые действительны в текущем контексте. Перейдя на точку выполнения EMP_RTN := EMP_REC…, выберите закладку Data и раскройте узел EMP_FOUND. Теперь запись заполнена. В этой точке можно просматривать и модифицировать переменные, чтобы увидеть их влияние на процедуру.
9. Продолжайте шаги до тех пор, когда процедура завершится, управление будет передано внешнему клиенту, и отладочная сессия завершится.

Реализация проверки бизнес-правил в виде триггеров:

CREATE OR REPLACE TRIGGER check_sal
  BEFORE UPDATE OF sal ON emp
  FOR EACH ROW
  WHEN (new.sal<old.sal OR new.sal>old.sal*1.1)
BEGIN
  RAISE_APPLICATION_ERROR(-20508, ‘Do not decrease salary not increase by more than 10%’);
END;

SHOW ERRORS – показ ошибок компиляции в SQL*Plus

перекомпиляция инвалидных пакетов:

declare
  str varchar2(200);
  err varchar2(2000);
  cursor c_obj is
    select object_name, object_type from user_objects where status=upper(‘invalid’);
begin
  for c1 in c_obj loop
    if c1.object_type=upper(‘package body’) then
      str:=’alter package ‘||c1.object_name||’ compile body’;
    else
      str:=’alter ‘||c1.object_type||’ ‘||c1.object_name||’ compile’;
    end if;
    begin
      execute immediate str;
    exception
      when others then
        select text
          into err
          from user_errors
          where name=c1.object_name and type=c1.object_type and rownum=1;
        dbms_output.put_line(c1.object_name||’:’||err);
        err:=null;
    end;
  end loop;
end;

использование условий типа:

where (i.id, i.date1) in (select k.id, k.date2 from table2) –почему такую конструкцию мы не использовали раньше – загадка, как говориться – век живи – век – учись

Тема хранимых процедур на C# меня заинтересовала, хоть и не совсем понятно зачем это (хранимые процедуры на C#) нужно, но забавно. Впрочем надобность хранимых процедур Oracle на Java также остается для меня туманной.

Сначала пришлось повозиться с Oracle Database Extensions for .NET: оказалось, что к листенеру не был подключен ICP протокол, не был запущен агент для CLR и не был прогнан скрипт DBMSClr.plb после установки ODE.NET. Потом возник вопрос с коннектом из процедуры: ведь глупо в хранимой процедуре, которая запускается врапленной PL/SQL процедурой из схемы, писать в строке подключения имя пользователя, пароль и базу. Ответ нашелся в виде строки подключения "context connection=true". После этого все заработало.
Исходники простенькой функции:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types; 

namespace OraTest
{
    public class Class1
    {
        public static Int32 AbonentsCount()
        {
            //Создаем соединение
            OracleConnection con = new OracleConnection();
            con.ConnectionString = "context connection=true";
            con.Open();
            //Создаем команду
            OracleCommand cmd = new OracleCommand();
            cmd.CommandText = "select count(*) cnt from prom_abonent";
            cmd.Connection = con;
            Int32 res = 0;
            res = Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Dispose();
            con.Close();
            return res;
        }
    }
} 

Запуск:
select abonentscount from dual; 

Написал и уже потом нашел туториал от Оракла на эту тему:
http://www.oracle.com/pls/xe102/to_toc?pathname=appdev.102%2Fb25312%2Ftoc.htm&remark=portal+%28Getting+Started%29