Справочные материалы по СУБД Oracle

1. Синтаксис определения объектов Oracle 2

2. Системные и объектные привилегии Oracle 4

3. Системная структура Oracle 6

4. Язык PL/SQL 11

4.1. Список ключевых слов 11

4.2. Предопределённые типы данных PL/SQL 12

4.3. Предопределенные исключительные ситуации 14

4.4. Некоторые наиболее часто используемые функции Oracle 15

4.4.1. Функции, устанавливающие соответствие числовых кодов и символов 15

4.4.2. Преобразования букв различных слов строки 15

4.4.3. Символьные функции усечения и дополнения строк 15

4.4.4. Символьные функции преобразования строк 15

4.4.5. Функции связанные с поиском вхождений подстрок 15

4.4.6. Числовые функции возведения в степень и логарифмирования 16

4.4.7. Тригонометрические функции 16

4.4.8. Числовые функции, связанные с округлением 16

4.4.9. Некоторые числовые функции 17

4.4.10. Функции, оперирующие с датами 17

4.4.11. Функция преобразования типов данных 18

4.4.12. Функции замены аргументов 19

4.5. Примеры программ на языке PL/SQL 20

4.6. Скрипт SQL 23

5. Параметры Oracle 24

6. Скрипты Oracle 32

6.1. Создание дополнительных структур словаря данных 33

7. Примеры применения триггеров 36

7.1. Аудит с помощью триггеров 36

7.2. Ограничения целостности и триггеры 38

7.3. Реализация комплексных ограничений контроля 42

7.4. Триггеры и комплексные проверки полномочий 42

7.5. Триггеры и прозрачная регистрация событий 43

7.6. Триггеры и вычисляемые значения столбцов 44

8. Операции, влияющие на состояние объекта 45

9. Некоторые команды администрирования базы данных 45

9.1. Запуск базы данных 46

9.2. Остановка базы данных 46

9.3. Управление архивированием журналов повторного выполнения 47

9.4. Восстановление базы данных 48

9.5. Резервное копирование 51

1. Синтаксис определения объектов Oracle

  1. Оператор определения пользователей Oracle использует следующий синтаксис:

CREATE USER имя_пользователя IDENTIFIED { BY пароль | EXTERNALLY }

[DEFAULT TABLESPACE имя_табличной_области1]

[TEMPORARY TABLESPACE имя_табличной_области2]

[QUOTA {число_единиц [{ К | M }] | UNLIMITED }

ON имя _табличной области]

[PROFILE имя_профиля];

  1. Оператор создания табличного пространства использует следующий синтаксис:

CREATE TABLESPACE имя_табличной_области

DATAFILE спецификация_фаила_операционной_системы

[ SIZE целое_число [ { К | М } ] ]

[ AUTOEXTEND {OFF | ON [ NEXT целое_число{К | М} ] ]

[ MAXSIZE { UNLIMITED | целое_число{К | М}] , …]

[ DEFAULT STORAGE размер_памяти ]

[ {ОNLINE | OFFLINE}] [{PERMANENT | TEMPORARY} ]

  1. Оператор определения таблиц Oracle использует следующий синтаксис:

CREATE TABLE [имя_схемы.]имя_таблицы

( имя_столбца тип_данных_столбца [ DEFAULT выражение] [CONSTRAINT

имя_ограничения_целостности] [ограничение_целостности_столбца …]} ,…)

