«Технологии баз данных (СУБД Oracle)» (стр. 3 )

Квоты табличных пространств

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

·  неограниченные (unlimited) – разрешение пользователю использовать столько пространства, сколько будет доступно в табличном пространстве.

·  Конкретные, заданные в мегабайтах или килобайтах – размер пространства, которое может быть занято пользователем. Это не гарантирует, что пространство специально зарезервировано для пользователя. Значение может быть больше ил меньше, чем текущий размер памяти, доступный в табличном пространстве.

Системная привилегия UNLIMITED TABLESPACE имеет приоритет над всеми отдельными квотами и предоставляет пользователю неограниченную квоту на все табличные пространства.

Табличное пространство по умолчанию

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

Временное табличное пространство

Это пространство, где будут создаваться временные объекты:

-  для сортировки,

-  для временных таблиц.

Блокирование входа пользователя в систему

Для запрета соединения с базой данных, можно заблокировать вход пользователя в систему. Блокирование и разблокирование входа пользователя в систему может выполняться как автоматически, так и вручную администратором базы данных.

Ресурсные ограничения

Можно наложить ограничения на использование ресурсов системы. Например, на время использования центрального процессора, логический ввод/вывод или количество сеансов, открытых пользователем.

Привилегии

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

Системные привилегии (system privileges)

Каждая системная привилегия, представленная пользователю, позволяет ему выполнять в базе данных конкретные операции или классы операций. Например, создание табличных пространств – это системная привилегия. Системные привилегии могут быть предоставлены администратором или кем-то, кому явно предоставлены права по сопровождению привилегий. Имеется более 100 системных привилегий.

Объектные привилегии (object privileges)

Каждая объектная привилегия, выданная пользователю, позволяет ему выполнять конкретные действия над определенным объектом (например, таблицей, представлением, последовательностью, процедурой, функцией или пакетом). Без специального разрешения пользователи имеют доступ только к своим собственным объектам. Объектные привилегии могут быть предоставлены владельцем объекта или кем-то, кому явно предоставлено право выдавать привилегии на объект.

Роли

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

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

Возможно и следующее:

·  Сделать роль не по умолчанию. При выделении роли пользователю убрать отметку в поле DEFAULT. Теперь пользователь должен явно включать роль перед тем, как станут доступны привилегии роли.

·  Потребовать дополнительную аутентификацию роли. По умолчанию дополнительная аутентификация отключена (NONE), однако возможно задание дополнительной аутентификации перед динамическим включением роли.

·  Создание ”роли приложения”, включение которой возможно только в определенной хранимой процедуре. В этой процедуре можно, например, проверить сетевой адрес пользователя, имя программы, выполняемой пользователем, время дня или что-то другое для обеспечения должной безопасности при выделении группы полномочий.

Профиль

Профиль – именованный набор накладываемых на пароли и системные ресурсы ограничений:

·  cрок действия паролей и его истечение,

·  хронология паролей,

·  проверка сложности паролей,

·  блокирование входа в систему,

·  процессорное время,

·  операции ввода/вывода,

·  время простоя,

·  время соединения,

·  число параллельных сеансов,

·  объем памяти.

После создания профиля администратор базы данных может назначить его каждому пользователю. Если включены ресурсные ограничения, то сервер Oracle ограничивает использование базы данных и ресурсов согласно профилю, назначенному пользователю. После создания базы данных сервер Oracle автоматически создает профиль DEFAULT. Все ресурсные ограничения пользователей, которым явно не назначены никакие профили, устанавливаются профилем по умолчанию (DEFAULT). Первоначально все ресурсные ограничения профиля DEFAULT устанавливаются как UNLIMITED. Однако администратор базы данных может изменить эти ресурсные ограничения, и они будут применены ко всем пользователям, имеющим профиль DEFAULT.

Аудит

