Сложное ограничение целостности (constraint)

Однажды возникла необходимость организовать сложный контроль вводимой одновременно в несколько таблиц логически связанной информации в БД ORACLE.Преобразование начального логически корректного набора данных в таблицах в конечный логически корректный набор производится последовательностью DML операторов. При этом проводить модификацию может произвольный клиент поведение которого неконтролируемо а структура данных такова, что в процессе выполнения пошаговой модификации на некоторых шагах набор данных может становится логически ошибочным.

Простейший пример это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.

Продолжение статьи на Хабре

Ускорение программы на базе Oracle средствами СУБД

Одним из наиболее общих типов запросов в хранилищах данных является запрос, возвращающий сводную: или сгруппированную или подробную информацию, такую как сумма, среднее или количество. Запросы, суммирующие данные, являются очень важными, поскольку объединяют подробную информацию индивидуальных транзакций и представляют их в удобной форме, которая показывает общее направление и помогает пользователям принимать обоснованные решения.

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

create materialized view MV_PORT refresh force on demand as select * from table_name

Обновление данных выполняется следующим кодом:

dbms_snapshot.refresh('adm.mv_port', 'C');

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

Проблемы с кодировкой при работе с Java

В прошлой записи мы подготовили Java-класс для выполнения запроса к web-сервису. В случае, если кодировка родительской базы не совпадает с кодировкой базы, к которой выполняется запрос, результата, естественно, мы не получим.

Можно выполнить преобразование на уровне базы данных (функция convert), а можно воспользоваться возможностями языка Java и перекодировать передаваемый параметр в нашем классе, например, в кодировку UTF-8:

java.net.URLEncoder.encode(param1, "UTF-8")

Подробности о выполнении запроса, по-прежнему, находятся в системном выводе Oracle — в случае 10.2.0.4 в папке udump (файлы *.trc). Не забываем пользоваться println‘ами для подробной статистики выполнения :)

Запрос к веб-сервису на Oracle

Для работы процедуры необходима предустановленная Java.

1. Пишем класс на языке Java:

create or replace and compile java source named run_curl as
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;

public class Service {
public static int call(String unr, String prtk) {
InputStream in = null;
try {
java.sql.DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
URL url =
new URL(«http://localhost:2222/web?soap_method=GetData»
+»&»+»pParam1=» + qq
+»&»+»pParam2=» + ww
);
in = url.openStream();
final int available = in.available();
byte[] buffer = new byte[available];
in.read(buffer);
String value = new String(buffer, «UTF-8»);
System.out.println(value);
#sql { INSERT INTO adm.tttt(p1) VALUES (:value)};
System.out.println(«Inserted»);

}
catch (Exception e) {
System.out.println(e.getMessage());
return 1;
}
finally {
if (in != null) {
try {
in.close();
}
catch (IOException e) {
}
}
}
return 0;
}
}

2. Оборачиваем в PL/SQL:

CREATE OR REPLACE PROCEDURE «RUN_CURL»(param1 varchar2, param2 varchar2)
IS LANGUAGE JAVA
NAME ‘Service.call(java.lang.String, java.lang.String)’;

3. Вызываем как обычную pl/sql-процедуру:

begin
run_curl(param1,param2);
end;

Подробности о выполнении запроса находятся в системном выводе Oracle — в случае 10.2.0.4 в папке udump (файлы *.trc).

Вызов команд Shell из Oracle

Способ работает только на версиях Oracle с предустановленной Java (не Oracle XE).

1. Пишем класс на языке Java

create or replace and compile java source named run_host as
public class RUN_xHOST
{
public static int run_OS_cmd( String command_str )

throws Exception
{
Runtime rt = Runtime.getRuntime();

Process pr = rt.exec( command_str );
return (0);
}
}

2. Оборачиваем в PL/SQL в качестве процедуры:

CREATE OR REPLACE PROCEDURE «RUN_HOST» (command_str IN VARCHAR2)
IS LANGUAGE JAVA
NAME ‘RUN_xHOST.run_OS_cmd(java.lang.String)’;

3. Вызываем как обычную процедуру:

begin
run_host(‘mkdir 111’);
end;

Более подробно на Хабре: способ от Тома Кайта (с использованием Java), способ с подключением С библиотеки.

Смена UNDO TABLESPACE

Во-первых, необходимо узнать текущие UNDO параметры:

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

Текущее табличное пространство UNDO в нашем случае UNDOTBS1. Вторым действием создаем табличное пространство ему на смену, выбирая размер и шаг увеличения в зависимости от потребностей базы:

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/app/oracle/oradata/orcl/undotbs2.dbf'
SIZE 500M AUTOEXTEND ON NEXT 50M;

Устанавливаем вновь созданное табличное пространство пространством по умолчанию:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

Пытаемся удалить старое табличное пространство:

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Появившаяся ошибка говорит нам о том, что UNDOTBS1 все еще используется и будет использоваться в течение времени, указанного в параметре UNDO_RETENTION — в нашем случае, 900 секунд, или 15 минут. Выполняем попытку удаления спустя 15 минут:

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Восстановление удаленных строк в Oracle

Начиная с версии 9i пользователям Oracle доступна возможность так называемых ретроспективных запросов, позволяющих восстановить данные на определенный момент времени в прошлом. Реализовано это так: при удалении строк они не исчезают, а перемещаются в UNDO сегмент, где хранятся некоторое время в зависимости от его размера и значения параметра UNDO_RETENTION в конфигурационном файле init.ora.

Существует 2 способа использования ретроспективных запросов:

Пример «AS OF»:

SELECT * FROM USERS AS OF TIMESTAMP
TO_TIMESTAMP('01-JAN-07 10:00:00','DD-MON-YY HH24: MI: SS');

Пример DBMS_FLASHBACK:

DECLARE
CURSOR c IS SELECT * FROM users;
user c%ROWTYPE;
BEGIN
DBMS_FLASHBACK.ENABLE_AT_TIME('01-JAN-07 10:00:00');
OPEN c;
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c INTO user;
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END;

Индексы в Oracle

Индексы Oracle, построенные на основе таблиц, позволяют значительно увеличить производительность запросов, использующих большой объем данных.

Oracle поддерживает следующие типы индексов:

Более подробно об индексах можно прочитать на Хабре. Применительно к Oracle индексы рассмотрены здесь.

Перенос таблицы в новое табличное пространство

В простейшем случае (для одной таблицы и одного индекса) достаточно строк:

ALTER TABLE table_name MOVE TABLESPACE new_tablespace;
ALTER INDEX index_name REBUILD TABLESPACE new_tablespace;

Но в реальной жизни, как правило, такой процесс нужно сделать сразу для нескольких таблиц и множества построенных на них индексов. Для генерации правильной последовательности SQL-операторов используем системные представления Oracle:

select ‘ALTER INDEX ‘ || OWNER || ‘.’ || INDEX_NAME || ‘ REBUILD TABLESPACE ‘ || TABLESPACE_NAME || ‘;’ from DBA_INDEXES WHERE OWNER IN (‘owner_name1′,’owner_name2’);
select ‘ALTER TABLE ‘ || OWNER || ‘.’ || TABLE_NAME || ‘ MOVE TABLESPACE ‘ || TABLESPACE_NAME || ‘;’ from DBA_TABLES WHERE OWNER IN (‘owner_name1′,’owner_name2’);

Кроме того аналогичный функционал можно реализовать с помощью анонимного pl/sql-блока, с той лишь разницей, что скрипт сам выполняет генерируемые предложения (перед использованием обязательно убедитесь, что выполняются нужные вам действия! операция необратима!). Вот его пример:

set serverout on size 1000000
declare
begin
for c1 in (select owner,segment_name, segment_type,decode(segment_type,’TABLE’,’MOVE’,’REBUILD’) operation,
case when owner=’OWNER_NAME1′ and segment_type=’TABLE’ then ‘usr_d_01′
when owner=’OWNER_NAME1′ and segment_type=’INDEX’ then ‘usr_x_01′
when owner=’OWNER_NAME2′ and segment_type=’TABLE’ then ‘usr_d_02′
when owner=’OWNER_NAME2′ and segment_type=’INDEX’ then ‘usr_x_02’ end tablespace_name
from dba_segments
where owner in (‘OWNER_NAME1′,’OWNER_NAME2’)
and segment_type in (‘TABLE’,’INDEX’) ) loop
begin

dbms_output.put_line(‘alter ‘||c1.segment_type||’.’||c1.owner||’.’||
c1.segment_name||’ ‘||c1.operation||’ tablespace ‘||c1.tablespace_name);

execute immediate ‘alter ‘||c1.segment_type||’ ‘||c1.owner||’.’||
c1.segment_name||’ ‘||c1.operation||’ tablespace ‘||c1.tablespace_name; */
exception
when others then
dbms_output.put_line(c1.owner||’ ‘||c1.segment_name||’ ‘||c1.segment_type||’ ‘||sqlerrm);
end;
end loop;
end;

Установка Oracle 10g на Linux (RedHat, CentOS, SLES)

Создаем пользователей, нужные группы и директории.
groupadd oinstall
groupadd dba
mkdir -p /u01/app/oracle
useradd -g oinstall -d /u01/app/oracle -G dba oracle
passwd oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

Теперь нужно поменять некоторые настройки ядра Linux, для этого нужно добавить эти строки в файл /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

И запустить:
# sysctl -p
Приступим к установке oracle 10g на linux:
$ su - oracle
Далее необходимо зайти в Disk1/ и запустить:
./runInstall -ignoreSysPrereqs
Ключ -ignoreSysPrereqs задается, чтобы Ваш инсталлятор работал на любом дистрибутиве linux, по умолчанию он поддерживает RedHat, SuSE, UnitedLinux. После включения опции можно будет ставить и на другие дистрибутивы Linux.
Но чтобы root.sh не вылетал при попытке запуска нужно найти и закомментировать следующие две строчки:
$LNS $ID/init.cssd $rc/"$RC_START"init.cssd // { $ECHO $?; exit 1; }
$LNS $ID/init.cssd $rc/"$RC_KILL"init.cssd // { $ECHO $?; exit 1; }

Теперь:
# . /root.sh
Для настройки автоматической загрузки экземпляра базы данных внесем изменения в файл /etc/oratab, в котором определяются, какие базы будут стартовать автоматически при запуске утилиты dbstart
В последней строке Вы найдете:
orcl::N
Необходимо поменять N на Y. Это значит, что инстанцию Oracle нужно загружать.
Далее внесем изменения в .bash_profile.
$ export ORACLE_HOME=
$ export ORACLE_SID=orcl
$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
$ export PATH=$PATH:$ORACLE_HOME/bin

Запуск:
$ dbstart
$ lsnrctl start

Для остановки:
$ dbshut
$ lsnrctl stop

Дальнейшая работа с Oracle возможна с помощью любого клиента (например, SqlPlus)


Все права защищены © 2016 Записки об Oracle | Thanks: Uzforyou