[{ CLUSTER имя_кластера ( имя_столбца [, …]) |

{PCTFREE целое | PCTUSED целое | INITRANS целое | MAXTRANS целое |

TABLESPACE имя_табличной_области | STORAGE размер_памяти |

{ RECOVERABLE | UNRECOVERABLE }} …]

[PARALLEL возможностъ_параллельной_обработки ]

[{ENABLE проверяемые_ограничения_целостности |

DISABLE игнорируемые_ограничения_целостности }…]

[AS запрос] [CACHE | NOCACHE];

(PCTFREE 0-99 (10) PCTUSED 1-99 (40) ) PCTFREE + PCTUSED ≤ 100%

Размер памяти устанавливается с помощью следующих параметров:

STORAGE (INITIAL целое_число{К | М} NEXT целое_число{К | М}

PCTINCREASE целое_число

MINEXTENTS целое_число MAXEXTENTS целое_число

FREELISTS целое_число FREELIST GROUPS целое_число)

Разбиение таблицы на разделы:

CREATE TABLE [имя_схемы.]имя_таблицы ( список_полей)

PARTITION BY RANGE (список_полей)

(PARTITION имя_раздела1 VALUES LESS THEN (список_значений_полей)

TABLESPACE имя_табличной_области1[,

PARTITION имя_раздела2 VALUES LESS THEN (список_значений_полей)

TABLESPACE имя_табличной_области2,…,

PARTITION имя_разделаN VALUES LESS THEN (MAXVALUE)

TABLESPACE имя_табличной_областиN]);

Удаление таблицы

DROP TABLE [имя_схемы.]имя_таблицы [ CASCADE CONSTRAINTS ];

  1. CREATE [UNIQUE] INDEX имя_индекса ON

{имя_таблицы (имя_столбца [ASC | DESC] [,имя_столбца [ASC | DESC]]…] |

CLUSTER имя_кластера}

[INITRANS n] [MAXTRANS n]

[TABLESPACE имя_табличной_области]

[STORAGE параметры_хранения] [PCTFREE n]

[NOSORT];

  1. Оператор определения представлений Oracle использует следующий синтаксис:

CREATE [OR REPLACE] [{FORCE | NO FORCE}]

VIEW [ имя_схемы.]имя_представления [альтернативное_имя …]

AS запрос WITH { READ ONLY | CHECK OPTION

[CONSTRAINT ограничение_целостности ]};

  1. Оператор определения синонима Oracle использует следующий синтаксис:

CREATE [PUBLIC] SYNONYM [имя_схемы.]имя_синонима

FOR [имя_схемы.]имя_объекта[@имя_связиБД]

  1. Оператор определения последовательности использует следующий синтаксис:

CREATE SEQUENCE [имя_схемы.]имя_последовательности

[ INCREMENT BY приращение]

[ START WITH начальное_значение]

[ MAXVALUE наибольшее_значение | NOMAXVALUE ]

[ MINVALUE наименьшее_значение | NOMINVALUE ]

[CYCLE | NOCYCLE ]

[ CACHE число_элементов | NOCACHE ]

[ ORDER | NOORDER ];

  1. Оператор определения роли Oracle использует следующий синтаксис:

CREATE CLUSTER имя_кластера

(столбец_кластерного_индекса тип_столбца[,…])

[параметры_физического_хранения];

  1. Оператор определения роли Oracle использует следующий синтаксис:

CREATE ROLE имя_роли

[ { NOT IDENTIFIED | IDENTIFIED { BY пароль | EXTERNALLY }}];

  1. Оператор создания связи с удаленной БД Oracle использует следующий синтаксис:

CREATE [ PUBLIC ] DATABASE LINK имя_связи_БД

[CONNECT TO имя_пользователя IDENTIFIED BY пароль_пользователя]

USING ‘строка_связи’

  1. Схема пользователя может быть создана за одну операцию:

CREATE SCHEMA AUTHORIZATION имя_пользователя

SQL-команды_создания_объектов

/

2. Системные и объектные привилегии Oracle

Сводный перечень всех системных привилегий приведен в табл.1. Опция ANY означает разрешение на применение команды в любой схеме. Привилегия без опции ANY разрешает действие только в своей схеме. Опция PUBLIC разрешает соответствующие действия с общесистемными объектами.

Таблица 1. Системные привилегии Oracle

Системная привилегия

Разрешение на

ANALYZE ANY

анализ любых таблиц, индексов и кластеров в любых схемах с помощью команды ANALYZE

AUDIT ANY

аудит любых объектов в любых схемах

ALTER DATABASE

изменение базы данных

ALTER RESOURCE COST

установку стоимости системных ресурсов

ALTER / AUDIT SYSTEM

изменение системы/аудит системных событий

CREATE / ALTER / DROP [ANY] CLUSTER

создание/изменение/удаление кластеров [во всех схемах]

TRUNCATE ANY

опустошение любых таблиц и кластеров

CREATE / DROP [PUBLIC] DATABASE LINK

создание/удаление связей базы данных

CREATE / ALTER / DROP [ANY] INDEX

создание/изменение/удаление индексов [во всех схемах]

CREATE / ALTER / DROP [ANY] PROCEDURE

создание/изменение/удаление [любой] процедуры, функции, [тела] пакета

EXECUTE ANY PROCEDURE

выполнение любой процедуры, функции, пакета;

CREATE / ALTER / DROP PROFILE

создание/изменение/удаление профилей

CREATE / ALTER / DROP [ANY] ROLE

создание/изменение/удаление [любых] ролей

GRANT ANY ROLE

предоставление любых ролей в базе данных

GRANT ANY PRIVILEGE

предоставление любых системных привилегий

CREATE / ALTER / DROP ROLLBACK SEGMENT

создание/изменение/удаление сегментов отката

CREATE / ALTER SESSION

соединение/изменение параметров сессии

RESTRICTED SESSION

соединение при установке STARTUP RESTRICT

CREATE / ALTER / DROP [ANY] SEQUENCE

создание/изменение/удаление [любых] последовательностей

SELECT ANY SEQUENCE

просмотр из любых последовательностей

CREATE / ALTER / DROP [ANY] SNAPSHOT

создание/изменение/удаление [любых] синонимов

CREATE / DROP [PUBLIC] / [ANY] SYNONYM

создание/изменение/удаление [общих]/[любых] синонимов

CREATE / ALTER / DROP [ANY] TABLE

создание/изменение/удаление [любых] таблиц

BACKUP ANY TABLE

экспорт записей из любых таблиц

COMMENT ANY TABLE

комментирование таблиц, представлений, столбцов

SELECT / UPDATE ANY TABLE

просмотр/обновление записей любых таблиц

INSERT / DELETE ANY TABLE

вставка/удаление записей любых таблиц

LOCK ANY TABLE

блокирование любых таблиц в любых схемах

CREATE / ALTER / DROP TABLESPACE

создание/изменение/удаление табличных областей

MANAGE TABLESPACE

Управление табличными областями (ТО) (ONLINE/OFFLINE/BACKUP)

UNLIMITED TABLESPACE

неограниченную квоту памяти во всех ТО

CREATE / ALTER / DROP [ANY] TRIGGER

создание/изменение/удаление [любых] триггеров

CREATE / ALTER / DROP USER

создание/изменение/удаление пользователей

BECOME USER

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

CREATE / ALTER / DROP [ANY] VIEW

создание/изменение/удаление [любых] представлений

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

Таблица 2. Объектные привилегии Oracle

Объектная привилегия

Разрешение на

ALL

все действия с объектом

ALL PRIVILEGES

то же, что ALL

ALTER

изменение определения объекта

DELETE

удаление строк из таблицы

EXECUTE

выполнение объекта, на обращение к его переменным

INDEX

создание индексов по таблице

INSERT

вставку строк в таблицу

REFERENCES

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

SELECT

выборку строк из таблицы, моментального снимка

UPDATE

изменение строк в таблице.

Не все объектные привилегии применяются для всех типов объектов. Возможное использование объектных привилегий приведено в табл. 3.

Таблица 3. Использование объектных привилегий

Объектная привилегия

Таблицы

Представ-ления

Последова-тельности

Функции Пакеты

Снапшоты

ALTER

+

 

+

  

DELETE

+

+

   

EXECUTE

   

+

 

INDEX

+

    

INSERT

+

+

   

REFERENCES

+

    

SELECT

+

+

+

 

+

UPDATE

+

+

   

3. Системная структура Oracle

Таблица 4. Файлы первоначального варианта БД (для Oracle7).

Размер (байт)

Имя

Примечания

754

configeagl.ora

Файл параметров конфигурации базы

3478

crdb2eagl.sql

2-й файл инсталляции базы (можно удалить)

1021

crdbeagl.sql

1-й файл инсталляции базы (можно удалить)

59392

ctrl1eagl.ctl

1-й управляющий файл

59392

ctrl2eagl.ctl

2-й управляющий файл

59392

ctrl3eagl.ctl

3-й управляющий файл

6571

init.ora

образец файла параметров (можно удалить)

4622

initeagl.ora

файл параметров запуска экземпляра

4607

initeagl_0.ora

файл параметров для инсталляции (можно удалить)

514048

log1eagl.dbf

1-й журнальный файл регистрации изменений

514048

log2eagl.dbf

2-й журнальный файл регистрации изменений

514048

log3eagl.dbf

3-й журнальный файл регистрации изменений

158186

migrate.bsq

скрипт перехода от Oracle6 к Oracle7 (можно удалить)

8390656

rbseagl.dbf

табличное пространство для сегментов отката

372

sgadefeagl.dbf

файл, показывающий, что запущена база данных eagl

71299

sql.bsq

начальный скрипт создания словаря данных (можно удалить)

41945088

systeagl.dbf

системное (SYSTEM) табличное пространство

565248

tempeagl.dbf

табличное пространство для временных сегментов

15730688

tooleagl.dbf

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

1050624

usreagl.dbf

табличное пространство для сегментов пользователей

(Обязательные файлы выделены полужирным шрифтом).

Таблица 5. Файлы первоначального варианта БД для Oracle8 под Windows NT.

Имя файла

Размер

Назначение

inst.ora

5 278

 

initorcl.ora

5 155

файл параметров запуска экземпляра

sys1orcl.ora

10 486 272

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

rbs1orcl.ora

5 243 392

табличное пространство для сегментов отката

tmp1orcl.ora

2 97 664

табличное пространство для временных сегментов

usr1orcl.ora

3 146 240

табличное пространство для сегментов пользователей

ctl1orcl.ora

159 232

управляющий файл

log1orcl.ora

205 312

1-й журнал регистрации изменений

log2orcl.ora

205 312

2-й журнал регистрации изменений

Фрагмент файла configeagl.ora:

control_files = /home_stat/statistic/dbs/cntrleagl1.ctl, 1-й управляющий файл

/home_stat/statistic/dbs/cntrleagl2.ctl, 2-й управляющий файл

/home/oracle80/dbs/cntrleagl3.ctl 3-й управляющий файл

background_dump_dest = /home/oracle80/rdbms/log место dump-файлов процессов

#core_dump_dest = /home_stat/statistic/dbs место dump-файлов экземпляра

#user_dump_dest = /home/oracle80/rdbms/log место dump-файлов пользователя

#log_archive_dest = /home/oracle80/dbs/arch/arch.log место размещения и

обозначение файлов архивируемых журналов

db_block_size = 8192 размер блока базы данных

db_name = eagl имя базы данных (до 8 символов)

Несколько фрагментов из реального сигнального файла:

Shutting down instance (normal) # нормальное закрытие

Starting ORACLE instance (normal) # сообщения нормального запуска экземпляра

Starting up ORACLE RDBMS Version: 8.0.1.2.0.

System parameters with non-default values: # processes = 150

# shared_pool_size = 10000000

starting up 1 dispatcher(s) for network protocol ‘ipc’… #

starting up 1 dispatcher(s) for network protocol ‘tcp’… #

alter rollback segment r02 storage (optimal 256K) # запрос и

Completed: alter rollback segment r02 storage (optimal 256K) # выполнение

alter rollback segment r04 offline # команд действия с

Completed: alter rollback segment r04 offline # сегментами отката

create tablespace indexes # создание табличного

datafile ‘/dev/rdsk/c0d2s3’ size 487200K # пространства с

default storage (initial 1M next 1M pctincrease 0) # заданными параметрами

Thread 1 advanced to log sequence 1350 # указание текущего файла

Current log# 1 seq# 1350 mem# 0: /dev/rdsk/c1d1s4 # журнала регистрации

Thread 1 advanced to log sequence 1351 # переключение на

Current log# 4 seq# 1351 mem# 0: /dev/rdsk/c1d1s2 # следующий файл журнала

ARCH: Archival stopped, error occurred. Will continue retrying # сбой arch

ORACLE Instance eagl – Archival Error # (нет памяти для архивации журнала)

Errors in file /home/oracle80/rdbms/log/ora_10681.trc: # внутренняя ошибка

ORA-00600: internal error code, arguments: [729], # Oracle – ORA-00600

[400], [space leak], [], [], [], [], [] # (самое опасное место !)

Например, для ОС UNIX имена трассовых файлов могут выглядеть так:

-rw-r—r—

oracle80

dba

700564

янв 10

alert_eagl.log

# сигнальный файл

-rw-r——

oracle80

dba

668

дек 18

d000_1714.trc

# сбой диспетчера

-rw-r——

oracle80

zic

233172

дек 15

ora_15341.trc

# файл процесса user

-rw-r——

oracle80

dba

542

дек 9

smon_1545.trc

# сбой процесса smon

Фрагмент сбойного пользовательского трассового файла:

ORACLE_SID = eagl # идентификатор экземпляра

Oracle process number: 14 Unix process id: 15341 # номера процессов Oracle/UNIX

Sun Dec 15 12:04:17 1996 # дата

*** SESSION ID:(10.6772) # внутренний номер сессии

ksedmp: internal or fatal error # сообщение о типе ошибки

ORA-00600: код внутренней ошибки, аргументы: [17005],… # код ошибки

Current SQL statement for this session: # SQL- предложение, в котором

create table CARTA /* ОБЪЕКТ » КАРТА» */ # произошла ошибка

(NSYST_OPZ number(9) constraint OPZ_NS PRIMARY KEY # В данном случае ошибка

USING INDEX # связана с многократным использованием

табличных ограничений на строчном уровне

Некоторые базовые объекты:

AUD$ – таблица audit-информации;

FILE$ – таблица файлов;

IND$ – таблица индексов;

LINK$ – таблица связей базы данных;

OBJ$ – таблица объектов;

SEG$ – таблица сегментов;

SYN$ – таблица синонимов;

TAB$ – таблица таблиц;

TS$ – таблица табличных пространств;

USER$ – таблица пользователей;

VIEW$ – таблица представлений.

Некоторые представления словаря-справочника данных:

DBA/ALL/USER_INDEXES – все/доступные/пользовательские индексы;

DBA/ALL/USER_IND_COLUMNS – все/доступные/пользовательские колонки индексов;

DBA/ALL/USER_OBJECTS – все/доступные/пользовательские объекты;

DBA/ALL/USER_SYNONYMS – все/доступные/пользовательские синонимы;

DBA/ALL/USER_TABLES – все/доступные/пользовательские таблицы;

DBA/ALL/USER_TAB_COLUMNS – все/доступные/пользовательские колонки таблиц;

DBA/ALL/USER_TAB_PRIVS – все/доступные/пользовательские привилегии на таблицы;

DBA/ALL/USER_USERS – все/известные/сам пользователь;

DBA/ALL/USER_VIEWS – все/доступные/пользовательские представления.

Примеры данных, извлекаемых DBA-представлениями:

select * from dba_data_files ;

FILE_NAME

FILE_ID

TABLESPACE

BYTES

BLOCKS

STATUS_NAME

/dev/rdsk/c1d2s1

5

USERS

419430400

51200

AVAILABLE

/dev/rdsk/c1d2s2

6

USERS

419430400

51200

AVAILABLE

/home/oracle80/ dbs/systeagl.dbf

1

SYSTEM

20971520

2560

AVAILABLE

/home/oracle80/ dbs/rbseagl.dbf

2

RBS

12582912

1536

AVAILABLE

/dev/rdsk/c0d0s13

3

TEMP

111616000

13625

AVAILABLE

/home/oracle80/ dbs/tooleagl.dbf

4

TOOLS

10485760

1280

AVAILABLE

/dev/rdsk/c0d2s3

8

INDEXES

498892800

60900

AVAILABLE

/dev/rdsk/c0d0s6

7

RBS

125829120

15360

AVAILABLE

/home/oracle80/ dbs/tool_2eagl.dbf

9

TOOLS

6291456

768

AVAILABLE

select * from dba_free_space;

TABLESPACE_NAME

FILE_ID

BLOCK_ID

BYTES

BLOCKS

SYSTEM

1

1718

6905856

843

SYSTEM

1

1120

81920

10

RBS

2

662

5242880

640

RBS

7

2

2129920

260

USERS

5

7662

81920

10

USERS

5

8637

81920

10

USERS

5

8182

81920

10

USERS

5

8647

1064960

130

INDEXES

8

8832

983040

120

INDEXES

8

11642

4177920

510

select * from dba_users;

USERNAME

USER_ID

PASSWORD

TABLESPACE

DEFAULT_ TEMPORARY

CREATED

PROFILE

SYS

3

******

SYSTEM

TEMP

24.10.95

DEFAULT

SYSTEM

5

******

TOOLS

TEMP

24.10.95

DEFAULT

ADM02

21

******

USERS

TEMP

25.10.95

DEFAULT

OP01

22

******

USERS

TEMP

25.10.95

DEFAULT

V$-объекты (динамические таблицы производительности):

V$DATABASE – информация о базе данных в целом;

V$DATAFILE – информация о файлах базы данных;

V$FIXED_TABLE – перечень всех X$-объектов;

V$LICENSE – информация о лицензионных соглашениях данной поставки;

V$LOCK – информация о имеющихся в данный момент блокировках;

V$LOG – информация о журналах регистрации изменений в базе данных;

V$LOGFILE – информация о файлах журналов регистрации;

V$PARAMETER – перечень и текущие значения параметров запуска экземпляра;

V$SESSION – информация об установленных в данный момент сессиях;

V$SYSSTAT – перечень и текущие значения параметров системной статистики

(свыше 120 различных параметров).

Несколько примеров данных, извлекаемых из V$-объектов:

select * from v$database;

NAME CREATED LOG_MODE CHECKPOINT ARCHIVE_CHANGE#

eagl 10/24/95 14:53:17 ARCHIVELOG 11043692 8638787

select * from v$log;

GROUP

SEQUENCE

B

Y

T

E

S

M

E

M

B

E

R

S

ARC

S

T

A

T

U

S

FIRST

FIRST_

TIME

T

H

R

E

A

D

_CHANGE

1

1

1380

47185920

1

YES

INACTIVE

8638787

11/22/96

12:40:28

4

1

1381

47185920

1

YES

INACTIVE

8793634

12/05/96

14:49:51

5

1

1382

47185920

1

NO

CURRENT

11019680

12/20/96

14:17:54

select * from v$logfile;

GROUP# STATUS MEMBER

1 /dev/rdsk/c1d1s4

4 /dev/rdsk/c1d1s2

5 /dev/rdsk/c1d1s3

select * from v$fixed_table;

NAME OBJECT_ID TYPE

X$KQFTA 4294950912 TABLE

X$KQFVI 4294950913 TABLE

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

select * from dict;

ALL_CATALOG

Все таблицы, представления, синонимы, последовательности, доступные пользователю

ALL_DB_LINKS

Связи базы данных, доступные пользователю

DBA_OBJECTS

Все объекты в базе данных

DBA_ROLES

Все роли, которые существуют в БД

USER_EXTENTS

Экстенты, принадлежащие пользователю

USER_VIEWS

Определения представлений, принадлежащих пользователю

DUAL

Псевдотаблица из одного столбца и одной строки

DICT

Синоним для DICTIONARY

TABS

Синоним для USER_TABLES

4. Язык PL/SQL

4.1. Список ключевых слов

Ниже приведен список ключевых (зарезервированных) слов:

%FOUND

%ISOPEN

%NOTFOUND

%ROWCOUNT

%ROWTYPE

%TYPE

AS

BEGIN

BFILE

BINARY_INTEGER

BLOB

BODY

BOOLEAN

CHAR

CHAR VARYING

CHARACTER

CLOB

CLUSTER

CURSOR

DATABASE LINK

DATE

DEC

DECIMAL

DECLARE

DOUBLE PRECISION

ELSE

ELSIF

END

EXCEPTION

EXEC

EXIT

FALSE

FETCH

FLOAT

FOR

FUNCTION

IF

IN

INDEX

INT

INTEGER

IS

LONG

LOOP

NATURAL

NATURALN

NULL

NUMBER

NUMERIC

ON

OPEN

OTHERS

PACKAGE

PLS_INTEGER

POSITIVE

POSITIVEN

PROCEDURE

RAISE

RAW

REAL

RECORD

RETURN

ROLE

SEQUENCE

SIGTYPE

SMALLINT

SNAPSHOT

SYBTYPE

SYNONYM

TABLE

TABLESPACE

THEN

TRIGGER

TRUE

TYPE

USER

VARCHAR

VARCHAR2

VIEW

WHEN

WHILE

4.2. Предопределённые типы данных PL/SQL

Таблица 6. Некоторые скалярные типы данных PL/SQL и связанные с ними подтипы.

Тип данных

Подтип

Описание

BINARY_INTEGER

NATURAL, NATURALN,

POSITIVE, POSITIVEN,

SIGTYPE

Целые числа со знаком. Использует библиотечную арифметику. NATURAL, NATURALN – только неотрицательные целые числа; последний запрещает null-значения. POSITIVE, POSITIVEN – только положительные целые числа; последний запрещает null-значения. SIGTYPE – знаковый тип: -1, 0 и 1.

NUMBER (точность, масштаб)

DEC, DECIMAL,

DOUBLE PRECISION,

FLOAT(точность),

INTEGER, INT,

NUMERIC, REAL,

SMALLINT

Числа с фиксированной или плавающей точкой. Использует библиотечную арифметику.

PLS_INTEGER

 

Целые числа со знаком. Для ускорения вычислений использует машинную арифметику.

CHAR(размер)

CHARACTER(размер)

Строки символов фиксированной длины. Максимальный размер 32767 байтов (для БД – 2000 байтов).

VARCHAR2(размер)

VARCHAR(размер), STRING

Строки символов переменной длины. Максимальный размер 32767 байтов (для БД – 4000 байтов).

DATE

 

Даты, часы, минуты, секунды.

BOOLEAN

 

Логические значения: TRUE – истина, FALSE – ложь, NULL – null-значения.

CLOB

 

Большие однобайтовые символьные объекты.

BLOB

 

Большие двоичные объекты.

BFILE

 

Указатели на объекты LOB, управляемые файловыми системами, внешними по отношению к БД.

Преобразования типов данных

Таблица 7. Явные преобразования типов данных.

Откуда

Куда

CHAR

DATE

NUMBER

RAW

ROWID

CHAR

 

TO_DATE

TO_NUMBER

HEXTORAW

CHARTORAWID

DATE

TO_CHAR

    

NUMBER

TO_CHAR

TO_DATE

   

RAW

RAWTOHEX

    

ROWID

ROWIDTOCHAR

    

Таблица 8. Неявные преобразования типов данных.

Откуда

Куда

BINARY_INTEGER

CHAR

DATE

LONG

NUMBER

RAW

ROWID

VARCHAR2

BINARY_ INTEGER

 

да

 

да

да

  

да

CHAR

да

 

да

да

да

да

да

да

DATE

 

да

 

да

   

да

LONG

 

да

   

да

 

да

NUMBER

да

да

 

да

   

да

RAW

 

да

 

да

   

да

ROWID

 

да

     

да

VARCHAR2

да

да

да

да

да

да

да

 

Таблица 9. Предопределенные типы данных PL/SQL

Тип и параметры типа

Минимальное значение (размер)

Максимальное значение (размер)

Примечание

Значение параметров по умолчанию

Числовые типы

BYNARY_ INTEGER

-231-1

231 –1

 

NATURAL

0

2147483647

 

POSITIVE

1

2147483647

 

NUBMER

[(точность, масштаб)]

1.0E-129

9.99E125

точность: 1÷38

масштаб:

-84÷127

точность – 38

масштаб = 0

подтипы NUBMER

DEC, DECIMAL, PRECISION, DOUBLE FLOAT, SMALLIN,T INTEGER,NUMERIC,REAL,INT

аналогично базовому типу

 

Символьные типы

CHAR [(длина)]

1

32767

 

длина = 1

подтипы CHAR

STRING, CHARACTER

аналогично базовому типу

 

VARCHAR2 (длина)

1

32767

 

подтип VARCHAR2

VARCHAR

должен измениться

 

LONG

1

32760

 

RAW (длина)

1

32767

 

LONG RAW

1

32760

 

Логический тип

BOOLEAN

TRUE, FALSE, NULL

Календарный тип

DATE

1.01.14712 г. до н.э.

31.12.314712г. н.э.

При отсутствии даты – первый день текущего месяца; при отсутствии времени – полночь.

Тип «Идентификатор строки»

ROWID

  

6-байтовые двоичные значения. Подтип типа CHAR.

Таблица 10. Старшинство операций.

Оператор

Операция

**, NOT

возведение в степень, логическое отрицание

+, –

тождественность, отрицание

*, /

умножение, деление

+, –, ||

сложение, вычитание, конкатенация

=, !=, <, >, <=, >=, LIKE, IS NULL, IN, BETWEEN

сравнение

AND

конъюнкция

OR

дизъюнкция

Таблица 11. Семейства типов данных PL/SQL.

Скалярные типы

Составные типы

BINARY_INTEGER

DEC

DECIMAL

DOUBLE PRECISION

FLOAT

INT

INTEGER

NATURAL

NUMBER

NUMERIC

POSITIVE

REAL

SMALLINT

CHAR

CHARACTER

LONG

LONG RAW

RAW

ROWID

STRING

VARCHAR

VARCHAR2

RECORD

TABLE

VARRAY

  

DATE

BOOLEAN

4.3. Предопределенные исключительные ситуации

В табл. 12 перечислены наиболее часто встречающиеся исключительные ситуации.

Таблица 12. Некоторые предопределенные исключительные ситуации

Имя предопределенной исключительной ситуации

Описание предопределенной исключительной ситуации

LOGIN_DENIED

Неуспешное подключение к серверу (например, введен ошибочный пароль)

NOT_LOGGED_ON

Попытка выполнить действие без подключения к серверу

INVALID_CURSOR

Ссылка на недопустимый курсор или недопустимая операция с курсором

NO_DATA_FOUND

Не найдены данные, соответствующие оператору select into

DUP_VAL_ON_INDEX

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

TOO_MANY_ROWS

Оператор select into возвращает более одной строки

VALUE_ERROR

Арифметическая ошибка, ошибка преобразования или усечения

ZERO_DIVIDE

Деление на 0

4.4. Некоторые наиболее часто используемые функции Oracle

4.4.1. Функции, устанавливающие соответствие числовых кодов и символов

  • Функция СHR(номер_символа) возвращает символ, который имеет соответствующее значение параметра номер_символа в используемом коде (обычно ASCII).
  • Функция ASCII(символ) возвращает числовое значение (номер) символа, заданного параметром символ.

4.4.2. Преобразования букв различных слов строки

  • Функция INITCAP(строка) преобразует каждую первую букву слов параметра строка в прописную, а все последующие — в строчные.
  • Функция LOWER(строка) преобразует каждую букву параметра строка в строчную.
  • Функция UPPER(строка) преобразует каждую букву параметра строка в прописную.

4.4.3. Символьные функции усечения и дополнения строк

  • Функция LPAD(строка_1, число_символов [, символ_наполнитель]) возвращает значение параметра строка_1, дополненное слева до числа символов, которое задано параметром число_символов, символом-наполнителем, заданным параметром символ_наполнитель. По умолчанию символом-наполнителем является пробел.
  • Функция RPAD(строка_1, число_символов [, символ_наполнитель]) возвращает значение параметра строки_1, дополненное справа до числа символов, которое задано параметром число_символов, символом-наполнителем, заданным параметром символ_наполнитель. По умолчанию символом-наполнителем является пробел.
  • Функция LTRIM(строка_1 [, строка_шаблон]) возвращает усеченное слева значение параметра строка_1. Из строки параметра строка_1 символы удаляются слева до тех пор, пока удаляемый символ входит в множество символов параметра строка_шаблон. По умолчанию строка_шаблон состоит из символа пробела.
  • Функция RTRIM(строка_1 [, строка_шаблон]) возвращает усеченное справа значение параметра строка_1. Из строки параметра строка_1 символы удаляются справа до тех пор, пока удаляемый символ входит в множество символов параметра строка_шаблон. По умолчанию строка_шаблон состоит из символа пробела.

4.4.4. Символьные функции преобразования строк

  • Функция TRANSLATE(строка_1, символы_поиска, символы_замены) возвращает значение параметра строка_1, для которой выполнено следующее преобразование. Все вхождения параметра символ_поиска замещены значением параметра символ_замены. Если в строке символы_поиска содержится больше символов, чем в строке символы_замены, то символы, которым нет соответствия, замещаются на пустой символ (т.е. исключаются из результирующей строки). Функция Translate может применяться, в частности, для обработки текстов, подготовленных с использованием различных раскладок клавиатур.

4.4.5. Функции связанные с поиском вхождений подстрок

  • Функция SUBSTR(строка_1, позиция [, длина_подстроки]) возвращает подстроку параметра строка_1, начиная с позиции, заданной параметром позиция, и длиной, заданной параметром длина_подстроки. Если параметр длина_подстроки не задан, то возвращается подстрока до конца строки, заданной параметром строка_1.
  • Функция INSTR(строка_1, строка_поиска [, позиция_начала_поиска [,номер_вхождения]]) возвращает позицию вхождения строки, задаваемой параметром строка_поиска, в строку, задаваемую параметром строка_1. Позиция начала поиска задается необязательным числовым параметром позиция_начала_поиска, а необязательный параметр номер_вхождения задает требуемй номер вхождения строки поиска в основную строку. Значения по умолчанию для необязательных параметров 1. При отсутствии требуемого параметра вхождения строки поиска в основную строку функция возвращает значение 0.
  • Функция LENGTH(строка) возвращает длину строки, заданной параметром строка.

4.4.6. Числовые функции возведения в степень и логарифмирования

  • Функция ЕХР(числовой_аргумент) возвращает число е (основание натуральных логарифмов) в степени параметра числовой _аргумент.
  • Функция LN(числовой_аргумент) возвращает натуральный логарифм положительного параметра числовой_аргумент.
  • Функция POWER(основание, числовой_аргумент) возвращает значение параметра основание в степени параметрам числовой_аргумент. Если параметр основание отрицательный, то параметр числовой_аргумент должен быть целым.
  • Функция SQRT(числовой_аргумент) возвращает значение квадратного корня параметра основание в степени параметра числовой _аргумент. Если параметр основание отрицательный, то возвращается сообщение об ошибке.
  • Функция LOG(основание, числовой_аргумент) возвращает логарифм по основанию, заданному параметром основание, параметра числовой_аргумент. Параметр основание может быть любым положительным числом» за исключением 1, а параметр числовой_аргумент должен быть положительным числом.

4.4.7. Тригонометрические функции

  • Функции SIN(числовой_аргумент), СOS(числовой_аргумент), TAN(чucлoвoй-_apгyмeнm) возвращают соответственно синус, косинус и тангенс параметра числовой_аргумент. Параметр числовой_аргумент предполагается заданным в радианах.
  • Функции АSIN(числовой_аргумент) и АСОS(числовой_аргумент) возвращают соответственно арксинус и арккосинус параметра числовой_аргумент. Параметр числовой_аргумент предполагается находящимся в диапазоне от -1 до 1. При вычислении функции с параметром числовой_аргумент вне указанного диапазона, выдается сообщение об ошибке. Функция АTAN(числовой_аргумент) возвращает арктангенс параметра числовой_аргумент.
  • Функции SINH(числовой_аргумент), СОSH(чнсловой_аргумент), TANH(чucлoвoй-_apгyмeнm) возвращают соответственно гиперболический синус, гиперболический косинус и гиперболический тангенс параметра числовой_аргумент.

4.4.8. Числовые функции, связанные с округлением

  • Функция ROUND(числовой_аргумент [, позиция]) округляет значение параметра числовой_аргумент с точностью, определяемой параметром позиция. Параметр позиция определяет число десятичных знаков после запятой. Если параметр позиция отрицательный, то аргумент округляется до целых чисел соответствующего масштаба (для значения параметра -1 до десятков, -2 до сотен и т.д.). Значение параметра позиция по умолчанию 0.
  • Функция TRUNC (чucлoвoй_apгумент [, позиция]) усекает значение параметра числовой _аргумент с точностью, определяемой параметром позиция. Параметр позиция определяет число десятичных знаков после запятой. Если параметр позиция отрицательный, то аргумент округляется до целых чисел соответствующего масштаба (для значения параметра -1 до десятков, от -2 до сотен и т.д.). Значение параметра позиция по умолчанию 0.
  • Функция FLOOR(числовой_аргумент) возвращает наибольшее целое, меньшее или равное значению параметра числовой_аргумент.
  • Функция СЕIL(числовой_аргумент) возвращает наименьшее целое, большее или равное значению параметра числовой__аргумент.

4.4.9. Некоторые числовые функции

  • Функция ABS(числовой_аргумент) возвращает абсолютное значение числа, заданного параметром числовой_аргумент.
  • Функция SIGN(числовой_аргумент) возвращает -1, если параметр числовой_аргумент меньше 0, возвращает 0, если параметр числовой_аргумент равен 0, и возвращает 1, если параметр числовой _аргумент больше 0.
  • Функция MOD(числовой_аргумент, основание) возвращает остаток от деления параметра числовой_аргумент на значение, определяемое параметром основание. Использование отрицательных значений параметра основание не рекомендуется, поскольку результат не соответствует принятому определению модуля числа.

4.4.10. Функции, оперирующие с датами

  • Функция SYSDATE возвращает дату и время, определяемые средствами операционной системы локального приложения.
  • Функция ROUND(дата [,формат]) округляет значение параметра дата по шаблону, определяемому параметром формат. Если параметр формат опущен, то аргумент дата округляется до дней (время в начале дня устанавливается в полночь).
  • Функция TRUNC(дата [,формат]) усекает значение параметра дата по шаблону, определяемому параметром формат. Если параметр формат опущен, то аргумент дата усекается до ближайшего дня (время в начале дня устанавливается в полночь).

Возможные значения параметра формат для даты представлены в табл. 13.

  • Функция NEXT_DAY(дата, название_дня) возвращает дату дня, который является первым днем, более поздним, чем текущая дата с названием, совпадающим с указанным параметром название_дня.

Функции ROUND, TRUNC, NEXT_DAY обычно используются для вычисления календарных интервалов.

Таблица 13. Значения параметра формат для даты.

Модель формата

Описание

CC,SCC

век (S префиксует даты до н.э. минусом)

YYYY,SYYYY

год (S префиксует даты до н.э. минусом)

IYYY

год в стандарте ISO

YYY,YY,Y

последние три, две или одна цифра года

IYY,IY,I

то же для года ISO

Y,YYY

год с запятой

YEAR,SYEAR

год прописью (S префиксует даты до н.э. минусом)

RR

последние две цифры года в новом веке

BC,AD

индикатор BC или AD

B.C.,A.D.

индикатор B.C. или A.D.

Q

квартал (1-4)

MM

месяц (1-12)

RM

римский номер месяца (I-XII)

MONTH

имя месяца

MON

сокращенное имя месяца

WW

неделя года (1-53)

IWW

неделя года (1-52 или 1-53) по ISO

W

неделя месяца (1-5)

DDD

день года (1-366)

DD

день месяца (1-31)

D

день недели (1-7)

DAY

имя дня

DY

сокращенное имя дня

J

юлианский день (число дней с 1 января 4712 г. до н.э.)

AM,PM

индикатор полудня

A.M.,P.M.

индикатор полудня с точками

HH,HH12

час дня (1-12)

HH24

час суток (0-23)

MI

минута (0-59)

SS

секунда (0-59)

SSSSS

секунд после полуночи (0-86399)

4.4.11. Функция преобразования типов данных

  • Функция ТО_CHAR(числовой_аргумент [,формат]) возвращает результат преобразования значения параметра числовой _аргумент типа NUMBER в строку типа VARCHAR2. Если параметр формат опущен, числовой_аргумент преобразовывается в строку с длиной, достаточной для хранения всех значащих цифр. Некоторые значения параметра формат для преобразования числовых значений представлены в табл. 14.

Таблица 14. Некоторые значения параметра числовой формат.

Параметр формат

Тип выводимого результата

9

Выводится цифра. Лидирующий 0 заменяется пробелом.

0

Выводится цифра. Лидирующий 0 выводится.

ЕЕЕЕ

Результат выводится в экспоненциальной нотации.

G

Выводится символ-разделитель (обычно запятая).

  • Функция TO_DATE(символьный_аргумент [формат]) возвращает результат преобразования значения параметра символьный_аргумент символьного типа в тип DATE. Если параметр формат опущен, символьный_аргумент должен соответствовать формату даты, принятой в системе по умолчанию. Наиболее употребительные значения параметра формат представлены выше в табл. 13. Обычно функцию TO_DATE используют для преобразования даты из используемого некоторым приложением формата в стандартный формат системы.
  • Функция ТО_NUMBER(символьный_аргумент) возвращает результат преобразования значения параметра символьный_аргумент символьного типа в аргумент типа NUMBER. Параметр символъный_аргумент может представлять числа в любой допустимой Oracle нотации.

4.4.12. Функции замены аргументов

  • Функция NVL(аргумент_1, аргумент_2) возвращает аргумент_2, если аргумент_1 имеет неопределенное значение (NULL), в противном случае возвращается аргумент_1. Тип данных возвращаемого значения определяется типом данных параметра аргумент_1.
  • Функция DECODE(выражение, аргумент_1, результат_1[, аргумент_2, результат_2, …[значение_по_умолчанию]]) возвращает значение параметра результат_х, если параметр выражение совпадает с параметром аргумент_х, где х принимает значение 1,2,… Если совпадения нет, то возвращается значение_по_умолчанию. Если этот параметр не задан, то возвращается неопределенное значение (NULL).

Таблица 15. Моды параметров.

IN

OUT

IN OUT

по умолчанию

задается явно

задается явно

передает значение подпрограмме

возвращает значение вызывающей подпрограмме

передает начальное значение подпрограмме; возвращает обновленное значение вызывающей подпрограмме

формальный параметр выступает как константа

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

формальный параметр выступает как инициализированная переменная

фактический параметр может быть константой, инициализированной переменной, литералом или выражением

фактический параметр должен быть переменной

фактический параметр должен быть переменной

встречен вызов процедуры

 
  

сравнить имя вызываемой про-цедуры с именами всех процедур, объявленных в текущей сфере

перейти в окружающую сферу

 
 

да↑

найдено совпадение ?

нет

есть окружающая сфера?

 
 

да

  

нет

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

 
  

нет

найдено совпадение ?

 
 

да

 

несколько совпадений ?

да

 
  
 

нет

 

сгенерировать синтаксическую ошибку

разрешить вызов

 

Рис. 1. Разрешение вызовов компилятором PL/SQL

4.5. Примеры программ на языке PL/SQL

1. Пакет для работы с таблицей «Сотрудники» (emp).

CREATE OR REPLACE PACKAGE emp_act AS

TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);

CURSOR desc_salary RETURN EmpRecTyp;

count_emp NUMBER;

PROCEDURE hire_emp

(tnum NUMBER, ename CHAR, job CHAR, vsal NUMBER, deptno NUMBER);

PROCEDURE fire_emp (emp_id NUMBER);

END emp_act;

CREATE OR REPLACE PACKAGE BODY emp_act AS

CURSOR desc_salary RETURN EmpRecTyp IS

SELECT tabnum, sal FROM emp ORDER BY sal DESC;

PROCEDURE hire_emp (tnum NUMBER, ename CHAR, job CHAR, vsal NUMBER, deptno NUMBER) IS

v_emp EmpRecTyp;

BEGIN

INSERT INTO emp (tabnum, name, post, sal, depnum)

VALUES (tnum, ename, job, vsal, deptno);

count_emp := count_emp+1;

dbms_output.put_line(‘ tabnum ‘ || ‘ salary ‘);

open desc_salary;

fetch desc_salary into v_emp;

while desc_salary%found loop

dbms_output.put_line(TO_char(v_emp.emp_id)||’ ‘||TO_char(v_emp.salary));

fetch desc_salary into v_emp;

end loop;

close desc_salary;

END hire_emp;

PROCEDURE fire_emp (emp_id NUMBER) IS

BEGIN DELETE FROM emp WHERE tabnum = emp_id;

count_emp := count_emp-1;

END fire_emp;

BEGIN

select count(*) into count_emp from emp;

END emp_act;

2. Пакет emp_actions. Спецификация этого пакета объявляет следующие типы, объекты и подпрограммы:

  • типы EmpRecTyp и DeptRecTyp
  • функции hire_employee, nth_highest_salary и rank
  • процедуры fire_employee и raise_salary
  • курсор desc_salary
  • исключение salary_missing

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

PACKAGE emp_actions IS

/* Объявить внешне видимые типы, курсор, исключение. */

TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);