Аудит – наблюдение за выбранными действиями пользователя базы данных. Используется для получения сведений о подозрительных операциях с базой данных и для сбора информации об отдельных операциях в базе данных. База данных Oracle предоставляет три вида аудита. Администратор может выполнить аудит всех действий внутри базы данных. Однако перехват и сохранение информации о том, что происходит, увеличивает затраты системных ресурсов. Аудит следует сфокусировать только на отдельных событиях, представляющих интерес. Такой аудит оказывает минимальное влияние на производительность. Неточно установленный аудит может повлиять на производительность.

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

Аудит по значениям регистрирует изменение данных (вставки, изменения и удаления). Такой аудит расширяет стандартный аудит БД, фиксируя не только произошедшее событие, но и фактические значения, которые были вставлены, изменены или удалены. Он реализуется с помощью триггеров базы данных.

Дифференцированный аудит (fine-grained auditing – FGA) применяется для наблюдения за командами SQL. FGA расширяет стандартный аудит БД, регистрируя выполняемые команды SQL, а не только происходящие события.

Стандартный аудит

Прежде всего, необходимо установить статический параметр инициализации AUDIT_TRAIL. Его значение показывает, куда направляются записи аудита. Обычное значение этого параметра DB приводит к записи данных аудита в представление DBA_AUDIT_TRAIL.

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

Варианты задания аудита:

Аудит команд SQL. Например, команда

AUDIT TABLE;

вызовет аудит любой команды DDL, связанной с таблицей: CREATE TABLE, DROP TABLE, TRUNCATE TABLE и т. д. Такой вид аудита можно сфокусировать на конкретном пользователе и удачном (SUCCESSFUL) или неудачном (NOT SUCCESSFUL) завершении операции.

AUDIT TABLE BY hr WHENEVER NOT SUCCESSFUL;

·  Аудит системных привилегий используется для сбора данных об использовании какой-либо системной привилегии (например, DROP ANY TABLE). Он может быть сфокусирован на конкретном пользователе и удачном или неудачном завершении операции. По умолчанию каждое использование системной привилегии генерирует запись аудита. Записи можно группировать таким образом, чтобы одна запись генерировалась для сеанса (так при изменении пользователем 100000 строк в таблице другого пользователя получается только одна запись). Фразу BY SESSION (аудит на уровне сеанса) необходимо задавать, так как по умолчанию действует BY ACCESS (аудит на уровне доступа).

AUDIT SELECT ANY TABLE, CREATE ANY TRIGGER;

AUDIT SELECT ANY TABLE BY hr BY SESSION;

·  Аудит объектных привилегий применяется для наблюдения за операциями с таблицами, представлениями, последовательностями, объектами directory и пользовательскими типами данных. Такой аудит может быть сфокусирован на удачных и неудачных операциях и фиксироваться на уровне сеанса или каждого обращения. В отличие от аудита системных привилегий по умолчанию действует аудит на уровне сеанса. Если необходимо получить в журнале записи по каждому использованию объекта, следует указывать фразу BY ACCESS.

AUDIT ALL BY hr. employees;

AUDIT UPDATE, DELETE ON hr. employees BY ACCESS;

·  Аудит сеансов.

AUDIT SESSION WHENEVER NOT SUCCESSFUL:

Такой аудит может быть сфокусирован на конкретном пользователе и удачном или неудачном завершении операции. Для каждого установленного с экземпляром соединения создается только одна запись аудита, которая заносится в журнал аудита во время соединения и изменяется при отсоединении. В этой записи аудита собирается суммарная информация о сеансе: время соединения, время отсоединения, выполненные логические и физические операции ввода/вывода и т. д. Во многих базах данных принято использовать команду

AUDIT SESSION (несфокусированный аудит).

Почти во всех базах данных для регистрации попыток проникновения в систему следует использовать

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

Доступ к записям аудита должен тщательно контролироваться, поскольку в них может содержаться секретная информация. Обычно сопровождение журнала производится администратором, но, если эта задача возлагается на кого-то другого, ему необходимо предоставить роль DELETE_CATALOG_ROLE, чтобы он мог удалять записи из журнала.