TYPE DeptRecTyp IS RECORD (dept_id INTEGER, location CHAR);

CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp;

salary_missing EXCEPTION;

/* Объявить внешне вызываемые подпрограммы. */

FUNCTION hire_employee (ename CHAR, job CHAR, mgr INTEGER,

sal NUMBER, comm NUMBER, deptno INTEGER)

RETURN INTEGER;

PROCEDURE fire_employee (emp_id INTEGER);

PROCEDURE raise_salary (emp_id INTEGER, increase NUMBER);

FUNCTION nth_highest_salary (n INTEGER) RERURN EmpRecTyp;

END emp_actions;

PACKAGE BODY

emp_actions IS number_hired INTEGER; — видна только в этом пакете

/* Определить курсор, объявленный в пакете. */

CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp IS

SELECT empno, sal FROM emp ORDER BY sal DESC;

/* Определить подпрограммы, объявленные в пакете. */

FUNCTION hire_employee (ename CHAR, job CHAR, mgr INTEGER,

sal NUMBER, comm NUMBER, deptno INTEGER)

RETURN INTEGER IS new_empno INTEGER;

BEGIN

SELECT empno_seq.NEXTVAL INTO new_empno FROM DUAL;

INSERT INTO emp VALUES

(new_empno, ename, job, mgr, SYSDATE, sal, comm, deptno);

number_hired := number_hired + 1;

RETURN(new_empno);

END hire_employee;

PROCEDURE fire_employee (emp_id INTEGER) IS

BEGIN

DELETE FROM emp WHERE empno = emp_id;

END fire_employee;

PROCEDURE raise_salary (emp_id INTEGER, increase NUMBER) IS

current_salary NUMBER;

BEGIN

SELECT sal INTO current_salary FROM emp

WHERE empno = emp_id;

IF current_salary IS NULL THEN RAISE salary_missing;

ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id;

END IF;

END raise_salary;

FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS

emp_rec EmpRecTyp;

BEGIN

OPEN desc_salary;

FOR i IN 1..n LOOP

FETCH desc_salary INTO emp_rec;

EXIT WHEN desc_salary%NOTFOUND;

END LOOP;

CLOSE desc_salary;

RETURN (emp_rec);

END nth_highest_salary;

/* Определить локальные функции, доступные лишь в пакете. */

FUNCTION rank (emp_id INTEGER, job_title CHAR)

RETURN INTEGER IS

/* Возвращает ранг (высший = 1) сотрудника при данной должности

* на основе рейтинга производительности. */

head_count INTEGER;

score NUMBER;

BEGIN

SELECT COUNT(*) INTO head_count FROM emp

WHERE job = job_title;

SELECT rating INTO score FROM reviews WHERE empno = emp_id;

score := score / 100; — максимальный рейтинг = 100

RETURN (head_count + 1) — ROUND(head_count * score);

END rank;

BEGIN — здесь начинается часть инициализации пакета

INSERT INTO emp_audit

VALUES (SYSDATE, USER, ‘EMP_ACTIONS’);

number_hired := 0;

END emp_actions;

4.6. Скрипт SQL

В блоке PL/SQL нельзя использовать предложений SQL*Plus, но можно в текстовом файле (скрипте) перемешивать блоки PL/SQL с предложениями SQL*Plus. В следующем примере блок PL/SQL находится среди предложений SQL*Plus, подготавливающих и выдающих отчет. Заметьте, что каждый блок PL/SQL должен заканчиваться наклонной чертой (/).

CLEAR BREAKS;

CLEAR COLUMNS;

COLUMN ENAME HEADING NAME;

TTITLE ‘CLERICAL STAFF’;

DECLARE avg_sal NUMBER(7,2);

BEGIN

SELECT AVG(sal) INTO avg_sal FROM emp;

IF avg_sal > 1500 THEN

UPDATE emp SET sal = sal * 1.05 WHERE job LIKE ‘CLERK%’;

END IF;

END;

/

SELECT ENAME, SAL FROM EMP WHERE JOB LIKE ‘CLERK%’;

5. Параметры Oracle

Рекомендуемые размеры области SGA.

Размер блока (Block Size)

Модель памяти

SMALL

MEDIUM

LARGE

2K

4500K

6800K

17000K

4K

5500K

8800K

21000K

Название параметра

Содержание

Примечание

 

Место размещения системных аудиторских файлов (по умолчанию – $ORACLE_HOME/rdbms/audit).

 

audit_trail

Включение режима аудита.

True / false

background_dump_dest

Место dump-файлов процессов (например, /home/oracle80/rdbms/log).

 

background_dump_dest = %RDBMS72%\trace

Имя каталога для трассовых и сигнальных файлов.

 

checkpoint_process

Установка процесса записи контрольной точки в оперативный журнал транзакций.

true / false

Compatible

Версия Oracle (например, 7.2.0.0.0).

 

control_files

Название управляющего файла (-ов).

 

core_dump_dest

Место dump-файлов экземпляра (например, /home_stat/statistic/dbs).

 

db_block_buffers

Количество буферов для ввода данных из файлов.

200

db_block_max_scan_cnt

Число буферов в SGA, которое просматривает пользовательский процесс при поиске свободного буфера.

 

db_block_size

Размер блока базы данных (обычно, 2048).

кратно 512

db_block_write_batch

Число блоков, записываемых в одной операции записи на диск.

 

db_file_multiblock_read_count

Количество блоков, считываемых за 1 раз

кратно 2n

db_files

Количество файлов для табличных пространств (20)

> 0

db_name

Имя базы данных (до 8 символов)

 

db_writers

Количество дополнительных процессов записи в БД.

≥ 0

disributed_transaction

Количество распределенных транзакций (1).

≥ 0

dml_locks

Количество блокировок(например, 100).

 

log_archive_dest

Место архивирования файлов оперативных журналов регистрации изменений в БД.

диск, лента,…

log_archive_dest

Место размещения и обозначение фай-лов архивируемых журналов (например, /home/oracle80/dbs/arch/arch.log).

 

log_archive_start

Включение режима автоматического архивирования.

true / false

log_buffer

Размер буфера журнала транзакций.

8192

log_files

Количество файлов журнала транзакций (не может превышать значения параметра maxlogfiles команды CREATE DATABASE)

2 – 255

log_checkpoint_interval

Интервал между запусками процесса контрольной точки (например, 10000).

ms

max_dump_file_size = 10240

Максимальный размер трассового файла (to 5 Meg each).

 

mts_max_dispatchers

Максимальное количество функционирующих в данный момент диспетчеров (2). Задается для каждого протокола отдельно, например: «tcp,1»

 

mts_max_servers

Максимальное количество функциони-рующих сервер-процессов (2).

 

mts_servers

Начальное количество функционирую-щих сервер-процессов (2).

 

open_cursors

Число личных областей SQL.

50

processes

Максимальное количество процессов.

 

remote_login_passwordfile

Удаленный файл учета пользователей

shared

sequence_cache_entries

Размер кеша для последовательности (например, 10).

 

sequence_cache_hash_buckets

Количество кешей для последовательности (например, 10).

 

shared_pool_size

Размер разделяемого пула (в байтах).

3.5М

small_table_threshold

Размер таблицы, которая будет считаться системой «очень маленькой».

 

snapshot_refresh_interval

Регулярность включения процессов автоматического обновления снапшотов

 

snapshot_refresh_processes

Количество процессов автоматического обновления снапшотов локальной базы.

1÷10

snapshot_refresh_processes

Количество процессов обновления снапшотов.

 

sort_area_size

Размер области сортировки.

 

sort_area_retained_size

Размер, до которого можно сокращать область сортировки.

0÷sort_area_size