Аудит по значениям

Аудит базы данных не может регистрировать значения столбцов для операций вставки, изменения и удаления. Если необходимо отслеживать изменения столбцов и сохранять значения столбцов для каждого изменения, используйте аудит по значениям. Такой аудит может быть выполнен с помощью триггеров базы данных. При вставке, изменении и удалении данных из таблицы в фоновом режиме прорабатывает соответствующий триггер данной таблицы. Он записывает перехватываемые, изменяемые данные в таблицу, созданную для хранения результатов аудита. Аудит по значениям может вызвать большее снижение производительности, так как триггер должен прорабатывать при каждой вставке, изменении или удалении. Степень снижения производительности зависит от эффективности кода триггера. Аудит по значениям должен использоваться только в ситуациях, когда стандартный аудит базы данных предоставляет недостаточные данные.

Дифференцированный аудит

Аудит базы данных фиксирует только операцию, но не сохраняет информацию о команде. Дифференцированный аудит (FGA) позволяет регистрировать команды запросов и манипулирования данных языка SQL. FGA – это наиболее точно сфокусированный вид аудита по сравнению с аудитом по значениям и стандартным аудитом. Параметры FGA позволяют сфокусироваться на отдельных столбцах таблицы или представления, задать условия (определяемые администратором спецификации) выполнения аудита. FGA не требует использование триггеров, его влияние на производительность такое же, как и у стандартного аудита БД. Аудит использует пакет PL/SQL DBMS_FGA для создания политики аудита для таблицы или представления. Когда строки блока запроса содержат наблюдаемые столбцы и удовлетворяют условиям аудита, срабатывает событие аудита, в результате которого создается и сохраняется в журнале строка аудита. Дополнительно событие аудита может вызвать выполнение процедуры. FGA автоматически фокусируется на уровне команды, поэтому одна команда SELECT, возвращающая тысячи строк генерирует только одну запись аудита.

Пакет DBMS_FGA – это средство администратора для выполнения функций дифференцированного аудита. Чтобы сопровождать политики аудита, необходима привилегия выполнения (EXECUTE) пакета DBMS_FGA. Пакет DBMS_FGA содержит следующие подпрограммы:

ADD_POLICY – создание политики аудита с указанием предиката, определяющего условия аудита.

DROP_POLICY – удаление политики аудита.

ENABLE_POLICY– включение политики аудита.

DISABLE_POLICY - выключение политики аудита

Записи дифференцированного аудита и аудита объектов или привилегий заносятся в разные таблицы. Представление DBA_FGA_AUDIT_TRAIL обеспечивает доступ к записям дифференцированного аудита. Для определения политик аудита используются следующие представления:

ALL_AUDIT_POLICIES – все политики FGA для объектов, к которым текущий пользователь имеет доступ.

DBA_AUDIT_POLICIES - все политики FGA в базе данных.

USER_AUDIT_POLICIES - все политики FGA для объектов в текущей пользовательской схеме.

Создание объектов БД

Основной задачей разработчиков баз данных является создание объектов. Совокупность объектов базы данных, принадлежащих конкретному пользователю, образуют схему. Схема имеет то же самое имя, что и пользователь, владеющий этой схемой. Объекты схемы - это логические структуры, которые прямо ссылаются на данные БД. Объектами схем могут быть, например, таблицы, представления и индексы. Не существует связи между табличным пространством и схемой. Объекты одной и той же схемы могут располагаться в различных табличных пространствах, а табличное пространство может хранить объекты различных схем.

При именовании имен следует учитывать, что база данных Oracle использует пространства имен для разрешения зависимостей объектов схемы. Когда команда SQL ссылается на объект, Oracle рассматривает содержимое этой команды и размещает объект в соответствующем пространстве имен. Затем Oracle выполнит над объектом операцию, определяемую командой. Если объект не найден в соответствующем пространстве, Oracle возвратит ошибку.

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

·  таблицы,

·  представления,

·  последовательности,

·  частные синонимы,

·  автономные процедуры,

·  автономные хранимые функции,

·  пакеты,

·  материализованные представления,

·  определенные пользователем типы.

У следующих объектов свое собственное пространство имен:

·  индексы,

·  ограничения,

·  кластеры,

·  триггеры БД,

·  частные связи БД.

Поскольку таблицы и представления в одном пространстве имен, они не могут иметь совпадающие имена в одной и той же схеме. А вот таблицы и индексы в разных пространствах имен и поэтому их имена могут совпадать в одной и той же схеме. У каждой схемы базы данных свои пространства собственные имен для объектов, находящихся в этой схеме. Это означает, например, что две таблицы в разных схемах находятся в разных пространствах имен и могут иметь совпадающие имена.

Стандартные таблицы и поддержка целостности базы данных

Таблицы – основные формы хранения информации в базе данных Oracle. В них хранятся данные, доступные пользователю. Каждая таблица состоит из столбцов и строк. Строки могут храниться в любом порядке, в зависимости от операций, выполняемых с таблицей. Для создания таблицы используется команда CREATE TABLE. Чтобы создать таблицу в собственной схеме, необходимо иметь привилегию CREATE TABLE. Для создания таблице в схеме другого пользователя необходимо иметь системную привилегию CREATE ANY TABLE.

Пример создания таблицы:

CREATE TABLE hr. employees(

Employee_id NUMBER(6),

first_name VARCHAR2(20),

last_name VARCHAR2(25),

email VARCHAR2(25),

phone_number VARCHAR2(20),

hire_date DATE DEFAULT SYSDATE,

job_id VARCHAR2(10),

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

manager_id NUMBER(6),

department_id NUMBER(4)

);

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

·  Ограничение NOT NULL. По умолчанию во всех столбцах таблицы разрешены неопределенные значения (null) . Это означает возможность отсутствия значения в столбце. Ограничение NOT NULL требует, чтобы в столбце таблицы были определенные значения. Например, если установлено ограничение NOT NULL для столбца last_name таблицы employees, то в каждой строке хранится фамилия сотрудника.

·  Ограничение UNIQUE. Ограничение целостности “уникальный ключ” означает, что каждое значение в столбце или наборе столбцов должно быть уникально, т. е. никакие две строки таблицы не должны содержать одинаковые значения в этом столбце или наборе столбцов. Например, ограничение UNIQUE, определенное для столбца email таблицы employees запрещает строки с повторяющимися адресами электронной почты.

·  Ограничение PRIMARY KEY. Главный ключ (PRIMARY KEY) у каждой таблицы может быть только один. Это ограничение обеспечивает уникальность значения столбца или комбинации столбцов и поэтому значение первичного ключа уникально идентифицирует каждую строку таблицы. Oracle, реализуя ограничение PRIMARY KEY гарантирует, что:

-  в таблице нет двух строк с повторяющимися значениями в столбце или столбцах главного ключа;

-  и, что столбцы главного ключа должны всегда содержать определенные значения в каждой строке таблицы.

Oracle поддерживает ограничение PRIMARY KEY с помощью индекса. Например, главный ключ таблицы employees, включающий столбец employee_id, поддерживается путем неявного создания:

-  уникального индекса для этого столбца:

-  ограничения NOT NULL для этого столбца.

·  Ограничение ссылочной целостности. Различные таблицы в базе данных могут связываться на основе общих столбцов. БД поддерживает правила ссылочной целостности, гарантируя связи между таблицами. Ограничения ссылочной целостности требуют, чтобы значение внешнего ключа (foreign key) в каждой строке таблицы соответствовало значению главного ключа. Например, внешний ключ в таблице employees - это столбец department_id. Он гарантирует, что каждое значение в этом столбце соответствует значению главного ключа таблицы departments (столбец department_id.). Поэтому в таблице employees не может быть отделов с неверными номерами в столбце department_id..