sql_trace

Формирование пользовательских файлов трассировки SQL-предложений и блоков PL/SQL (если эти блоки целиком выполняются на сервере).

true / false

timed_statistics

Включение режима сбора временной статистики.

true / false

user_dump_dest

Место dump-файлов пользователя (по умолчанию $ORACLE_HOME/rdbms/log).

 
  1. – если disributed_transaction > 0, запускается процесс reco, ответственный за связи с удаленными БД. Его можно не запускать (disributed_transaction = 0), но тогда экземпляр не сможет использовать ни одной «связи между базами данных».
  2. – только для архитектуры MTS с разделяемыми серверами.

Ниже приведены значения параметров по умолчанию для различных моделей памяти (для Oracle7):

# db_file_multiblock_read_count = 8 # SMALL

# db_file_multiblock_read_count = 16 # MEDIUM

# db_file_multiblock_read_count = 32 # LARGE

# db_block_buffers = 200 # SMALL

# db_block_buffers = 550 # MEDIUM

# db_block_buffers = 3200 # LARGE

# shared_pool_size = 3500000 # SMALL

# shared_pool_size = 6000000 # MEDIUM

# shared_pool_size = 9000000 # LARGE

# processes = 50 # SMALL

# processes = 100 # MEDIUM

# processes = 200 # LARGE

# dml_locks = 100 # SMALL

# dml_locks = 200 # MEDIUM

# dml_locks = 500 # LARGE

# log_buffer = 8192 # SMALL

# log_buffer = 32768 # MEDIUM

# log_buffer = 163840 # LARGE

# sequence_cache_entries = 10 # SMALL

# sequence_cache_entries = 30 # MEDIUM

# sequence_cache_entries = 100 # LARGE

# sequence_cache_hash_buckets = 10 # SMALL

# sequence_cache_hash_buckets = 23 # MEDIUM

# sequence_cache_hash_buckets = 89 # LARGE

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

Имя параметра

Значение по умолчанию

Диапазон

background_dump_dest

%RDBMSnn%\TRACE\

Любая допустимая директория

commit_point_strength

1

0 – 255

control_files

%ORACLE_HOME%\database\

ctl1%ORACLE_SID%.ora

Любые допустимые путь и имя файла

db_block_size

2048

512 – 8192

db_files

32

254, не больше db_block_buffers

db_file_multiblock_read_count

4

1 – 32

db_file_simultaneous_writes

4

1 – 24

distributed_transactions

25 * transactions

0 – transactions

log_archive_buffer_size

127

1 – 127

log_archive_buffers

4

1 – 8

log_archive_dest

%RDBMSnn%\

Любая допустимая директория

log_archive_format

ARC%S.%T

Любой допустимый формат архива

log_checkpoint_interval

8000

2 – Unlimited

log_files

255

2 – 255

log_small_entry_max_size

800

0 – Unlimited

maxlogfiles

  

nls_language

AMERICAN

Любой допустимый язык

nls_sort

Зависит от языка

Зависит от языка

nls_territory

AMERICA

Любая допустимая территория

open_cursors

50

1 – Unlimited

os_authent_prefix

OPS$

Любая строка символов

processes

25

3 – Unlimited

remote_login_passwordfile

shared

shared,exclusive,none

shared_pool_size

3,500,000

300K – Unlimited

single_process

Режим процессов экземпляра – один процесс или много

true / false

sort_area_size

65536

0 – Unlimited

sort_read_fac

20

0 – Unlimited

sort_spacemap_size

512

0 – Unlimited

temporary_table_locks

SESSIONS

0 – Unlimited

transactions_per_rollback_segment

30

1 – 255

user_dump_dest

%RDBMSnn%\TRACE

Любая допустимая директория

Следующие параметры определяют характеристики Personal Oracle7 и других продуктов Oracle (значения приведены для операционной системы Windows 95):

NLS_LANG

Значение по умолчанию: автоматически устанавливается, когда вы выбираете язык во время инсталляции

Допустимые значения: поддерживаемые языки, территории и наборы символов

Возможность модификации? да (но только с инсталлятором)

Этот параметр включает три компоненты:

language – язык, на котором будут выдаваться сообщения на экран.

territory – формат, в котором представляется дата.

character set – выводимый набор символов.

Значение по умолчанию приводит к тому, что все сообщения выдаются на английском.

ORACLE_HOME

Значение по умолчанию: нет (устанавливается во время инсталляции)

Допустимые значения: любая допустимая директория или устройство

Возможность модификации? нет

Этот параметр специфицирует домашнюю директорию Oracle, в которой инсталлируются продукты Oracle.

ORACLE_SID

Значение по умолчанию: нет (устанавливается как orcl, если инсталлируется стартовая БД)

Допустимые значения: любая комбинация из 4-х букв

Возможность модификации? да

Этот параметр специфицирует имя экземпляра БД на хост-машине. Значение этого параметра является системным идентификатором БД (SID).

ORA_sid_PWFILE

Значение по умолчанию: ORA%ORACLE_SID%.ORA

Допустимые значения: любое допустимое имя файла

Возможность модификации? да

Этот параметр специфицирует имя файла паролей (password file).

ORA_SQLDBA_MODE

Значение по умолчанию: SCREEN (устанавливается как LINE во время инсталляции)

Допустимые значения: LINE, SCREEN

Возможность модификации? да

Этот параметр специфицирует режим, в котором SQL*DBA стартует из командной строки.

PLSQLnn

Значение по умолчанию: нет (устанавливается как %ORACLE_HOME%\PLSQLnn во время инсталляции)

Допустимые значения: любой допустимый путь

Возможность модификации? нет

Этот параметр специфицирует директорию, которая определена для файлов PL/SQL. Если не определить это значение вручную, то во время инсталляции этот параметр будет установлен в %ORACLE_HOME%\PLSQLnn, где nn – это версия PL/SQL, которая будет использоваться.

RDBMSnn

Значение по умолчанию: нет

Допустимые значения: любой допустимый путь

Возможность модификации? нет

Этот параметр специфицирует директорию, которая определена для файлов базы данных. Personal Oracle7 и утилиты Oracle будут также обращаться к этой директории для локализации сообщений и исходных файлов. Если не определить это значение вручную, то во время инсталляции этот параметр будет установлен в %ORACLE_HOME%\RDBMSnn, где nn – это версия Personal Oracle7, которая будет использоваться.

API

Значение по умолчанию: нет (устанавливается как %ORACLE_HOME%\dbs во время инсталляции)

Допустимые значения: любой допустимый путь

Возможность модификации? нет

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

COMPANY_NAME

Значение по умолчанию: нет (устанавливается во время инсталляции вручную)

Допустимые значения: любая строка

Возможность модификации? нет

DATABASE_STARTUP

Значение по умолчанию: OFF (устанавливается как AUTO во время инсталляции)

Допустимые значения: AUTO, OFF

Возможность модификации? да

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

DBA_AUTHORIZATION

Значение по умолчанию: нет (устанавливается как ORACLE во время инсталляции, если создается БД)

Допустимые значения: любое

Возможность модификации? да

Этот параметр специфицирует пароль для БД.

EXECUTE_SQL

Значение по умолчанию: нет (устанавливается как PLUS32 во время инсталляции)

Допустимые значения: некоторое выполнимое, которое может быть интерпретировано/ исполнено как SQL-скрипт.

Возможность модификации? да

Этот параметр используется для определения, какое программное обеспечение будет применяться для выполнения SQL-скриптов.

LISTENER_STARTUP

Значение по умолчанию: OFF (устанавливается как AUTO во время инсталляции)

Допустимые значения: AUTO, OFF

Возможность модификации? да

Этот параметр используется для индикации, будет ли стартовать SQL*Net Listener при старте БД.

MSHELP

Значение по умолчанию: нет (устанавливается как %ORACLE_HOME%\MSHELP во время инсталляции)

Допустимые значения: любой допустимый путь

Возможность модификации? нет

Этот параметр используется некоторыми утилитами для поиска файлов помощи

MSHELP_TOOLS

Значение по умолчанию: нет (устанавливается как %ORACLE_HOME%\MSHELP во время инсталляции)

Допустимые значения: любой допустимый путь

Возможность модификации? нет

Этот параметр используется некоторыми утилитами для поиска файлов помощи.

NLSRTLnn

Значение по умолчанию: нет (устанавливается как %ORACLE_HOME%\NLSRTL31 во время инсталляции)

Допустимые значения: любой допустимый путь

Возможность модификации? Нет

ORA_NLS

Значение по умолчанию: нет (устанавливается как %ORACLE_HOME%\NLSRTL31\DATA во время инсталляции)

Допустимые значения: любой допустимый путь

Возможность модификации? нет

Этот параметр используется для поиска файлов с наборами символов.

ORACLE_GROUP_NAME

Значение по умолчанию: устанавливается во время инсталляции

Допустимые значения: любая строка

Возможность модификации? нет

Этот параметр определяет группу, в которую входит БД.

PLUSnn

Значение по умолчанию: нет (устанавливается как %ORACLE_HOME%\PLUS32 во время инсталляции)

Допустимые значения: любой допустимый путь

Возможность модификации? нет

Этот параметр используется SQL*PLUS для поиска его файлов сообщений.

PROnn

Значение по умолчанию: нет (устанавливается как %ORACLE_HOME%\PRO17 во время инсталляции)

Допустимые значения: любой допустимый путь

Возможность модификации? нет

Этот параметр используется Pro*C и SQLLIB для поиска их файлов сообщений.

RDBMS_FILES

Значение по умолчанию: нет (устанавливается как %ORACLE_HOME%\DATABASE во время инсталляции)

Допустимые значения: любая директория

Возможность модификации? нет

Этот параметр используется утилитами БД для локализации файлов БД для архивации и восстановления, когда БД не запущена.

SQLPATH

Значение по умолчанию: любой допустимый путь

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

Возможность модификации? да

Этот параметр используется SQL*PLUS для локализации SQL-скриптов.

LOCAL

Значение по умолчанию: 2 (локальная БД)

Допустимые значения: любой допустимый SQL*Net алиас, строка подключения (connect string) или описание подключения

Возможность модификации? да

Этот параметр специфицирует строку подключения, которая будет использоваться, когда отсутствует другая спецификация. Этот параметр разрешает определять строку подключения «по умолчанию» для сетевого соединения. Когда пользователь пытается подключиться без указания строки подключения, Oracle7 пытается использовать строку, определенную как LOCAL (даже если это срока подключения к удаленной БД). Если невозможно найти LOCAL, то подключение происходит к локальной БД, имя которой определено параметром ORACLE_SID на локальной машине.

Замечание: вы можете изменить этот параметр из командной строки в любое время.

NET20

Значение по умолчанию: нет

Допустимые значения: %ORACLE_HOME%\NETWORK

Возможность модификации? нет

Этот параметр специфицирует для SQL*Net Version 2 директорию для файлов сообщений.

Замечание: отдельные утилиты Oracle могут дополнять конфигурацию параметров. Это нужно смотреть в документации по утилитам и параметрам.

— системная информация о версиях, настройках и глобальном имени БД

select * from sys.props$;

Name

Value$

Comment$

DICT.BASE

NLS_LANGUAGE

NLS_TERRITORY

NLS_CURRENCY

NLS_ISO_CURRENCY

NLS_NUMERIC_CHARACTERS

NLS_DATE_FORMAT

NLS_DATE_LANGUAGE

NLS_CHARACTERSET

NLS_SORT

NLS_CALENDAR

NLS_RDBMS_VERSION

GLOBAL_DB_NAME

EXPORT_VIEWS_VERSION

2

AMERICAN

AMERICA

$

AMERICA

.,

DD-MON-YY

AMERICAN

WE8ISO8859P1

BINARY

GREGORIAN

7.2.2.3.1

ORACLE.WORLD

2

dictionary base tables version #

Language

Territory

Local currency

ISO currency

Numeric characters

Date format

Date language

Character set

Linguistic definition

Calendar system

RDBMS version for NLS parameters

Global database name

Export views revision #

6. Скрипты Oracle

Таблица 16. Требуемые скрипты SQL

Имя скрипта

Требуемые опции

Описание

CATALOG.SQL

нет

Создает словарь данных и общие синонимы для многих его обзоров, и дает доступ к этим синонимам для PUBLIC; вызывает CATAUDIT, CATEXP и CATLDR

CATAUDIT.SQL

нет

Создает аудиторский журнал базы данных и его обзоры (автоматически вызывается из CATALOG; журнал можно удалить скриптом CATNOAUD)

CATEXP.SQL

нет

Создает таблицы словаря для утилит экспорта и импорта (вызывается из CATALOG)

CATLDR.SQL

нет

Создает обзоры для SQL*Loader; вызывается из CATALOG

CATPARR.SQL

Параллел.

сервер

Создает обзоры словаря данных для параллельного сервера

CATTRUST.SQL

Trusted

Определяет структуры для сервера ORACLE Trusted ORACLE

CATPROC.SQL

процедурная

Выполняет все скрипты, требуемые или используемые с процедурной опцией: CATPRC,CATSNAP,CATRPC,STANDARD, DBMSSTDX,PIPDL,PIDIAN,DIUTIL,PISTUB, DBMSUTIL,DBMSSNAP,DBMSLOCK,DBMSPIPE, DBMSALRT, DBMSOTPT, DBMSDESC