·  Ограничение CHECK задает условие, которое должно быть верно (true) или неопределенно для каждой строки. Когда в результате команды DML нарушается условие в ограничении CHECK (false), команда откатывается.

Ограничение целостности Foreign Key

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

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

DROP TABLE table CASCADE CONSTRAINTS

Главная таблица не может быть очищена без удаления или отключения внешнего ключа.

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

DROP TABLESPACE табличное пространство INCLUDING CONTENTS CASCADE CONSTRAINTS

Если при удалении строк из главной таблицы не используется параметр DELETE CASCADE, то сервер Oracle проверяет, существуют ли в подчиненной таблице строки с соответствующим внешним ключом. Аналогично, обновление в главной таблице разрешается только тогда, когда отсутствуют подчиненные строки со старым значением ключа.

Состояния ограничений целостности

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

·  DISABLE NOVALIDATE – отключенное непроверенное;

·  DISABLE VALIDATE - отключенное проверенное;

·  ENABLE NOVALIDATE - включенное непроверенное;

·  ENABLE VALIDATE - включенное проверенное;

Отключенное непроверенное ограничение не проверялось, хотя определение ограничения хранится в базе данных. Данные, как находящиеся в таблице, так и вводимые заново, могут не подчиняться правилам, определяемым ограничением.

Если ограничение находится в состоянии “отключенное проверенное”, то запрещены все изменения ограниченных колонок. Кроме того, удален индекс для этого ограничения и ограничение отключено.

Если ограничение находится в состоянии “включенное непроверенное“, то невозможно ввести новые данные, нарушающие эти ограничение. Однако в таблице могут содержаться некорректные данные, т. е. данные, нарушающие это ограничение.

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

Когда ограничение из отключенного состояния переходит во включенное проверенное, таблица блокируется, и все данные этой таблицы проверяются на соответствие правилу, определяемому ограничением. Это может привести к блокированию операций DML, таких как загрузка данных, поэтому рекомендуется сначала переводить ограничение в состояние “включенное непроверенное“, а затем – во “включенное проверенное“. Переходы между этими состояниями подчиняются следующим правилам:

·  Указание только ENABLE подразумевает VALIDATE, если не указано NOVALIDATE.

·  Указание только DISABLE подразумевает NOVALIDATE, если не указано VALIDATE.

·  Для VALIDATE и NOVALIDATE нет подразумеваемого по умолчанию состояния ENABLE и DISABLE.

·  Когда уникальный или первичный ключ переводится из состояния DISABLE. в ENABLE и не существует подходящего индекса, автоматически создается уникальный индекс.

·  Когда любое ограничение переводится из состояния NOVALIDATE в VALIDATE, все данные проверяются.

Проверка ограничений

Точка транзакции, в которой проверяется ограничение целостности, задается соответствующим определением этого ограничения.

Немедленные (IMMEDIATE) проверки ограничений выполняются в конце каждого оператора DML. Нарушение ограничения вызывает откат соответствующего оператора. Если ограничение приводит к такому действию, как, например, каскадное удаление (delete cascade), то это действие выполняется как часть вызвавшего его оператора. Если задано ограничение с немедленной проверкой, то невозможно отложить эту проверку в конец транзакции. Отложенные (DEFERRED) проверки ограничений выполняются только во время фиксации транзакции. Если во время фиксации обнаруживаются какие-либо нарушения ограничений, то выполняется откат всей транзакции. Такие проверки ограничений особенно удобны, когда одновременно вводятся главные и подчиненные строки внешнего ключа. Ограничение, определенное как откладываемое (DEFERRABLE), может быть задано:

-  изначально с немедленной проверкой (INITIALLY IMMEDIATE) – т. е. по умолчанию функционирует как ограничение с немедленной проверкой, пока не будет явно указано иное:

-  изначально с отложенной проверкой (INITIALLY DEFERRED) – т. е. по умолчанию это ограничение проверяется только в конце транзакции.

Изменение режима выполнения откладываемых ограничений

Команда SET CONSTRAINTS переводит ограничения данной транзакции в состояние DEFERRED (отложенное) или IMMEDIATE (немедленные). Можно использовать эту команду, чтобы установить режим для всех или определенных в списке ограничений. Установленный в команде SET CONSTRAINTS режим действует в течении транзакции или до другой SET CONSTRAINTS, в которой переустанавливается режим. Команду SET CONSTRAINTS нельзя использовать в триггерах.

В команде ALTER SESSION также имеется фраза (SET CONSTRAINTS) для задания режима ограничений, проверку которых можно отложить. Фраза применяется для перевода в определенный режим DEFERRED или IMMEDIATE всех ограничений (список имен ограничений указать нельзя). Действие команды ALTER SESSION SET CONSTRAINTS распространяется только на текущую транзакцию.

ALTER SESSION SET CONSTRAINT[S] = {IMMEDIATE | DEFERRED | DEFAULT};

SET CONSTRAINTS | CONSTRAINT {список имен ограничений | ALL}

{IMMEDIATE | DEFERRED};

Создание ограничений

Ограничение целостности можно определять при создании таблицы в команде CREATE TABLE или после создания таблицы в команде ALTER TABLE. Фрагмент примера создания первичного ключа при создании таблицы.