CATPRC.SQL

процедурная

Создает обзоры словаря данных для хранимых процедур, пакетов и триггеров базы данных (вызывается из CATALOG)

CATSNAP.SQL

процедурная распределе-нная

Создает структуры словаря данных для снимков (вызывается из CATALOG; требует CATPRC)

CATRPC.SQL

процедурная распределе-нная

Создает обзоры словаря данных для снимков (вызывается автоматически скриптом CATPROC; требует CATPRC)

STANDARD.SQL

процедурная

Создает пакеты PL/SQL для процедурной опции (вызывается из CATPTOC; требует CATPRC)

DBMSSTDX.SQL

процедурная

Включает расширения в пакет standard (вызывается из CATPROC; требует STANDARD)

PIPIDL.SQL

процедурная

Создает пакеты PL/SQL для процедурной опции (вызывается из CATPROC; требует DBMSSTDX)

PIDIAN.SQL

процедурная

Создает пакеты PL/SQL для процедурной опции (вызывается из CATPROC; требует DBMSSTDX)

DIUTIL.SQL

процедурная

Создает пакеты PL/SQL для процедурной опции (вызывается из CATPROC; требует PIDIAN)

PISTUB.SQL

процедурная

Создает пакеты PL/SQL для процедурной опции (вызывается из CATPROC; требует DIUTIL)

DBMSUTIL.SQL

процедурная

Создает утилиты, которые можно вызывать из процедур (вызывается из CATPROC;требует PISTUB)

DBMSSNAP.SQL

процедурная распределе-нная

Создает процедуры для администрирования снимков (вызывается из CATPROC; требует CATSNAP; должен выполняться как на узле снимка, так и на узле главной таблицы)

DBMSALRT.SQL

процедурная

Позволяет пользователям и приложениям использовать сигнализаторы событий (вызывается из CATPROC; требует PISTUB)

DBMSLOCK.SQL

процедурная

Позволяет пользователям и приложениям использовать пакет блокировок (вызывается из CATPROC; требует PISTUB)

DBMSMAIL.SQL

процедурная

Позволяет пользователям и приложениям посылать сообщения Oracle*Mail (вызывается из CATPROC; требует PISTUB; вы должны выполнить его на посылающей базе данных, и запустить UTLMAIL.SQL на принимающей базе данных)

DBMSOTPT.SQL

процедурная

Позволяет разработчикам приложений принимать ввод из процедур (вызывается из CATPROC; требует PISTUB)

DBMSPIPE.SQL

процедурная

Позволяет сессиям одной и той же инстанции общаться друг с другом (вызывается из CATPROC; требует PISTUB)

DBMSDESC.SQL

процедурная

Создает пакет, который позволяет описывать аргументы и возвращать значения из программных единиц (вызывается из CATPROC; требует PISTUB)

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

INIT_SQL_FILES = (CATALOG.SQL, CATPROC.SQL ACME_DBA.SQL)

В этом примере ACME_DBA.SQL — это дополнительный файл, который должен быть выполнен после создания базы данных.

6.1. Создание дополнительных структур словаря данных

ORACLE поставляет с сервером ряд других скриптов, создающих дополнительные структуры, которые вы можете использовать для управления вашей базой данных и создания приложений. Эти скрипты перечислены ниже; для дополнительной информации о них обратитесь к приложению B. Точные имена и местоположения этих скриптов зависят от операционной системы (обратитесь к вашему руководству о инсталляции).

Таблица 17. Дополнительные скрипты SQL

Имя скрипта

Требуемые опции

Кто выпол-няет

Описание опции

CATALOG6.SQL

нет

SYS

Создает обзоры словаря версии 6

CATBLOCK.SQL

нет

SYS

Создает обзор BLOCKING_LOCKS, который показывает, какие блокировки держат систему

CATDBSYN.SQL

нет

имеющий доступ к таблицам словаря

Создает личные синонимы для словарных обзоров DBA_

CATEXP6.SQL

нет

SYS

Создает обзоры для использования утилиты экспорта версии 6 в ORACLE7

CATNOAUD.SQL

нет

SYS

Удаляет аудиторский журнал, который был создан CATAUDIT, включая его данные и обзоры

CATNOPRC.SQL

нет

SYS

Удаляет структуры словаря данных, созданные CATPRC

CATSTAT.SQL

нет

SYS

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

UTLBSTAT.SQL

нет

любой

Начинает сбор статистики для настройки производительности (заканчивает скрипт UTLESTAT)

UTLCHAIN.SQL

нет

любой

Создает таблицы для сохранения вывода команды ANALYZE с опцией CHAINED_ROWS

UTLDTREE.SQL

процедурная

любой

Создает таблицы и обзоры по зависимостям между объектами

UTLESTAT.SQL

нет

любой

Заканчивает сбор статистики, начатый скриптом UTLBSTAT

UTLEXCPT.SQL

нет

любой

Создает умалчиваемую таблицу (EXCEPTIONS) для сохранения исключений от включенных ограничений

UTLEXP6.SQL

нет

SYS

Возвращает список объектов, которые не были экспортированы экспортом SQL*Net базы данных версии 6

UTLDIDXS.SQL

нет

любой

Выдает результаты выполнения скриптов UTLDIDXSS и UTLDIDXSO

UTLOIDXS.SQL

нет

любой

Выполняет UTLIDXSS на нескольких столбцах

UTLLOCKT.SQL

нет

SYS

Выдает граф ожиданий блокировок в формате структуры дерева

UTLMAIL.SQL

нет

SYS

Позволяет базе данных Oracle*Mail принимать сообщения от процедур (выполняется на базе данных Oracle*Mail; требует запуска DBMSMAIL.SQL на базе данных, которая будет посылать сообщения)

UTLMONTR.SQL

нет

SYS

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

UTLSAMPL.SQL

нет

любой

Создает тестовые таблицы (такие как EMP и DEPT) и пользователей

UTLSIDXS.SQL

нет

любой

Вычисляет селективность столбца и проверяет, подходит ли индекс, созданный для столбца

UTLXPLAN.SQL

нет

любой

Создает таблицу PLAN_TABLE, которая содержит вывод команды EXPLAIN PLAN

7. Примеры применения триггеров

Триггеры обычно используются для:

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

7.1. Аудит с помощью триггеров

Типичное применение триггеров – дополнять встроенные средства аудита ORACLE. Хотя можно писать триггеры, которые будут записывать информацию, аналогичную той, что регистрируется командой AUDIT, триггеры следует применять лишь в том случае, если вам требуется более детальная аудиторская информация. Например с помощью триггеров можно реализовать отслеживание на уровне значений столбцов в строках таблиц.

Иногда команда ORACLE AUDIT рассматривается как средство аудита ЗАЩИТЫ, тогда как триггеры могут обеспечить средства, например, ФИНАНСОВОГО аудита.

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

Применяя триггеры для аудита, обычно используют триггеры AFTER.

Триггеры могут позволить пользователю предоставлять «код причины», по которой выдается отслеживаемое предложение, что может быть полезным при аудите как на уровне строк, так и на уровне предложений.

Пример 1.

Следующий триггер отслеживает модификации таблицы EMP по строкам. Он требует, чтобы в глобальной переменной пакета перед обновлением был предоставлен «код причины».

CREATE TRIGGER audit_employee

AFTER INSERT OR DELETE OR UPDATE ON emp

FOR EACH ROW

BEGIN

/* AUDITPACKAGE – это пакет, в котором объявлена общая переменная REASON. Эта переменная должна быть установлена приложением с помощью команды, например, такой как EXECUTE AUDITPACKAGE.SET_REASON(reason_string).

Пакетированная переменная сохраняет свое значение на протяжении всей сессии, и каждая сессия имеет свою собственную копию всех пакетированных переменных. */

IF auditpackage.reason IS NULL THEN

raise_application_error(-20201,

‘Задайте причину через AUDITPACKAGE.SET_REASON(reason_string)’);

END IF;

/* Если выполнено приведенное выше условие, т.е. переменная REASON пуста, то выдаются указанное сообщение и код ошибки, выполнение триггера прекращается, и результаты предложения, возбудившего триггер, откатываются. В противном случае, триггер вставляет новую строку в предопределенную аудиторскую таблицу AUDIT_EMPLOYEE, записывая старое и новое значения в таблице EMP и код причины, определенный значением переменной REASON из пакета AUDITPACKAGE. «Старые» значения пусты, если триггер вызван предложением INSERT, и «новые» значения пусты, если триггер вызван предложением DELETE. */

INSERT INTO audit_employee VALUES

(:old.ssn, :old.name, :old.job_classification, :old.sal,

:new.ssn, :new.name, :new.job_classification, :new.sal,

auditpackage.reason, user, sysdate);

END;

Если требуется, можно сбрасывать переменную REASON в пустое значение, чтобы заставить пользователей устанавливать код причины перед каждым обновлением. Следующий простой триггер предложения с условием AFTER сбрасывает код причины после выполнения предложения триггера:

CREATE TRIGGER audit_employee_reset

AFTER INSERT OR DELETE OR UPDATE ON emp

BEGIN auditpackage.set_reason(NULL);

END;

Оба показанных выше триггера возбуждаются одним и тем же типом предложений SQL. Однако, первый триггер (триггер AFTER уровня строки) возбуждается один раз для каждой строки таблицы, затрагиваемой предложением триггера, тогда как второй триггер (триггер AFTER уровня предложения) возбуждается один раз после того, как закончено выполнение предложения триггера.

Пример 2.

Этот триггер отслеживает изменения, которые вносятся в таблицу EMP, и записывает эту информацию в таблицы AUDIT_TABLE и AUDIT_TABLE_VALUES.

CREATE OR REPLACE TRIGGER audit_emp

AFTER INSERT OR UPDATE OR DELETE ON emp

FOR EACH ROW

DECLARE

time_now DATE;

terminal CHAR(10);

BEGIN

time_now := SYSDATE; — текущее время

terminal := USERENV(‘TERMINAL’); — терминал пользователя

IF INSERTING THEN — записать первичный ключ

INSERT INTO audit_table — нового сотрудника

VALUES (audit_seq.NEXTVAL, user, time_now, terminal,

‘EMP’, ‘INSERT’, :new.empno);

ELSIF DELETING THEN — записать первичный ключ

INSERT INTO audit_table — удаляемого сотрудника

VALUES (audit_seq.NEXTVAL, user, time_now, terminal,

‘EMP’, ‘DELETE’, :old.empno);

ELSE — записать первичный ключ

INSERT INTO audit_table — обновляемой строки

VALUES (audit_seq.NEXTVAL, user, time_now, terminal,

‘EMP’, ‘UPDATE’, :old.empno);

— для столбцов SAL, DEPTNO записать старые и новые значения

IF UPDATING (‘SAL’) THEN

INSERT INTO audit_table_values

VALUES (audit_seq.CURRVAL, ‘SAL’, :old.sal, :new.sal);

ELSIF UPDATING (‘DEPTNO’) THEN

INSERT INTO audit_table_values

VALUES (audit_seq.CURRVAL, ‘DEPTNO’, :old.deptno,

:new.deptno);

END IF;

END IF;

END;

/

7.2. Ограничения целостности и триггеры

Триггер внешнего ключа для порожденной таблицы

Следующий триггер гарантирует, что предложение INSERT или UPDATE смогут создать новое значение внешнего ключа лишь тогда, когда существует соответствующее значение в родительском ключе. Ограничение MUTATING_TABLE, включенное в этот пример, позволяет использовать этот триггер в сочетании с триггерами UPDATE_SET_DEFAULT и UPDATE_CASCADE. Это исключение можно удалить, если данный триггер будет использоваться в одиночку.

CREATE TRIGGER emp_dept_check

BEFORE INSERT OR UPDATE OF deptno ON emp

FOR EACH ROW WHEN (new.deptno IS NOT NULL)

/* Перед появлением в таблице EMP нового значения DEPTNO, возбудить этот триггер, чтобы проверить, что это значение внешнего ключа (DEPTNO) существует в таблице DEPT. */

DECLARE

dummy INTEGER; /* используется для извлечения из курсора */

invalid_department EXCEPTION;

valid_department EXCEPTION;

mutating_table EXCEPTION;

PRAGMA EXCEPTION_INIT (mutating_table, -4091);

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

CURSOR dummy_cursor (dn NUMBER) IS

SELECT deptno FROM dept

WHERE deptno = dn

FOR UPDATE OF deptno;

BEGIN

OPEN dummy_cursor (:new.deptno);

FETCH dummy_cursor INTO dummy;

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

IF dummy_cursor%NOTFOUND THEN RAISE invalid_department;

ELSE RAISE valid_department;

END IF;

EXCEPTION

WHEN invalid_department THEN raise_application_error(-20000,

‘Invalid Department Number ‘ || TO_CHAR(:new.deptno));

WHEN valid_department THEN CLOSE dummy_cursor;

WHEN mutating_table THEN NULL;

END;

Триггер UPDATE и DELETE RESTRICT для родительской таблицы

Следующий триггер определяется по таблице DEPT, чтобы реализовать ссылочное действие UPDATE и DELETE RESTRICT по первичному ключу таблицы DEPT:

CREATE TRIGGER dept_restrict

AFTER DELETE OR UPDATE OF deptno ON dept

FOR EACH ROW

/* Перед удалением или изменением в таблице DEPT значения первичного ключа (DEPTNO) проверить, что в таблице EMP нет зависимых значений внешнего ключа; если они есть, то возвратить ошибку. */

DECLARE

dummy INTEGER; /* используется для извлечения из курсора */

employees_present EXCEPTION;

employees_not_present EXCEPTION;

/*Курсор, используемый для проверки зависимых внешних ключей*/

CURSOR dummy_cursor (dn NUMBER) IS

SELECT deptno FROM emp WHERE deptno = dn;

BEGIN

OPEN dummy_cursor (:old.deptno);

FETCH dummy_cursor INTO dummy;

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

IF dummy_cursor%FOUND THEN

RAISE employees_present; /* существуют зависимые строки */

ELSE

RAISE employees_not_present; /* нет зависимых строк */

END IF;

EXCEPTION

WHEN employees_present THEN

raise_application_error(-20001,

‘Employees Present in Department ‘||TO_CHAR(:new.deptno));

CLOSE dummy_cursor;

WHEN employees_not_present THEN

CLOSE dummy_cursor;

END;

Замечания

Этот триггер не будет работать с самоссылочными таблицами (т.е. таблицами, содержащими как первичный/уникальный ключ, так и внешний ключ).

Кроме того, этот триггер не позволяет триггерам зацикливаться (например, A возбуждает B, который возбуждает A).

Триггеры UPDATE и DELETE SET NULL для родительской таблицы

Следующий триггер, определяемый по таблице DEPT, реализует ссылочное действие UPDATE и DELETE SET NULL по первичному ключу таблицы DEPT:

CREATE TRIGGER dept_set_null

AFTER DELETE OR UPDATE OF deptno ON dept

FOR EACH ROW

/* Перед удалением или изменением в таблице DEPT значения первичного ключа (DEPTNO) сбросить в NULL все зависимые значения внешнего ключа в таблице EMP. */

BEGIN

IF UPDATING AND :OLD.deptno != :NEW.deptno THEN

UPDATE emp SET emp.deptno = NULL

WHERE emp.depnto = :old.deptno;

END IF;

END;

Триггер DELETE CASCADE для родительской таблицы

Следующий триггер, определяемый по таблице DEPT, реализует ссылочное действие DELETE CASCADE по первичному ключу таблицы DEPT:

CREATE TRIGGER dept_del_cascade

AFTER DELETE ON dept

FOR EACH ROW

/* Перед удалением строки из таблицы DEPT удалить из таблицы EMP все строки, имеющие такое же значение DEPTNO. */

BEGIN

DELETE FROM emp

WHERE emp.depnto = :old.deptno;

END;

Замечание: Обычно код для DELETE CASCADE объединяют вместе с кодом для UPDATE SET NULL или UPDATE SET DEFAULT, чтобы учесть как обновления, так и удаления в одном триггере.

Триггер UPDATE CASCADE для родительской таблицы

Следующий триггер гарантирует, что при изменении номера отдела в таблице DEPT это изменение будет распространено на все зависимые внешние ключи в таблице EMP:

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

create sequence update_sequence increment by 1 maxvalue 5000

cycle;

CREATE PACKAGE integritypackage AS

updateseq NUMBER;

END integritypackage;

CREATE OR REPLACE PACKAGE BODY integritypackage AS

END integritypackage;

ALTER TABLE emp ADD update_id NUMBER; /* добавить флажок */

CREATE TRIGGER dept_cascade1

BEFORE UPDATE OF deptno ON dept

DECLARE

dummy NUMBER;

/* Перед обновлением таблицы DEPT (это триггер предложения), сгенерировать новый номер последовательности и назначить его общей переменной UPDATESEQ из пакета INTEGRITYPACKAGE. */

BEGIN

SELECT update_sequence.NEXTVAL

INTO dummy

FROM dual;

integritypackage.updateseq := dummy;

END;

CREATE TRIGGER dept_cascade2

AFTER DELETE OR UPDATE OF deptno ON dept

FOR EACH ROW

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

BEGIN

IF UPDATING THEN UPDATE emp

SET deptno := :new.deptno,

update_id = integritypackage.updateseq — из 1-го триггера

WHERE emp.deptno = :old.deptno

AND update_id IS NULL;

/* этот флажок пуст только в случае, если он не был обновлен 3-м триггером для того же самого предложения триггера */

END IF;

IF DELETING THEN

/* Перед удалением строки из таблицы DEPT, удалить из таблицы EMP все строки, имеющие такое же значение DEPTNO, что и в удаляемой строке таблицы DEPT */

DELETE FROM emp

WHERE emp.deptno = :old.deptno;

END IF;

END;

CREATE TRIGGER dept_cascade3

AFTER UPDATE OF deptno ON dept

BEGIN

UPDATE emp

SET update_id = NULL

WHERE update_id = integritypackage.updateseq;

END;

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

7.3. Реализация комплексных ограничений контроля

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

CREATE TRIGGER salary_check

BEFORE INSERT OR UPDATE OF sal, job_classification ON emp

FOR EACH ROW

DECLARE

minsal NUMBER;

maxsal NUMBER;

salary_out_of_range EXCEPTION;

BEGIN

/* Извлечь в MINSAL и MAXSAL минимальное и максимальное жалованья из справочной таблицы SALGRADE по заданной новой должности сотрудника. */

SELECT minsal, maxsal INTO minsal, maxsal FROM salgrade

WHERE job_classification = :new.job_classification;

/* Если новое жалованье сотрудника выходит за допустимый диапазон, возбуждается исключение, возвращается сообщение об ошибке, и висящее предложение INSERT или UPDATE, которое возбудило триггер, откатывается. */

IF (:new.sal < minsal OR :new.sal > maxsal) THEN

RAISE salary_out_of_range;

END IF;

EXCEPTION

WHEN salary_out_of_range THEN

raise_application_error (-20300,

‘Salary ‘ || TO_CHAR(:new.sal) ||

‘ out of range for job classification ‘ ||

:new.job_classification ||

‘ for employee ‘ || :new.name);

WHEN NO_DATA_FOUND THEN

raise_application_error (-20322,

‘Invalid Job Classification ‘ || :new.job_classification);

END;

7.4. Триггеры и комплексные проверки полномочий

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

Лучше всего для комплексной проверки полномочий использовать триггер предложения BEFORE. Это дает следующие преимущества:

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

Пример.

Запретить обновление данных о зарплате в праздничные и выходные дни.

CREATE TRIGGER emp_permit_changes

BEFORE INSERT OR DELETE OR UPDATE ON emp

DECLARE

dummy INTEGER;

not_on_weekends EXCEPTION;

not_on_holidays EXCEPTION;

non_working_hours EXCEPTION;

BEGIN

/* проверить на выходные */