CREATE TABLE employees
(
employee_id NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY,

….

Пример создания внешнего ключа:

ALTER TABLE EMPLOYEES
ADD CONSTRAINT emp_dept_key FOREIGN KEY (department_id)
REFERENCES departments (department_id);

Получение информации о таблицах и ограничениях целостности

Информацию о таблицах можно получить из словаря данных. Следующий запрос предоставит список всех таблиц пользователя HR.

SELECT table_name FROM dba_tables WHERE owner=’HR’;

Чтобы посмотреть, когда создана таблица employees пользователя HR, можно выполнить следующий запрос:

SELECT object_name, created FROM dba_objects WHERE object_name=’EMPLOYEES’ AND owner=’HR’;

Информацию об ограничениях можно получит из словарей dba_constraints и dba_cons_columns.

Временные таблицы

Временные таблицы создаются для обработки частных данных сеанса, время жизни которых ограничено транзакцией или сеансом. По команде CREATE GLOBAL TEMPORARY TABLE создаются временные таблицы двух видов: определенные для транзакции и определенные для сеанса. В таблицах первого вида данные существуют в течении транзакции, в таблицах второго вида – в течении сеанса. Данные принадлежат сеансу и в каждом сеансе можно выбирать и изменять только его собственные данные. Блокировки DML не устанавливаются над данными временных таблиц. Время жизни строк устанавливается использованием фраз:

·  ON COMMIT DELETE – строки доступны в рамках транзакции;

·  ON COMMIT PRESERVE ROWS – строки доступны в течении сеанса.

Для временных таблиц можно создавать индексы, представления и триггера, а также использовать утилиты Export и Import для выгрузки и загрузки определений временных таблиц. Однако данные не выгружаются. Определение временной таблицы доступно всем сеансам.

Пример создания временной таблицы:

CREATE GLOBAL TEMPORARY TABLE hr. emp_temp

AS SELECT * FROM hr. employees;

Внешние таблицы

Данные во внешних таблицах нельзя менять и они находятся вне базы данных – во внешних текстовых файлах. Внешняя таблица, как объект базы данных, создается командой

CREATE TABLE … ORGANIZATION EXTERNAL

Внешние таблицы похожи на представления - есть правила выборки, а доступ осуществляется реально к другим объектам. Операции DML над внешними таблицами невозможны. Индексы создавать нельзя. Внешние таблицы позволяют получить доступ к внешним данным, которые по каким то причинам не загружены в базу данных, как к простым таблицам. Если необходимо обработать внешние данные, перед окончательным помещением их в таблицы можно загрузить внешние данные и последовательно их преобразовывать внутри базы данных. Внешние таблицы позволяют изменить архитектуру обработки – использовать потоковую (pipelined) загрузку и преобразование данных. Можно определить цепочку обработки данных и использовать ее “на лету” без хранения промежуточных результатов. Данные во внешних таблицах нельзя менять с помощью команд DML. Однако можно менять их вручную – редактируя или подменяя внешние файлы. Внешние таблицы используют возможности утилиты SQL*Loader по загрузке и преобразованию данных. Эта утилита предоставляет наиболее быстрый путь по загрузке форматированного текста в таблицы базы данных Oracle. При использовании внешних таблиц, необходимо помнить, что многократный произвольный доступ к строкам такой таблицы может оказаться менее быстрым, чем доступ к простой таблице из-за того, что индексы на внешнюю таблицу отсутствуют. Поэтому, при обращении к внешней таблице будут просматриваться все строки внешнего файла. Внешние таблицы не подменяют собой таблицы базы данных, они предназначены для потоковой обработки данных.

Пример создания внешней таблицы:

CREATE TABLE my_external

(employee_id, first_name, last_name)

ORGANIZATION EXTERNAL

(TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY ext_tab_dir LOCATION ('ext1.ext','ext2.ext'))

PARALLEL AS

SELECT employee_id, first_name, last_name FROM HR. EMPLOYEES;

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

DBA_EXTERNAL_TABLES – список атрибутов всех внешних таблиц в базе данных;

DBA_EXTERNAL_LOCATIONS – список плоских текстовых файлов и имена директорий, где они находятся.

Индексы на основе В*-дерева

Индексирование - очень важный аспект проектирования и разработки приложения. Если индексов слишком много, снизится производительность операторов. Если индексов не хватает, снизится производительность запросов (а следовательно, вставок, изменений и удалений). Правильное решение этой проблемы позволит обеспечить высокую производительность приложений.

Индексы на основе В*-дерева, или "обычные" индексы, - наиболее широко используемый тип индексной структуры в базе данных. По реализации они подобны двоичному дереву поиска. Цель их создания - минимизировать время поиска данных сервером Oracle. Сервер Oracle поддерживает все индексы при выполнении над таблицей команд DML.

-  Операции вставки приводят к вставке элемента индекса.

-  Удаление строки приводит только к логическому удалению элемента индекса.

-  Обновление ключевых столбцов приводит к логическому удалению и вставке индекса.

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

Пример создания индекса для таблицы employees, содержащий колонку job_id

CREATE INDEX emp_job_ix ON employees (job_id)

Индексы могут быть организованы и по убыванию (DESC), что позволяет отсортировать данные в структуре индекса от "больших" к "меньшим" (по убыванию), а не от меньших к большим (по возрастанию).

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

DBA_INDEXES – данные об индексах.

DBA_IND_COLUMNS – данные о колонках индексов.

Индексы с обращенным ключом

Это индексы на основе В*-дерева, байты ключа в которых инвертированы (REVERSE). Это используется для более равномерного распределения записей по индексу при вводе возрастающих значений ключей. Предположим, при использовании последовательности для генерации первичного ключа генерируются значения 987502 и т. д. Поскольку это последовательные значения, они будут попадать в один и тот же блок индекса, конкурируя за него. В индексе с обращенным ключом сервер Oracle будет индексировать значения 005789. Эти значения обычно будут далеко отстоять друг от друга в индексе, и вставки в индекс будут распределены по нескольким блокам.

Пример создания индекса с обращенным ключом:

CREATE INDEX ind_rev ON employees (commission_pct) REVERSE;

Индексы на основе битовых карт