IF (TO_CHAR(sysdate, ‘DY’) = ‘SAT’ OR (TO_CHAR(sysdate, ‘DY’) = ‘SUN’

THEN RAISE not_on_weekends;

END IF;

/* проверить на праздники */

SELECT COUNT(*) INTO dummy FROM company_holidays

WHERE TRUNC(day) = TRUNC(sysdate);

/* TRUNC отсекает компоненту времени из даты */

IF dummy > 0 THEN RAISE not_on_holidays;

END IF;

/* Проверить на рабочие часы (8am .. 6pm) */

IF (TO_CHAR(sysdate, ‘HH24’) < 8 OR (TO_CHAR(sysdate, ‘HH24’) > 18

THEN RAISE non_working_hours;

END IF;

EXCEPTION

WHEN not_on_weekends THEN

raise_application_error (-20324,

‘May not change employee table during the weekend’);

WHEN not_on_holidays THEN

raise_application_error (-20325,

‘May not change employee table during a holiday’);

WHEN non_working_hours THEN

raise_application_error (-20326,

‘May not change employee table during non-working hours’);

END;

7.5. Триггеры и прозрачная регистрация событий

Триггеры весьма полезны для организации прозрачного выполнения обновлений базы данных, связанных с определенными событиями.

Например, триггер REORDER показывает пример триггера, который осуществляет повторный заказ товара, когда имеют место определенные условия (а именно, количество товара в наличии, PART_ON_HANDS, меньше, чем предписанное значение, REORDER_POINT).

CREATE TRIGGER reorder

AFTER UPDATE OF parts_on_hand ON inventory

FOR EACH ROW

WHEN (new.parts_on_hand < new.reorder_point)

DECLARE

x NUMBER;

BEGIN

SELECT COUNT(*) INTO x

FROM pending_orders

WHERE part_no = :new.part_no;

IF x = 0 THEN

INSERT INTO pending_orders

VALUES (:new.part_no, :new.reorder_quantity, sysdate);

END IF;

END;

7.6. Триггеры и вычисляемые значения столбцов

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

  • Зависимые значения должны быть вычислены перед тем, как произойдет вставка или обновление, так, чтобы предложение триггера могло использовать вычисленные значения.
  • Триггер должен возбуждаться для каждой строки, которую затрагивает возбуждающее триггер предложение INSERT или UPDATE.

Пример

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

CREATE TRIGGER emp_compute

BEFORE INSERT OR UPDATE OF ename ON emp

/* Перед появлением нового значения поля ENAME, вычислить значения полей UPPERNAME и SOUNDEXNAME. Следует запретить пользователям обновлять эти поля непосредственно. */

FOR EACH ROW

BEGIN

:new.uppername := UPPER(:new.ename);

:new.soundexname := SOUNDEX(:new.ename);

END;

— Вычисление возраста (относительно текущей даты в XXI веке):

select name, born,

trunc((sysdate-born-(sysdate-born)/(365*4))/365) age

from emp e;

select name, born,

to_number(to_char(round(sysdate, ‘YEAR’),’YYYY’))-

to_number(to_char(round(born, ‘YEAR’) ,’YYYY’)) age

from emp e;

8. Операции, влияющие на состояние объекта

Операция

Результирующее состояние объекта

Результирующее состояние зависимых объектов

CREATE таблица, последовательность, синоним

VALID, если нет ошибок

Без изменений*

ALTER таблица [ADD | MODIFY стлб ] RENAME таблица, обзор, синоним, последовательность

VALID, если нет ошибок

INVALID (недействительное)

DROP таблица, посл., синоним, обзор, процедура, функция, пакет

Никакого; объект удален

INVALID

CREATE обзор, процедура**

VALID, если нет ошибок; INVALID при ошибках синтаксиса / полномочий

без изменений*

CREATE OR REPLACE обзор, процедура**

VALID, если нет ошибок; INVALID при ошибках синтаксиса / полномочий

INVALID

REVOKE объектная привилегия*** ON объект TO / FROM пользователь

Без изменений

INVALID для всех зависимых объектов этого польз.***

REVOKE объектная привилегия*** ON объект TO / FROM PUBLIC

Без изменений

INVALID для всех зависимых объектов в базе данных***

REVOKE системная привилегия**** TO / FROM пользователь

Без изменений

INVALID для всех объектов пользователя****

REVOKE системная привилегия TO / FROM PUBLIC

Без изменений

INVALID для всех объектов в базе данных****

* – Может вызывать недействительность зависимых объектов, если объект не существовал ранее.

** – Независимые процедуры и функции, пакеты и триггеры.

*** – Только объектные привилегии DML, включая SELECT, INSERT, UPDATE, DELETE и EXECUTE; приведение в действительное состояние не требует перекомпиляции.

**** – Только системные привилегии DML, включая SELECT / INSERT / UPDATE / DELETE ANY TABLE и EXECUTE ANY PROCEDURE; приведение в действительное состояние не требует перекомпиляции.

9. Некоторые команды администрирования базы данных

Ряд команд утилиты SQL*Plus предназначен для АБД. По сути, SQL*Plus  – полнофункциональное средство администрирования. Доступные в SQL*Plus команды администрирования рассматриваются в данном разделе.

9.1. Запуск базы данных

Начиная с Oracle8, утилиту SQL*Plus можно использовать для запуска и остановки базы данных. Запуск базы данных состоит из трех шагов:

  1. Запуск экземпляра.
  2. Монтирование базы данных.
  3. Открытие базы данных.

Запуск базы данных в SQL*Plus выполняется командой STARTUP:

<команда STARTUP> ::=

STARTUP [FORCE] [RESTRICT] [PFILE=<имя файла параметров>] <действие запуска>

<действие запуска> ::= MOUNT [<имя базы данных>]

| OPEN [<опция открытия>][<имя базы данных>]

| NOMOUNT

<опция открытия> ::= READ ONLY

| READ WRITE [RECOVER]

| RECOVER

Все варианты команды STARTUP, так или иначе, запускают экземпляр (выделяется память и запускаются фоновые процессы). Опции команды STARTUP представлены в табл.18.

Таблица 18. Опции команды STARTUP.

Опция

Назначение

FORCE

Останавливает (с опцией ABORT) и потом перезапускает экземпляр. Это единственная опция команды STARTUP, которую можно применять к работающему экземпляру. Используется при отладке и в экстраординарных ситуациях.

RESTRICT

Позволяет подключаться к базе данных после запуска только пользователям, обладающим системной привилегией RESTRICTED SESSION. Это ограничение в дальнейшем можно снять с помощью команды ALTER SYSTEM.

PFILE

Задает нестандартный файл параметров инициализации. Если эта опция не указана, используется стандартный файл (в ОС UNIX это обычно $ORACLE_HOME/admin/dbs/init$ORACLE_SID.ora, а в Windows — %ORACLE_HOME%\database\init%ORACLE_SID%.ora).

MOUNT

Монтирует указанную (стандартную локальную — значение параметра инициализации DB_NAME) базу данных, но не открывает ее.

OPEN

Монтирует и открывает указанную базу данных.

NOMOUNT

Экземпляр запускается, но база данных не монтируется.

RECOVER

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

Эту команду может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA к выделенному серверному процессу. По умолчанию используется опция OPEN. Команда STARTUP OPEN RECOVER монтирует и открывает базу данных, даже если полное восстановление закончилось неудачно.

9.2. Остановка базы данных

Команда SHUTDOWN в SQL*Plus останавливает текущий экземпляр Oracle, к которому подключен пользователь, и может при этом закрыть и демонтировать базу данных. Эту команду можно применять только для серверов версии 8 и выше.

<команда SHUTDOWN> ::=

SHUTDOWN <режим остановки> <режим остановки> ::=

ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]

Назначение опций команды SHUTDOWN описано в табл.19.

Таблица 19. Опции команды SHUTDOWN.

Опция

Назначение

ABORT

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

IMMEDIATE

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

NORMAL

Ждет отключения всех пользователей от базы данных (новые подключения запрещены). База данных закрывается и демонтируется, затем останавливается экземпляр. При перезапуске восстановление не потребуется.

Эта опция используется по умолчанию.

TRANSACTIONAL [LOCAL]

Ждет завершения активных транзакций. При попытке начать новую транзакцию происходит отключение сеанса. После завершения всех активных транзакций все сеансы автоматически отключаются. Затем остановка идет как при вводе опции IMMEDIATE.

Режим LOCAL задает такой режим остановки только для локальных транзакций. Сервер не ждет завершения удаленных транзакций.

Эту команду может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA к выделенному серверному процессу. По умолчанию используется опция NORMAL.

9.3. Управление архивированием журналов повторного выполнения

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

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

Для управления режимом архивирования журналов повторного выполнения утилита SQL*Plus предлагает команду ARCHIVE LOG, которая имеет следующий синтаксис:

<команда ARCHIVE LOG > ::=

ARCHIVE LOG <команда или журнал> [TO <место назначения>]

<команда или журнал> ::=

LIST | STOP | START | NEXT | ALL | <номер журнала>

Эта команда позволяет начать или остановить автоматическое архивирование оперативных файлов журнала повторного выполнения, явно заархивировать указанные по номерам файлы или выдать информацию о них. Опции команды ARCHIVE LOG описаны в табл.20.

Таблица 20. Опции команды ARCHIVE LOG.

Опция

Назначение

LIST

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

STOP

Останавливает автоматическое архивирование. Если экземпляр по- прежнему работает в режиме ARCHIVELOG и все группы журналов повторного выполнения заполнены, работа базы данных приостанавливается, пока файл журнала повторного выполнения не будет заархивирован (например, командами ARCHIVE LOG NEXT или ARCHIVE LOG ALL).

START

Включает автоматическое архивирование. Запускает фоновый процесс ARCH, выполняющий автоматическое архивирование при необходимости. Если запускается процесс ARCH и в команде указано имя файла, этот файл становится новым стандартным местом назначения для архива. Процесс ARCH запускается автоматически при запуске экземпляра, если параметр инициализации LOG_ARCHIVE_START имеет значение TRUE.

NEXT

Явно архивирует следующую заполненную, но еще не заархивированную оперативную группу файлов журнала повторного выполнения.

ALL

Явно архивирует все заполненные, но еще не заархивированные оперативные группы файлов журнала повторного выполнения.

номер журнала

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

Если место назначения для архива не указано явно в командной строке, используется параметр инициализации LOG_ARCHIVE_DEST (стандартное место назначения). Если новое место назначения указано с опцией START, оно становится стандартным. В остальных случаях туда записываются только соответствующие архивы, инициированные данной командой.

Команду ARCHIVE LOG может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA. Она применяется только к текущему экземпляру. Для управления другими экземплярами и кластером в целом используется SQL-оператор ALTER SYSTEM.

Если все оперативные группы файлов журнала повторного выполнения заполнены и не доступны для повторного использования (т.е. сервер работает в режиме ARCHIVELOG, и они не скопированы) работа базы данных приостанавливается. Явное архивирование решает эту проблему.

Рассмотрим простой пример использования команды ARCHIVE LOG для просмотра информации о текущем состоянии архивирования журналов повторного выполнения:

SQL> archive log 745

ORA-16013: журнал 1 с номером последовательности 745 не требует

архивирования

9.4. Восстановление базы данных

Если база данных работала в режиме ARCHIVELOG, после сбоя носителя ее можно полностью или частично восстановить. Для этого в SQL*Plus предлагается команда RECOVER, имеющая следующий, весьма объемный, синтаксис:

<команда RECOVER> ::=

RECOVER <режим восстановления> [<степень параллелизма>]

<режим восстановления> ::=

<общее восстановление> | <управляемое восстановление> | END BACKUP

<общее восстановление> ::=

[AUTOMATIC] [FROM <местонахождение>] <команда восстановления>

<команда восстановления> ::=

<вид восстановления> [TEST ALLOW <целое число> CORRUPTION]
  | CONTINUE [DEFAULT]
  | CANCEL

<вид восстановления> ::= <полное восстановление>

  | <частичное восстановление>

  | LOGFILE <имя файла>

<полное восстановление> ::=

[STANDBY] DATABASE { <уровень восстановления >} <уровень восстановления> UNTIL <точка восстановления>
  | USING BACKUP CONTROLFILE

<точка восстановления> ::=

CANCEL | TIME <дата> | CHANGE <целое число>

<частичное восстановление> ::=

<табличные пространства или файлы данных>
  | STANDBY <табличные пространства или файлы данных>
    UNTIL [CONSISTENT] [WITH] CONTROLFILE

<табличные пространства или файлы данных> ::=

TABLESPACE <табличное пространство>{, <табличное пространство>}
  | DATAFILE <файл данных>{, <файл данных>}

<управляемое восстановление> ::=

MANAGED STANDBY DATABASE <опция управляемого восстановления>

<опция управляемого восстановления> ::=

NODELAY
  | [TIMEOUT] <целое число>
  | CANCEL [IMMEDIATE] [NOWAIT]
  | DISCONNECT [FROM SESSION] [FINISH [NOWAIT]]

<степень параллелизма> ::=

PARALLEL [<целое число>] | NOPARALLEL

Опции команды RECOVER кратко описаны в табл.21.

Таблица 21. Опции команды RECOVER.

Опция

Назначение

AUTOMATIC

Автоматически генерирует имя архивного файла журнала повторного выполнения, необходимого для продолжения операции восстановления. Для этого используются значения параметров конфигурации LOG_ARCHIVE_DEST и LOG_ARCHIVE_FORMAT (или соответствующие стандартные значения). Если файл с таким именем не найден, SQL*Plus запрашивает имя файла, выводя автоматически сгенерированное в качестве подсказки. Имя запрашивается также, если не указана ни опция AUTOMATIC, ни опция LOGFILE. Если заранее известно, что архивирование выполнялось в файл с нестандартным именем, имеет смысл сразу указать опцию LOGFILE.

FROM <местонахождение>

Задает местонахождение архивных файлов журнала повторного выполнения. По умолчанию используется значение параметра инициализации LOG_ARCHIVE_DEST. Можно также задать местонахождение архивных файлов с помощью команды SQL*Plus SET LOGSOURCE.

LOGFILE

Продолжает восстановление носителей, применяя указанный файл журнала повторного выполнения. При восстановлении в интерактивном режиме (AUTORECOVERY OFF), запрашивает новое имя файла, если указанный файл журнала не найден.

TEST ALLOW <целое число> CORRUPTION

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

CONTINUE

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

CONTINUE DEFAULT

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

CANCEL

Прерывает восстановление, ведущееся до CANCEL (см. опцию UNTIL CANCEL).

STANDBY DATABASE

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

DATABASE

Восстанавливает всю базу данных в целом.

UNTIL CANCEL

Задает неполное восстановление, до прерывания администратором. Необходимо указать или подтвердить автоматически сгенерированные имена файлов журналов повторного выполнения. Восстановление завершится, если указать CANCEL вместо очередного имени файла.

UNTIL TIME

Задает неполное восстановление до момента времени. Момент времени указывается в одиночных кавычках по формату ‘YYYY-MM-DD:HH24:MI:SS’.

UNTIL CHANGE

Задает неполное восстановление до указанного по номеру изменения (SCN), не включая его.

USING BACKUP CONTROLFILE

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

TABLESPACE

Восстанавливает указанные табличные пространства текущей базы данных (до 16).

DATAFILE

Восстанавливает любое количество указанных файлов данных.

STANDBY TABLESPACE

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

STANDBY DATAFILE

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

UNTIL CONSISTENT WITH CONTROLFILE

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

MANAGED STANDBY DATABASE

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

NODELAY

Немедленно применяет отложенный архивный журнал к резервной базе данных, независимо от установки параметра DELAY в параметре инициализации LOG_ARCHIVE_DEST_n в основной БД.

TIMEOUT

Задает период ожидания (в минутах) для операции устойчивого восстановления. Если за это время не станет доступным архивный журнал повторного выполнения, процесс восстановления завершается с ошибкой. Если эта конструкция не указана, резервная база данных остается в состоянии ожидания восстановления, пока не будет повторно выполнена команда RECOVER с конструкцией CANCEL или пока не произойдет остановка или сбой экземпляра.

CANCEL

При управляемом восстановлении конструкция CANCEL прерывает восстановление резервной базы данных после применения текущего архивного файла повторного выполнения. Приглашение SQL*Plus снова появится после остановки процесса восстановления.

CANCEL IMMEDIATE

Прерывает управляемое восстановление резервной базы данных после применения текущего архивного файла повторного выполнения или после прочтения следующего, в зависимости от того, какое событие произойдет раньше. Приглашение SQL*Plus снова появится после остановки процесса восстановления. Команду RECOVER CANCEL IMMEDIATE нельзя выполнять из того же сеанса, из которого была выполнена команда RECOVER MANAGED STANDBY DATABASE.

CANCEL NOWAIT

Прерывает управляемое восстановление резервной базы данных после прочтения следующего файла журнала повторного выполнения и выдает приглашение SQL*Plus.

DISCONNECT FROM SESSION

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

FINISH

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

NOWAIT

Возвращает управление немедленно, не дожидаясь завершения процесса восстановления.

Для выполнения команды RECOVER необходимо обладать ролью SYSDBA и подключиться через выделенный серверный процесс.

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

Рассмотрим простой пример остановки, запуска и восстановления носителя из SQL*Plus:

SQL> connect system/manager as sysdba

Соединено.

SQL> shutdown

База данных закрыта.

База данных размонтирована.

Экземпляр ORACLE завершен.

SQL> startup mount

Экземпляр ORACLE запущен.

Total System Global Area 152581388 bytes

Fixed Size 70924 bytes

Variable Size 78741504 bytes

Database Buffers 73691136 bytes

Redo Buffers 77824 bytes

База данных смонтирована.

SQL> recover database until time ‘2001-12-09:00:00:00’

Восстановление носителя завершено.

SQL> alter database open;

9.5. Резервное копирование

Ниже приведено содержимое командного файла, который создает sql-файл с командами создания горячей резервной копии текущей базы данных.

— параметр запуска — директория, куда будут копироваться файлы БД

set serveroutput on size 100000

set echo off feed off pages 0

spool d:\hot.backup

select ‘File created ‘||to_char(sysdate, ‘yyyy-mm-dd:hh24:mi:ss’) from dual;

prompt

begin

declare

target_dir varchar2(100):=&1;

source_file varchar2(100);

ts_name varchar2(100);

prev_ts_name varchar2(100);

cursor mycur is select file_name, lower(tablespace_name)

from sys.dba_data_files

where instr(file_name, ‘TEMP’)=0

order by 2;

begin

prev_ts_name:=’X’;

open mycur;

fetch mycur into source_file, ts_name;

while mycur%found loop

if ts_name<>prev_ts_name then

dbms_output.put_line(‘####################################’);

dbms_output.put_line(‘#tablespace ‘||ts_name);

dbms_output.put_line(‘sqlplus @‘||target_dir||’\start.sql ‘||ts_name);

end if;

dbms_output.put_line(‘copy ‘||source_file||’ ‘||target_dir);

prev_ts_name:=ts_name;

fetch mycur into source_file, ts_name;

if ts_name<>prev_ts_name then

dbms_output.put_line(‘sqlplus @‘||target_dir||’\end.sql ‘||prev_ts_name);

end if;

end loop;

dbms_output.put_line(‘sqlplus @‘||target_dir||’\end.sql ‘||prev_ts_name);

end;

end;

/

spool off

Поделитесь своим мнением или задайте вопрос