Базовые понятия

При работе с SQL*Plus используются следующие базовые понятия:

Команда	Команда SQL*Plus или оператор SQL Oracle.
Блок PL/SQL	Группа взаимосвязанных операторов PL/SQL, оформленная в виде анонимного блока.
Таблица	Базовая единица хранения данных в Oracle.
Запрос	SQL-оператор SELECT, выбирающий информацию из одной или нескольких таблиц.
Результаты запроса	Данные, возвращенные запросом.
Отчет	Результаты запроса, сформатированные с помощью команд SQL*Plus.

Буфер SQL

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

Для повторного выполнения содержимого буфера SQL используется команда RUN или косая черта (/). Команда RUN выдает содержимое буфера в стандартный выходной поток, а затем выполняет. Косая черта вызывает просто выполнение команды из буфера.

Дополнительные источники информации

SQL*Plus. User's Guide and Reference. Oracle9i. Database Concepts. SQL*Plus. Quick Reference. SQL*Plus Getting Started for Windows. Кевин Луни, Марлен Терьо. Oracle8i. Настольная книга администратора. - М.: Издательство "ЛОРИ", 2001. ISBN 5-85582-113-7

Сайт Ask Tom

Другие полезные команды

Есть еще несколько полезных команд, не относящихся ни к одной из рассмотренных выше категорий. Вот некоторые из них.

Формат строки связи для удаленной базы данных

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

из SQL*Plus с помощью команды CONNECT;

при запуске SQL*Plus с помощью опций командной строки.

В обоих случаях необходимо указать строку связи, задающую сетевую службу, к которой необходимо подключиться. Формат строки связи зависит от версии сетевых служб Oracle на машине, с которой выполняется подключение. При использовании Net8 обычно строка связи соответствует имени сетевой службы из файла tnsnames.ora, находящегося (в версиях 8.1.x и выше) в каталоге $ORACLE_HOME/network/admin/.

Пусть в файле tnsnames.ora имеется следующая запись для сетевой службы Oracle:

TRAINING = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = creator)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = training) ) )

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

sqlplus scott/tiger@training

Для подключения к соответствующей базе данных из SQL*Plus необходимо выполнить команду вида:

CONNECT scott/tiger@training

Форматирование результатов запросов

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

Использование команды CONNECT

Команда CONNECT имеет следующий синтаксис:

<команда CONNECT> ::=

CONN[ECT] [<пользователь и база данных>] [AS <роль пользователя>]

Информация о пользователе и базе данных, к которым необходимо подключиться, вводится в том же формате, что и при запуске (см. синтаксис вызова утилиты SQL*Plus ). При подключении, как и при запуске, можно указать роль пользователя, от имени которого оно выполняется.

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

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

Базы данных на других компьютерах или просто отличающиеся от стандартной называются удаленными. К удаленной базе данных можно подключиться, если для нее сконфигурированы сетевые службы Oracle Net и драйверы сетевых служб удаленной базы данных и локального клиента SQL*Plus совместимы.

Использование подставляемых переменных

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

SQL> select &func.(&col.) from &tab;

Введите значение для func: max Введите значение для col: sal Введите значение для tab: emp прежний 1: select &func.(&col.) from &tab новый 1: select max(sal) from emp

MAX(SAL) --------- 5000

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

Обратите внимание, что если необходимо вставить значение подставляемой переменной перед не пробельным символом, необходимо указать точку (.) после имени переменной.

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

SQL*Plus читает данные с клавиатуры, даже если входной и выходной потоки терминала перенаправлены в файлы. Если же сценарий запущен в пакетном режиме, данные читаются из соответствующего файла.

Если введенное значение совпадает с подставляемой переменной (начинается с &), то (по крайней мере, в версии 8.1.6) выдается сообщение об ошибке:

SQL> define emp = dept

SQL> select * from &tab;

Введите значение для tab: &emp

прежний 1: select * from &tab новый 1: select * from &emp SP2-0552: Переменная привязки "EMP" не описана.

Использование профилей сайта и пользователя

Утилита SQL*Plus поддерживает использование профиля сайта- командного файла, создаваемого администратором базы даных и автоматически выполняемого при регистрации любого пользователя. Этот файл обычно называется glogin.sql и находится в каталоге $ORACLE_HOME/sqlplus/admin.

Примечание

Здесь и далее $ORACLE_HOME означает начальный каталог, в который установлено используемое программное обеспечение Oracle. При указании имен файлов и каталогов используются соглашения ОС UNIX.

Утилита SQL*Plus поддерживает также использование профиля пользователя - командного файла, выполняемого автоматически при регистрации пользователя после

профиля сайта. Утилита SQL*Plus ищет файл профиля, login.sql, в текущем каталоге или, если не находит его там, в каталогах поиска файлов SQL-сценариев, задаваемых обычно переменной среды SQLPATH.

В случае неудачной регистрации (после трехкратного запроса имени пользователя и пароля), утилита SQL*Plus завершает работу с кодом возврата, аналогичным получаемому по команде EXIT FAILURE.

Коды возврата командных файлов

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

<команда WHENEVER SQLERROR> ::=

WHENEVER SQLERROR <реакция на ошибку>

<реакция на ошибку> ::=

<выход>

| COMMIT

| ROLLBACK

| CONTINUE <действие перед продолжением>

<выход> ::=

EXIT [<код возврата>][<завершение транзакции>]

<код возврата> ::=

<завершение транзакции> ::=

COMMIT | ROLLBACK

<действие перед продолжением> ::=

COMMIT | ROLLBACK | NONE

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

Команда ACCEPT

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

<команда ACCEPT> ::=

ACC[EPT] <имя переменной> [<тип переменной>]

[FOR[MAT] <формат>] [DEF[AULT] <стандартное значение>]

[<приглашение>] [HIDE]

<тип переменной> ::=

NUM[BER] | CHAR | DATE

<приглашение> ::=

PROMPT <текст> | NOPR[OMPT]

Если указанная в команде ACCEPT пользовательская переменная не существует, SQL*Plus создает ее. Опции команды ACCEPT описаны в .

Таблица 14. Опции команды ACCEPT.

Опция	Назначение
NUMBER	Задает переменной тип NUMBER. Если введенное значение не приводится к этому типу, команда ACCEPT выдает сообщение об ошибке и запрашивает значение снова.
CHAR	Задает переменной тип CHAR. Длина строки-значения не должна превышать 240 байтов.
DATE	Задает переменной тип DATE. Если введенное значение не преобразуется в тип DATE с учетом текущего формата даты в сеансе (NLS_DATE_FORMAT), команда ACCEPT выдает сообщение об ошибке и запрашивает значение снова.
FORMAT	Явно задает формат, которому должно соответствовать введенное значение. Формат задается так же, как для команды . Если значение не соответствует формату, команда ACCEPT выдает сообщение об ошибке и запрашивает значение снова.
DEFAULT	Задает стандартное значение, если оно не будет введено. Стандартное значение должно соответствовать стандартному или указанному формату.
PROMPT	Выдает на экран указанный текст перед ожиданием ввода значения переменной.
NOPROMPT	Выдает перевод строки и ждет ввода значения, не выдавая приглашений.
HIDE	Подавляет выдачу вводимого значения на экран по ходу ввода.

Рассмотрим простой пример совместного использования команд PROMPT и ACCEPT. Пусть имеется командный файл splus1.sql со следующим содержимым:

REM splus1.sql - пример диалога с пользователем prompt prompt Input department number (10, 20, 30) accept dept_number number prompt 'Dept. #: '

select * from dept where deptno = &dept_number;

Вот что происходит при его выполнении:

SQL> @f:\usr\doc\orasdev\splus1

Input department number (10, 20, 30) Dept. #: q SP2-0425: "q" не является допустимым числом Dept. #: 10 прежний 2: where deptno = &dept_number новый 2: where deptno = 10

DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK

Команда BREAK

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

<команда BREAK> ::=

BRE[AK] {ON <элемент отчета>{ <действие>}}

<элемент отчета> ::=

<столбец> | <выражение> | ROW | REPORT

<действие> ::=

SKI[P] <количество строк> [<дубликаты>]

| SKI[P] PAGE [<дубликаты>]

<дубликаты> ::=

NODUP[LICATES] | DUP[LICATES]

Команда BREAK без параметров выдает свои текущие параметры (параметры разрыва). Каждый последующий вызов BREAK с параметрами отменяет предыдущий. Для отмены параметров разрыва используется команда . Назначение опций команды BREAK описано в .

Таблица 7. Основные варианты вызова команды BREAK.

Конструкция	Описание
ON <столбец> { <действие>}	Задает действия, которые выполняются при изменении значения указанного столбца. Столбец задается по имени или псевдониму, без уточнения именем объекта. Если действие не указано, подавляется выдача повторяющихся значений и отмечается место, где выполняется вычисление, заданное в соответствующей команде .
ON <выражение> { <действие>}	Задает действия, которые выполняются при изменении значения выражения из списка выбора. Выражение надо задавать буквально, как в списке выбора. Если действие не указано, подавляется выдача повторяющихся значений и отмечается место, где выполняется вычисление, заданное в соответствующей команде .
ON ROW <действие>{ <действие>}	Задает действия, которые выполняются при выдаче каждой строки. Необходимо задать хотя бы одно действие.
ON REPORT { <действие>}	Отмечает место в отчете, где SQL*Plus выполнит вычисление, заданное в соответствующей команде . Позволяет выдавать суммарные значения.
SKIP <количество строк>	Пропускает указанное количество строк (вставляет столько пустых строк) перед строкой, в которой происходит разрыв.
SKIP PAGE	Пропускает столько строк, сколько задано в качестве размера страницы (задается с помощью SET PAGESIZE). После последней строки данных строки не пропускаются.
NODUPLICATES	Выдает пробелы вместо значения в столбце, если оно совпадает со значением в предыдущей строке.
DUPLICATES	Выдает значение столбца в каждой строке, независимо от дублирования.

<
Конструкцию ON <столбец> можно задавать в одной команде BREAK

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

Обычно команда используется с операторами SELECT, содержащими конструкцию ORDER BY. Рассмотрим пример (обратите внимание на пустые строки):

SQL> break on deptno skip on sal skip 1

SQL> break

break on deptno пропустить 1 nodup on sal пропустить 1 nodup

SQL> select deptno, ename, sal from emp order by deptno;

DEPTNO ENAME SAL ---------- ---------- ---------- 10 CLARK 2450

KING 5000

MILLER 1300

20 SMITH 800

ADAMS 1100

FORD 3000 SCOTT

JONES 2975

30 ALLEN 1600

BLAKE 2850

MARTIN 1250

JAMES 950

DEPTNO ENAME SAL ---------- ---------- ----------

30 TURNER 1500

WARD 1250

14 строк выбрано.

Команда CLEAR

Команда CLEAR позволяет сбросить значение ряда опций утилиты SQL*Plus, в частности, связанных с форматированием результатов. Она имеет следующий синтаксис:

<команда CLEAR> ::=

Назначение опций команды CLEAR представлено в .

Таблица 10. Опции команды CLEAR.

Опция	Назначение
BREAKS	Сбрасывает определения групп, установленные командой BREAK.
BUFFER	Очищает буфер SQL. Аналогично , если только не используется несколько буферов (см. ).
COLUMNS	Сбрасывает в стандартные значения атрибуты представления данных всех столбцов, установленные командами COLUMN.
COMPUTES	Удаляет все определения итоговых функций, установленные командой COMPUTE.
SCREEN	Очищает экран SQL*Plus.
SQL	Очищает буфер SQL. Аналогично , если только не используется несколько буферов (см. ).
TIMING	Удаляет все таймеры, созданные командой TIMING.

Команда COLUMN

Команда COLUMN для управления форматом выдачи столбца имеет следующий синтаксис:

<команда COLUMN> ::=

COL[UMN] [<ссылка на столбец> {<опция> }]

<ссылка на столбец> ::=

<имя столбца> | <псевдоним> | <выражение>

<опция> ::=

ALI[AS] <псевдоним>

| CLE[AR]

| ENTMAP [<вкл./откл.>]

| FOLD_A[FTER]

| FOLD_B[EFORE]

| FOR[MAT] <формат>

| HEA[DING] <текст>

| JUS[TIFY] <выравнивание>

| LIKE <выражение> | <псевдоним>

| NEWL[INE]

| NEW_V[ALUE] <переменная>

| NOPRI[NT]

| PRI[NT]

| NUL[L] <текст>

| OLD_V[ALUE] <переменная>

| <вкл./откл.>

| WRA[PPED]

| WOR[D_WRAPPED]

| TRU[NCATED]

<вкл./откл.> ::=

ON | OFF

<выравнивание> ::=

L[EFT] | C[ENTER] | C[ENTRE] | R[IGHT]

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

Таблица 5. Основные опции команды COLUMN.

Опция	Назначение
ALIAS	Присваивает столбцу указанный псевдоним. По этому псевдониму на столбец можно ссылаться в дальнейшем в командах , и .
CLEAR	Сбрасывает атрибуты указанного столбца в стандартные значения.
ENTMAP	Позволяет включать и отключать форматирование значений столбца для HTML-отчета. Если эта опция для столбца включена, в значениях будут заменяться символы, имеющие управляющее значение в HTML (<, >, & и т.д.).
FOLD_AFTER	Вставляет перевод строки после заголовка столбца и каждого значения в данном столбце. Перевод строки не вставляется, если указанный столбец - последний в списке выбора.
FOLD_BEFORE	Вставляет перевод строки перед заголовком столбца и каждым значением в данном столбце. Перевод строки не вставляется, если указанный столбец - первый в списке выбора.
FORMAT	Задает формат вывода значений столбца. Основные элементы формата представлены в ниже.
HEADING	Задает заголовок столбца. Если эта опция не используется, в качестве заголовка используются начальные символы (до ширины столбца) имени столбца или выбираемого выражения. Если в тексте есть пробелы или символы пунктуации, его необходимо брать в одинарные или двойные кавычки. Вместо каждого вхождения символа HEADSEP (по умолчанию - "\|") в тексте заголовка вставляется перевод строки.
JUSTIFY	Задает выравнивание столбца. По умолчанию столбцы типа NUMBER выравниваются вправо, все остальные - влево.
LIKE	Копирует особенности форматирования указанного столбца, не заданные для текущего столбца явно.
NEWLINE	Вставляет перевод строки перед выдачей значения столбца аналогично FOLD_BEFORE.
NEW_VALUE	Задает переменную, в которой будет храниться значение столбца. Эту переменную можно использовать в команде в качестве элемента верхнего колонтитула. Сам столбец необходимо при этом указать в команде с действием SKIP PAGE.
NOPRINT PRINT	Управляет выдачей столбца. Опция NOPRINT отключает выдачу столбца на экран и в отчет. Опция PRINT восстанавливает выдачу столбца.
NULL	Задает текст, выдаваемый утилитой SQL*Plus вместо пустых значений в столбце. По умолчанию используется пробел.
OLD_VALUE	Задает переменную, в которой будет храниться значение столбца. Эту переменную можно использовать в команде в качестве элемента нижнего колонтитула. Сам столбец необходимо при этом указать в команде с действием SKIP PAGE.
ON OFF	Управляет применением особенностей (атрибутов) форматирования столбца. Значение OFF отключает применение особенностей форматирования, не отменяя их. Значение ON снова включает применение заданных особенностей форматирования.
WRAPPED WORD_WRAPPED TRUNCATED	Задает правила работы со значениями, превышающими ширину столбца. Допускается перенос на следующую строку по границе столбца, по границе слова или усечение по границе столбца.

<
Таблица 6. Основные элементы формата опции FORMAT.

Элемент	Пример	Описание
A<ширина>	A20	Этот элемент формата позволяет изменить стандартную ширину столбца строкового типа и типа DATE. Если значение столбца не помещается в заданную <ширину>, оно усекается или переносится, в зависимости от соответствующих установок.
9	9999	Представляет значащую цифру в числовом значении. Вместо начальных нулей выдаются пробелы. Нулевое значение представляется цифрой 0.
0	0999	Выдает начальный нуль.
$	$999	Выдает перед числовым значением символ доллара.
L	9999L	Выдает символ локальной денежной единицы в данной позиции.
.	9999.99	Выдает разделитель целой и дробной части (десятичную запятую) в данной позиции
,	9,999	Выдает запятую (разделитель разрядов) в данной позиции.
DATE	DATE	Выдает числовое значение (представляющее дату в Юлианском формате) как дату в формате MM/DD/YY.
EEEE	9.999EEEE	Выдает значение в экспоненциальном формате (обязательно указывать ровно четыре E).

Рассмотрим простой пример использования команды COLUMN для форматирования значений и заголовков столбцов:

SQL> col sal format 9,999.99

SQL> select sal, ename from emp;

SAL ENAME --------- ---------- 800.00 SMITH 1,600.00 ALLEN 1,250.00 WARD 2,975.00 JONES 1,250.00 MARTIN 2,850.00 BLAKE 2,450.00 CLARK 3,000.00 SCOTT 5,000.00 KING 1,500.00 TURNER 1,100.00 ADAMS 950.00 JAMES 3,000.00 FORD 1,300.00 MILLER

14 строк выбрано.

SQL> col sal clear

SQL> col ename heading "Зарплата|товарища"

SQL> a where deptno=30

1* select sal, ename from empwhere deptno=30 SQL> /

select sal, ename from empwhere deptno=30 * ошибка в строке 1: ORA-00933: неверное завершение SQL-предложения

SQL> c/where/ where/

1* select sal, ename from emp where deptno=30 SQL> /

Зарплата SAL товарища ---------- ---------- 1600 ALLEN 1250 WARD 1250 MARTIN 2850 BLAKE 1500 TURNER 950 JAMES

6 строк выбрано.

Команда COLUMN с единственным параметром - ссылкой на столбец, выдает все атрибуты форматирования указанного столбца. Команда COLUMN без параметров выдает все атрибуты форматирования для всех столбцов, для которых они явно устанавливались. Продолжая предыдущий пример:

SQL> col ename

COLUMN ename ON HEADING 'Зарплата|товарища' headsep '|'

Команда COMPUTE

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

<команда COMPUTE> ::=

COMP[UTE]{ <функция> [LAB[EL] <текст>]}

OF <ссылка на столбец>{ <ссылка на столбец>}

ON <ссылка на место>{ <ссылка на место>}

<ссылка на место> ::=

<ссылка на столбец> | REPORT | ROW

Функции, которые можно использовать при вычислении в команде COMPUTE, представлены в .

Таблица 8. Функции в команде COMPUTE.

Функция	Назначение	Допустимые типы данных
AVG	Среднее среди непустых значений	Числовые
COU[NT]	Количество непустых значений	Все
MIN[IMUM]	Минимальное значение	Числовые и строковые
MAX[IMUM]	Максимальное значение	Числовые и строковые
NUM[BER]	Количество строк	Все
SUM	Сумма непустых значений	Числовые
STD	Среднеквадратичное отклонение непустых значений	Числовые
VAR[IANCE]	Дисперсия непустых значений	Числовые

Назначение основных конструкций команды COMPUTE описано в .

Таблица 9. Основные конструкции команды COMPUTE.

Конструкция	Описание
LABEL <текст>	Задает метку вычисляемого значения. Если эта конструкция не указана, выдается полное имя функции (см. ). Максимальная длина текста - 500 символов. Если текст содержит пробелы и символы пунктуации, его надо брать в одиночные кавычки. Метка выравнивается влево и усекается до меньшего из значений ширины столбца или длины строки. Метка для вычисляемого значения выдается в столбце, по которому выполняется BREAK. Чтобы метка не выдавалась, необходимо задать опцию NOPRINT в команде COLUMN для этого столбца. Если вычисления выполняются по ON ROW или ON REPORT, вычисляемое значение выдается в первом столбце и метка не выдается. Чтобы метка была выдана, включите в список выбора первым фиктивный столбец.
OF <ссылка на столбец>	Задает столбцы или выражения, функция от которого вычисляется. В конструкции OF можно сослаться на выражение в списке выбора, взяв его в двойные кавычки. Имя или псевдоним столбца в кавычки брать не надо.
ON <ссылка на место>	Задает событие, которое утилита SQL*Plus будет считать местом для вычисления. При ссылке на столбец его имя нельзя уточнять, - при необходимости надо использовать псевдонимы. При достижении места вычисления (т.е. когда изменяется значение столбца или выражения, извлекается новая строка или достигается конец отчета) команда COMPUTE выдает вычисленное значение и начинает вычисление сначала. Если для одного и того же столбца задано несколько команд COMPUTE, применяется последняя из них. В конструкции ON можно сослаться на выражение в списке выбора, взяв его в двойные кавычки. Имя или псевдоним столбца в кавычки брать не надо. Если в качестве события задано ON ROW или ON REPORT, необходимо, чтобы в последней команде BREAK также использовался критерий разрыва ROW или REPORT.

<
Для удаления всех определений COMPUTE используется команда CLEAR COMPUTES.

Рассмотрим простой пример вычисления итоговых значений и редактирования SQL-операторов в SQL*Plus:

SQL> set pagesize 55

SQL> break on deptno skip 1

SQL> compute avg label 'Средняя' of sal on deptno

SQL> select deptno, ename, sal

2 from emp

3 order by deptno;

DEPTNO ENAME SAL ---------- ---------- ---------- 10 CLARK 2450 KING 5000 MILLER 1300 ********** ---------- Средняя 2916,66667

20 SMITH 800 ADAMS 1100 FORD 3000 SCOTT 3000 JONES 2975 ********** ---------- Средняя 2175

30 ALLEN 1600 BLAKE 2850 MARTIN 1250 JAMES 950 TURNER 1500 WARD 1250 ********** ---------- Средняя 1566,66667

14 строк выбрано.

SQL> compute sum of sal on report

SQL> /

... тот же результат, поскольку не изменили условие BREAK

SQL> break on deptno skip 1 on report

SQL> list 2

2* from emp SQL> list *

2* from emp SQL> i where deptno in (10, 20)

SQL> /

DEPTNO ENAME SAL ---------- ---------- ---------- 10 CLARK 2450 KING 5000 MILLER 1300 ********** ---------- Средняя 2916,66667

20 SMITH 800 ADAMS 1100 FORD 3000 SCOTT 3000 JONES 2975 ********** ---------- Средняя 2175

---------- sum 19625

8 строк выбрано.

Команда DESCRIBE

Команда DESCRIBE позволяет получить описание таблицы или представления, а также спецификацию типа, пакета, функции или процедуры:

<команда DESCRIBE> ::=

DESC[RIBE] [[<схема>.]<объект>[@<строка связи>]]

Описание таблицы, представления или синонима содержит следующую информацию:

имя каждого столбца;

допускаются ли в столбце значение NULL;

типы данных столбцов;

длина или точность (и масштаб, для числовых) столбцов.

Например:

SQL> desc emp Имя Пусто? Тип ----------------------------------------- -------- -------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)

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

Длину выдаваемой строки описания можно контролировать с помощью команды .

Описание функций и процедур содержит следующую информацию:

тип программной единицы (процедура или функция);

имя функции или процедуры;

тип возвращаемого значения для функций;

имена, типы, режим передачи и стандартные значения аргументов.

Например:

PROCEDURE sys.subptxt Имя Аргумента Тип В/Из По-умолч ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN SUBNAME VARCHAR2 IN USR VARCHAR2 IN TXT VARCHAR2 IN/OUT

Команда EDIT

Команда EDIT позволяет вызвать текстовый редактор базовой операционной системы для редактирования указанного файла или содержимого SQL-буфера. Она имеет следующий синтаксис:

<команда EDIT> ::=

ED[IT] [<имя файла>[.<раширение>]]

Если расширение не указано, предполагается стандартное расширение (SQL или заданное командой ). Файл для редактирования ищется в текущем рабочем каталоге. Если в нем такой файл не найден, он создается. При вызове без параметров содержимое буфера помещается в файл afiedt.buf в текущем рабочем каталоге, а затем этот файл загружается в текстовый редактор. Это стандартное имя можно переопределить с помощью команды . Если команда вызвана без параметров, а SQL-буфер пустой, выдается следующее сообщение об ошибке:

SP2-0107: Нет ничего для сохранения.

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

Имя вызываемого текстового редактора содержится в пользовательской переменной SQL*Plus _EDITOR. Значение этой переменной можно задать с помощью команды DEFINE. Если ее значение не задано, используется стандартный редактор операционной системы (Notepad в Windows; задаваемый переменной среды EDITOR или ed в UNIX).

Команда EXECUTE

Эта команда выполняет один оператор PL/SQL. Чаще всего, это вызов хранимой процедуры или функции. Команда, по сути, неявно создает анонимный блок, в котором выполняется оператор. Рассмотрим пример:

18:02:02 SQL> set timing off

18:02:36 SQL> set serverout on

18:02:41 SQL> exec ListBlackFridays(sysdate, 3);

13.09.02 13.12.02 13.06.03

Процедура PL/SQL успешно завершена.

Команда HELP

Для получения справки по командам SQL*Plus используется команда HELP. Она может вызываться с одним необязательным аргументом. Если аргумент не указан, выдается справка по справочной системе SQL*Plus. Если аргумент указан, то выдается справка по всем командам SQL*Plus, начинающимся с указанного аргумента. Например, команда HELP EX выдает сначала справку по команде EXECUTE, а затем - по команде EXIT.

Команда HELP INDEX выдает список всех команд SQL*Plus. Команда HELP TOPICS

выдает список команд вместе с однострочным описанием назначения.

Команда PASSWORD

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

<команда PASSWORD> ::=

PASSW[ORD] [<имя пользователя>]

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

18:02:49 SQL> set time off

SQL> connect system/manager

Соединено. SQL> password scott

Изменение пароля для scott Новый пароль: ***** Повторите новый пароль: ***** SQL> connect scott/tiger

Соединено.

Команда PAUSE

Команда PAUSE позволяет дождаться подтверждения того, что пользователь прочитал сообщение на экране, выданное командой PROMPT. Для подтверждения необходимо нажать клавишу Enter, после чего выполнение сценария или сеанса SQL*Plus продолжится.

Команда PAUSE имеет следующий синтаксис:

<команда PAUSE> ::=

PAU[SE] [<текст>]

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

Рассмотрим простой пример. Изменим файл splus1.sql следующим образом:

set verify off

prompt Input department number (10, 20, 30) accept dept_number number prompt 'Dept. #: ' pause Press Enter to view results

select * from dept where deptno = &dept_number;

Вот что будет выдано при его выполнении:

SQL> @f:\splus1

Input department number (10, 20, 30) Dept. #: 30 Press Enter to view results

DEPTNO DNAME LOC ---------- -------------- ------------- 30 SALES CHICAGO

Для получения результатов пришлось нажать клавишу Enter после вывода соответствующего приглашения.

Команда PRINT

Команда PRINT имеет следующий синтаксис:

<команда PRINT> ::=

PRI[NT] { <имя переменной>}

Эта команда выдает текущее значение перечисленных связываемых переменных. При вызове без параметров выдаются значения всех связываемых переменных.

Продолжая предыдущий пример:

SQL> print a

SP2-0625: Ошибка печати переменной "a" SQL> print

DEP ---------- 10

DEP2 ----------

SP2-0625: Ошибка печати переменной "a" SQL> set autoprint off

SQL> exec open :a for select ename,sal,comm from emp where deptno=:dep;

Процедура PL/SQL успешно завершена.

SQL> print dep a

DEP ---------- 10

ENAME SAL COMM ---------- ---------- ---------- CLARK 2450 KING 5000 MILLER 1300

Команда PROMPT

Для выдачи на экран произвольного текста используется команда PROMPT со следующим синтаксисом:

<команда PROMPT> ::=

PRO[MPT] [<текст>]

Она выдает указанный текст или пустую строку (при вызове без параметров). Если необходимо выдать несколько строк, для каждой строки выполняется отдельная команда PROMPT.

Команда SAVE

Команда SAVE позволяет сохранить в файле содержимое SQL-буфера и имеет следующий синтаксис:

<команда SAVE> ::=

SAV[E] <имя файла>[.<расширение>] [<режим записи>]

<режим записи> ::=

CRE[ATE] | REP[LACE] | APP[END]

Команда SAVE по умолчанию (или в режиме записи CREATE) создает файл с указанным именем и записывает в него содержимое буфера. В режиме REPLACE содержимое существующего файла заменяется содержимым буфера или файл создается. В режиме APPEND содержимое буфера дописывается в конец указанного файла.

Если расширение не указано, предполагается стандартное расширение (SQL или заданное командой ). Если имя файла совпадает с режимом записи, расширение указывать обязательно.

Команда SAVE добавляет в командный файл строку, содержащую символ косой черты (/).

Команда SET

Команда SET позволяет установить системную переменную, изменяющую свойства среды SQL*Plus для текущего сеанса. Она имеет следующий синтаксис:

<команда SET> ::=

SET <системная переменная> <значение>

Имена, описания и возможные значения основных системных переменных для команды SET представлены в табл. 15.

Таблица 15. Основные системные переменные SQL*Plus

Переменная	Значения	Назначение
APPI[NFO]	ON \| OFF \| <текст>	Устанавливает автоматическую регистрацию командных файлов с помощью пакета DBMS_APPLICATION_INFO. Это позволяет контролировать производительность и использование ресурсов каждым командным файлом. При отключенной регистрации файлов в качестве имени модуля используется строка "SQL*Plus" или заданный в этой переменной текст. По умолчанию регистрация отключена.
ARRAY[SIZE]	<размер пакета>	Устанавливает размер пакета строк, извлекаемых утилитой SQL*Plus из базы данных за один прием. Диапазон допустимых значений - от 1 до 5000. По умолчанию извлекается по 15 строк.
AUTO[COMMIT]	ON \| OFF \| IMM[EDIATE] \| <к-во операторов>	Управляет фиксацией изменений в базе данных. По умолчанию (OFF) изменения необходимо фиксировать явно. Изменения могут фиксироваться немедленно после успешного выполнения оператора или блока (ON, IMM), или после успешного выполнения указанного количества операторов или блоков PL/SQL. Значение должно быть в диапазоне от 0 до 2000000000.
AUTOP[RINT]	ON \| OFF	Устанавливает автоматическую выдачу значений использованных в команде связываемых переменных.
AUTORECOVERY	ON \| OFF	При установке значения ON команда будет автоматически использовать стандартные имена файлов архивных журналов повторного выполнения. По умолчанию, имена файлов при восстановлении придется вводить вручную, в ответ на запросы SQL*Plus.
AUTOT[RACE]	ON \| OFF \| TRACE[ONLY] [EXP[LAIN]] [STAT[ISTICS]]	Управляет выдачей отчета о выполнении успешного оператора SELECT, INSERT, UPDATE или DELETE. В отчет может включаться план выполнения и статистическая информация о выполненных действиях.
BLO[CKTERMINATOR]	. \| <символ>	Задает не алфавитно-цифровой символ, используемый для завершения блока PL/SQL. По умолчанию используется точка.
CMDS[EP]	; \| ON \| OFF \| <символ>	Задает не алфавитно-цифровой символ, используемый для разделения нескольких команд SQL*Plus в одной строке. ON/OFF управляет возможностью обработки нескольких команд в строке. Значение ON означает, кроме того, установку стандартного разделителя- точки с запятой.
COLSEP	<текст>	Задает текст, который будет выдаваться между выбранными столбцами. Если текст содержит пробелы или символы пунктуации, его необходимо брать в апострофы. По умолчанию используется один пробел.
COM[PATIBILITY]	V7 \| V8 \| NATIVE	Задает версию Oracle, к которой подключена утилита SQL*Plus. Значение NATIVE, принятое по умолчанию, указывает, что версия определяется базой данных.
CON[CAT]	. \| ON \| OFF \| <символ>	Задает символ, который позволяет отделить имя подставляемой переменной от остальной части строки. Стандартный символ - точка.
COPYC[OMMIT]	<к-во пакетов>	Задает количество пакетов (размер задается системной переменной ARRAYSIZE), после копирования которых команда фиксирует изменения в базе данных. Значение должно быть в диапазоне от 0 до 5000. При использовании стандартного значения, 0, фиксация выполняется только по завершении копирования.
DEF[INE]	& \| ON \| OFF \| <символ>	Задает символ-префикс подставляемой переменной. При указании значения ON используется стандартный префикс - &.
DESC[RIBE]	[DEPTH 1 \| ALL \| <глубина>] [LINENUM ON \| OFF] [INDENT ON \| OFF]	Задает количество уровней рекурсивного описания объекта. Допустимы значения от 1 до 50. ALL означает максимальное количество уровней, 50. Можно также указывать номер строки и отступы для имени столбца или атрибута, когда объект содержит несколько компонентов объектных типов.
ECHO	ON \| OFF	Управляет выдачей на экран каждой команды в командном файле, выполняемом с помощью .
EDITF[ILE]	<имя файла>[.<расширение>]	Устанавливает стандартное имя файла для команды .
EMB[EDDED]	ON \| OFF	Задает выдачу каждого нового отчета с новой страницы (стандартное значение, OFF) или как продолжение на текущей странице (ON).
ESC[APE]	\ \| ON \| OFF \| <символ>	Задает маскирующий символ. Значение ON задает стандартный маскирующий символ, обратную косую.
FEED[BACK]	ON \| OFF \| <к-во строк>	Управляет выдачей количества строк, возвращенных запросом. Информация выдается, если возвращено не менее указанного количества строк. Значение ON эквивалентно 1.
FLU[SH]	ON \| OFF	Управляет буферизацией результатов. При стандартном значении ON результаты выдаются клиенту немедленно.
HEA[DING]	ON \| OFF	Управляет выдачей заголовков столбцов в результатах.
HEADS[EP]	\| \| ON \| OFF \| <символ>	Задает символ перевода строки для команд, задающих колонтитулы. Можно задавать любой символ, кроме алфавитно-цифровых и пробела. По умолчанию используется вертикальная черта.
LIN[ESIZE]	<длина строки>	Задает количество символов в выдаваемой строке. Если выдаваемая строка длиннее, остаток переносится на новую строку. Это значение также используется для выравнивания колонтитулов и заголовков отчетов.
LOGSOURCE	[<путь>]	Задает местонахождение архивных файлов журнала повторного выполнения для восстановления. Если значение не указано, будет использоваться путь, указанный в файле параметров инициализации.
LONG	<ширина столбца>	Задает максимальную ширину (в байтах) для выдачи значений типа LONG, CLOB и NCLOB (а также для копирования значений типа LONG). Значение не может превосходить 2 Гбайта.
NEWP[AGE]	NONE \| <к-во строк>	Задает количество пустых строк, выдаваемых в начале страницы отчета, перед верхним колонтитулом. Стандартное значение - 1 строка. Если указано значение NONE, пустые строки не выдаются. Если же указано значение 0, при печати пропускается страница, а при выдаче на экран он очищается.
NULL	<текст>	Задает текст, представляющий пустое значение в результатах выполнения оператора SELECT.
NUMF[ORMAT]	<формат>	Задает стандартный формат выдачи чисел (о форматах см. в описании команды )
NUM[WIDTH]	<ширина>	Задает стандартную ширину числовых столбцов.
PAGES[IZE]	<к-во строк>	Задает количество строк на странице. Если установить значение 0, подавляется выдача заголовков, колонтитулов и начальных пустых строк.
PAU[SE]	ON \| OFF \| <текст>	Позволяет управлять прокруткой при показе отчетов. При установке значения ON, утилита SQL*Plus приостанавливает работу перед выводом каждой страницы отчета, ожидая нажатия клавиши Enter. Можно также задать текст, который будет при этом выводиться.
RECSEP	WR[APPED] \| EA[CH] \| OFF	Устанавливает выдачу разделителей между записями (после перенесенных на несколько строк, после всех или никогда).
RECSEPCHAR	<символ>	Задает символ, из которого состоит разделитель записей. По умолчанию используется пробел. Разделитель записей представляет собой строку символов RECSEPCHAR, повторенных LINESIZE раз.
SERVEROUT[PUT]	ON \| OFF [SIZE <к-во строк>] [FOR[MAT] WRA[PPED] \| WOR[D_WRAPPED] \| TRU[NCATED]]	Управляет показом результатов хранимых процедур или анонимных блоков PL/SQL (выданных с помощью пакета DBMS_OUTPUT), размером буфера и переносом строк. Размер буфера должен быть в пределах от 2000 (стандартное значение) до 1000000.
SHOW[MODE]	ON \| OFF	Управляет выдачей значений системных переменных при изменении. Если задано значение ON, при изменении выдается старое и новое значение.
SQLBL[ANKLINES]	ON \| OFF	Разрешает или запрещает использование пустых строк в сценариях и командах SQL.
SQLC[ASE]	MIX[ED] \| LO[WER] \| UP[PER]	Управляет изменением регистра символов в команде перед выполнением. По умолчанию (MIXED) регистр символов остается неизменным. Иначе все символы, включая литералы в кавычках, переводятся в соответствующий регистр. Содержимое буфера SQL при этом не меняется.
SQLCO[NTINUE]	<текст>	Задает текст, выдаваемый утилитой SQL*Plus в качестве приглашения для продолжения ввода команды. По умолчанию используется символ >.
SQLN[UMBER]	ON \| OFF	Управляет выдачей приглашения для ввода второй и последующих строк команды или блока PL/SQL. Если указано значение ON, в качестве приглашения выдается номер строки, если OFF - значение системной переменной SQLPROMPT.
SQLPRE[FIX]	<символ>	Задает префиксный символ SQL*Plus (по умолчанию - #). Если по ходу ввода команды или блока PL/SQL ввести в отдельной строке команду, начинающуюся префиксным символом, эта команда немедленно выполнится. Префиксный символ должен быть не алфавитно-цифровым.
SQLP[ROMPT]	<текст>	Задает приглашение командной строки SQL*Plus (по умолчанию - "SQL> ").
SQLT[ERMINATOR]	<символ> \| ON \| OFF	Задает символ, завершающий и посылающий на выполнение команду SQL. По умолчанию используется точка с запятой. Если задать значение OFF, то для завершения придется вводить пустую строку или BLOCKTERMINATOR, если установлена системная переменная SQLBLANKLINES.
SUF[FIX]	<текст>	Задает стандартное расширение командного файла. По умолчанию используется sql.
TAB	ON \| OFF	Управляет использованием символа табуляции для форматирования результатов запросов в SQL*Plus.
TERM[OUT]	ON \| OFF	Управляет выдачей результатов команд на экран. Если задать значение OFF, результат выполнения команд (кроме интерактивных) не будет выдаваться на экран, но будет записываться в файл, указанный в команде SPOOL.
TI[ME]	ON \| OFF	Управляет выдачей текущего времени. При установке значения ON в приглашении выдается текущее время.
TIMI[NG]	ON \| OFF	Управляет выдачей статистики о времени. При установке значения ON после выполнения команды или командного файла выдается время выполнения с точностью до сотых долей секунды.
TRIM[OUT]	ON \| OFF	Управляет усечением хвостовых пробелов в выдаваемых строках. При установке значения ON (принято по умолчанию), хвостовые пробелы усекаются. Эта переменная учитывается только при выводе на экран.
TRIMS[POOL]	ON \| OFF	Управляет усечением хвостовых пробелов в строках, сбрасываемых в файл или на принтер, аналогично TRIMOUT.
UND[ERLINE]	ON \| OFF \| <символ>	Задает символ, используемый для подчеркивания заголовков столбцов. По умолчанию используется символ дефиса. Значение OFF отключает подчеркивание заголовков.
VER[IFY]	ON \| OFF	Управляет выдачей текста SQL-операторов и PL/SQL-блоков до и после замены подставляемых переменных значениями.
WRA[P]	ON \| OFF	Управляет усечением слишком длинных строк результатов. По умолчанию, остатки строк переносятся на следующую строку. При задании значения OFF - усекаются.

Рассмотрим пример задания некоторых системных переменных SQL*Plus:

SQL> set time on

16:43:39 SQL> set timing on

16:43:43 SQL> set underline off

16:43:55 SQL> set pagesize 1000

16:44:02 SQL> set linesize 128

16:44:12 SQL> select * from dept

16:44:18 2 ;

DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 TRAINING KIEV

Затрач.время: 00:00:00.80 16:44:20 SQL>

Команда SHOW

Команда SHOW позволяет получить значение системных переменных SQL*Plus, а также ряд информации о среде, базе данных, к которой подключена утилита, и об ошибках в последней выполненной команде. Эта команда имеет следующий синтаксис:

<команда SHOW> ::=

SHO[W] <опция>

<опция> ::=

<имя системной переменной>

| ALL

| BTI[TLE]
| ERR[ORS] [[<объект>] [<схема>.]<имя объекта>]

| LNO

| PARAMETERS [<часть параметра>]

| PNO

| REL[EASE]

| REPF[OOTER]

| REPH[EADER]

| SGA

| SPOO[L]

| SQLCODE

| TTI[TLE]

| USER

<объект> ::=

FUNCTION | PROCEDURE

  | PACKAGE
  | PACKAGE BODY
  | TRIGGER

| VIEW

| TYPE

| TYPE BODY

| DIMENSION

| JAVA CLASS

Назначение опций представлено в .

Таблица 16. Опции команды SHOW.

Опция	Назначение
ALL	Выдает значения всех опций SHOW, кроме ERRORS и SGA, и всех системных переменных.
BTI[TLE]	Выдает текущее определение BTITLE (нижнего колонтитула).
ERR[ORS]	Выдает ошибки компиляции хранимой программной единицы. При компиляции выдается сообщение, что ошибки есть. Для получения детальной информации об ошибках необходимо выполнить команду SHOW ERRORS. При выполнении этой команды без аргументов, выдается информация об ошибках при компиляции последней созданной или измененной программной единицы. Если же указать тип и имя программной единицы, будут выданы ошибки, полученные при ее компиляции. Выдается номер строки/столбца (в столбце LINE/COL), в которых обнаружена ошибка, и текст сообщения об ошибке (в столбце ERROR). Форматом выдачи можно управлять с помощью команды .
LNO	Показывает номер текущей строки на странице.
PARAMETERS	Выдает имя, тип и текущее значение одного или нескольких параметров инициализации. При вызове без дополнительных параметров, эта команда выдает информацию обо всех параметрах инициализации. Если указать части имен параметров, будет выдана информация только о параметрах, в имена которых указанные части присутствуют. Для использования этой опции необходима объектная привилегия SELECT на представление V_$PARAMETER.
PNO	Выдает номер текущей страницы результатов.
REL[EASE]	Выдает номер версии сервера Oracle, к которой подключена утилита SQL*Plus.
REPF[OOTER]	Выдает текущее определение REPFOOTER.
REPH[EADER]	Выдает текущее определение REPHEADER.
SGA	Выдает информацию об области SGA экземпляра, к которому подключена утилита SQL*Plus. Для использования опции SGA необходима объектная привилегия SELECT на представление V_$SGA.
SPOO[L]	Выдает информацию о том, направляются ли результаты в файл или на принтер.
SQLCODE	Выдает код завершения последнего оператора SQL.
TTI[TLE]	Выдает текущее определение TTITLE (верхнего колонтитула).
USER	Выдает имя текущего пользователя.

<
Рассмотрим пример использования ряда опций команды SHOW:

17:21:24 SQL> connect system/manager

Соединено. 17:21:47 SQL> show sga

Total System Global Area 152581388 bytes Fixed Size 70924 bytes Variable Size 78741504 bytes Database Buffers 73691136 bytes Redo Buffers 77824 bytes 17:21:49 SQL> show sqlcode

sqlcode 0 17:33:36 SQL> select * from emp;

select * from emp * ошибка в строке 1: ORA-00942: таблица или представление пользователя не существует

Затрач.время: 00:00:00.40 17:33:43 SQL> show sqlcode

sqlcode 942 17:33:50 SQL> show spool

spool OFF 17:34:52 SQL> show linesize

linesize 128 17:36:07 SQL> show user

USER имеет значение "SYSTEM" 17:36:10 SQL> show parameters buff

NAME TYPE VALUE buffer_pool_keep string buffer_pool_recycle string db_block_buffers integer 17991 log_buffer integer 32768 use_indirect_data_buffers boolean FALSE

Команда VARIABLE

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

<команда VARIABLE> ::=

VAR[IABLE] [<имя переменной> [<тип данных>]]

<тип данных> ::=

NUMBER

| CHAR [(<количество> [<единица измерения>])]
| NCHAR [(<количество>)]

<единица измерения> ::=

CHAR | BYTE

При вызове без параметров команда VARIABLE выдает список всех переменных, созданных в сеансе. Если указать только имя переменной, выдается информация только об этой переменной.

Связываемые переменные можно использовать как параметры хранимых процедур или непосредственно, в анонимных PL/SQL-блоках. Их нельзя использовать в команде или присвоить им значение в SQL-операторах, не входящих в PL/SQL-блоки. Вместо связанной переменной, не получившей явно значения, при необходимости подставляется значение NULL.

Рассмотрим простой пример использования связываемых переменных:

SQL> var

SP2-0568: Не объявлены переменные привязки. SQL> var dep number

SQL> begin

2 select deptno into :dep from emp

3 where sal = (select max(sal) from emp);

4 end;

SQL> /

Процедура PL/SQL успешно завершена.

SQL> select * from dept where deptno = :dep;

DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK

SQL> var dep2 number

SQL> c /:dep/:dep2

1* select * from dept where deptno = :dep2 SQL> /

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

SQL> var

переменная dep тип данных NUMBER

переменная dep2 тип данных NUMBER

Мы использовали связываемую переменную для передачи значения из одного оператора SQL в другой, так и не выдав его на экран. Чтобы значения используемых в команде связываемых переменных выдавались автоматически, необходимо выполнить команду SET AUTOPRINT ON.

Типы данных для связываемых переменных аналогичны соответствующим типам данных PL/SQL. Переменные типа REFCURSOR позволяют работать с курсорными переменными PL/SQL. Утилита SQL*Plus обрабатывает связываемые переменные такого типа особым образом. Курсор, соответствующий курсорной переменной, открывается явно, а закрывается после выдачи значения или при завершении сеанса. При выдаче значения на экран выдается результирующее множество соответствующего запроса. Рассмотрим пример:

SQL> set autoprint on

SQL> var a refcursor;

SQL> begin

2 open :a for select * from dept;

3 end;

4 /

Процедура PL/SQL успешно завершена.

DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 TRAINING KIEV

SQL> exec open :a for select ename, sal from emp where deptno = :dep;

Процедура PL/SQL успешно завершена.

DEP ---------- 10

ENAME SAL ---------- ---------- CLARK 2450 KING 5000 MILLER 1300

Результаты, выдаваемые при показе значений связываемой переменной типа REFCURSOR можно форматировать так же, как и результаты выполнения SQL-оператора SELECT. Значение такой переменной выдается только один раз, - затем результирующее множество надо выбирать повторно.

Для явной выдачи на экран значения связываемой переменной используется команда PRINT.

Команды администрирования базы данных

Ряд команд утилиты SQL*Plus предназначен для АБД. По сути, SQL*Plus- полнофункциональное средство администрирования. Доступные в SQL*Plus команды администрирования рассматриваются в данном разделе.

Команды BTITLE и TTITLE

Утилита SQL*Plus позволяет задавать заголовок для показа в качестве верхнего (команда TTITLE) и нижнего (команда BTITLE) колонтитула на каждой странице отчета. Эти команды имеют следующий синтаксис:

<команда BTITLE> ::=

BTI[TLE] [<спецификации печати> <текст или переменная>{ <текст или переменная>}] [<вкл-выкл>]

<команда TTITLE> ::=

TTI[TLE] [<спецификации печати> <текст или переменная>{ <текст или переменная>}] [<вкл-выкл>]

<спецификации печати> ::=

<спецификация печати>{ <спецификация печати>}

<спецификация печати> ::=

COL <позиция>

| S[KIP] [<количество строк>]

| TAB <количество табуляций>

| LE[FT]

| CE[NTER]

| R[IGHT]

| BOLD

| FORMAT <формат>

<текст или переменная> ::=

<текст>

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

Конструкция <вкл-выкл> (ON | OFF) позволяет включать и отключать вывод колонтитулов, не влияя на их определения.

Назначение спецификаций печати команд BTITLE и TTITLE представлено в .

Таблица 11. Спецификации печати в командах BTITLE, TTITLE, REPHEADER и REPFOOTER.

Спецификация	Назначение
COL	Выравнивает текст по указанной символьной позиции.
SKIP	Выводит указанное количество пустых строк. По умолчанию - одну пустую строку. Значение 0 означает возврат к началу строки.
TAB	Пропускает указанное количество столбцов (позиций табуляции). Если указано отрицательное значение, смещает текущую позицию на соответствующее количество столбцов влево.
LEFT CENTER RIGHT	Задает выравнивание текущей строки (влево, по центру, вправо, соответственно). Все следующие элементы колонтитула (до конца спецификации или до следующей спецификации LEFT, CENTER, RIGHT или COL) выравниваются вместе, как группа. При этом используется длина строки, заданная с помощью команды SET LINESIZE.
BOLD	Выделяет данные. На терминале для этого одинаковые данные печатаются в трех последовательных строках. При выводе на печать строка обычно выдается жирным шрифтом.
FORMAT	Задает модель формата для последующих данных. Допустимые элементы модели формата см. в выше.

При вызове без параметров эти команды выдают текущий формат и признак вывода соответствующего колонтитула.

Если спецификации колонтитула необходимо перенести на следующую строку, предыдущая строка завершается дефисом (-).

Рассмотрим простой пример задания верхнего колонтитула для отчета:

SQL> ttitle left 'Список отделов' center '6 декабря 2001 г.' -

> right 'Стр.: ' format 999 SQL.PNO

SQL> select * from dept;

Список отделов 6 декабря 2001 г. Стр.: 1 DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Команды DEFINE и UNDFEFINE

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

<команда DEFINE > ::=

DEF[INE] [<имя переменной> [= <текст>]]

Если текст, присваиваемый переменной, содержит пробелы или символы пунктуации, его надо брать в апострофы.

При вызове без значения, команда DEFINE выдает значение переменной. Команда DEFINE без параметров выдает значение всех пользовательских переменных SQL*Plus, включая системные. Рассмотрим пример:

SQL> define a=text

SQL> define a

DEFINE A = "text" (CHAR) SQL> define b=5

SQL> define

DEFINE _SQLPLUS_RELEASE = "801060000" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production" (CHAR) DEFINE _O_RELEASE = "801060000" (CHAR) DEFINE A = "text" (CHAR) DEFINE B = "5" (CHAR) SQL>

Как видите, утилита SQL*Plus автоматически определяет ряд системных пользовательских переменных.

Для удаления как явно определенной пользовательской переменной, так и параметра командной строки сценария (см. описание команды выше) используется команда UNDEFINE. После применения этой команды к переменной значение переменной теряется, и она становится неопределенной. Команда UNDEFINE

имеет следующий простой синтаксис:

<команда UNDEFINE> ::=

UNDEF[INE] <имя переменной>{ <имя переменной>}

Продолжая предыдущий пример:

SQL> undefine a b

SQL> define a

SP2-0135: символ a UNDEFINED SQL>

Команды для работы с файлами

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

Команды REPHEADER и REPFOOTER

Команды REPHEADER и REPFOOTER позволяют задать текст, выдаваемый в начале и в конце каждого отчета, соответственно. При этом используются те же спецификации печати, что и в командах BTITLE и TTITLE (см. выше). Команды имеют следующий синтаксис:

<команда REPHEADER> ::=

REP[HEADER] [PAGE] [<спецификации печати>
<текст или переменная>{ <текст или переменная>}] [<вкл-выкл>]

<команда REPFOOTER> ::=

REP[FOOTER] [PAGE] [<спецификации печати>

<текст или переменная>{ <текст или переменная>}] [<вкл-выкл>]

Если указана опция PAGE, то соответствующий текст выдается на отдельной странице.

При вызове без параметров эти команды выдают текущий формат и признак вывода текста в начале и в конце отчета.

Комментарии в командных файлах

В командных файлах можно вводить и использовать комментарии трех видов:

однострочные комментарии, задаваемые командой SQL*Plus REMARK;

одно- или многострочные комментарии SQL /* ... */;

однострочные комментарии ANSI/ISO --.

Команда REMARK имеет следующий простой синтаксис:

<команда REMARK> ::=

REM[ARK] [<любые символы до конца строки>]

Команда REMARK должна быть первой командой в строке. Задаваемый ею комментарий продолжается до конца строки.

Комментарии /* ... */ можно вводить в виде отдельных строк в командном файле, в строке оператора SQL или PL/SQL-блока. После пары символов начала комментария (/*) обязательно должен идти пробел, иначе косая рассматривается как команда выполнения содержимого SQL-буфера. Такие комментарии не могут быть вложенными. Комментарий, введенный в командной строке SQL*Plus, не попадает в буфер SQL.

Комментарии в виде -- ... можно вводить в конце строки с часть оператора SQL или PL/SQL-блока (такой комментарий продолжается до конца строки). Таким комментарием нельзя завершать команду SQL*Plus- он должен быть первой командой в строке.

При размещении комментариев в командных файлах необходимо придерживаться ряда простых правил, связанных с особенностями работы утилиты SQL*Plus:

Не размещайте комментарии среди первых нескольких ключевых слов оператора SQL.

Не размещайте комментарии после символа-завершителя команды SQL*Plus (точки, точки с запятой или косой).

Не размещайте символы-завершители команды в конце строки комментария или после комментария в SQL-операторе или PL/SQL-блоке.

Не используйте в комментариях метасимвол &, - утилита SQL*Plus потребует ввести значение параметра, проинтерпретировав слово после символа & в качестве имени параметра.

Рассмотрим пример командного файла, использующего все виды комментариев:

-- Это однострочный комментарий SQL*Plus

set linesize 128; REM И это тоже однострочный комментарий SQL*Plus

select ename, empno /* Это многострочный ** комментарий в ** SQL-операторе */

from emp -- однострочный комментарий в SQL-операторе

where sal = 5000;

Вот результат его выполнения:

SQL> @f:\tmp.txt

ENAME EMPNO ---------- ---------- KING 7839

Копирование данных из одной базы данных в другую

Утилита SQL*Plus предлагает собственные средства копирования данных из одной базы данных в другую и из одной таблицы в другую. Для этого используется команда COPY, которая позволяет:

копировать данные с удаленной базы данных в текущую;

копировать данные из текущей базы данных на удаленную;

копировать данные из одной удаленной базы данных в другую.

Примечание

В общем случае, предполагалось копирование данных между базой данных Oracle и базой данных другого производителя. В среде Oracle для копирования данных можно использовать SQL-операторы (CREATE TABLE AS и INSERT).

Команда COPY имеет следующий синтаксис:

<команда COPY> ::=

COPY <направление копирования> <действие копирования>

<имя целевой таблицы> [(<имя столбца>{, <имя столбца>})] USING <запрос>

<направление копирования> ::=

FROM <база данных>

| TO <база данных>

| FROM <база данных> TO <база данных>

<база данных> ::=

<имя пользователя>[/<пароль>]@<строка связи>

<действие копирования> ::=

APPEND | CREATE | INSERT | REPLACE

Она копирует данные, возвращаемые запросом, в таблицу в локальной или удаленной базе данных. Поддерживаются только базовые типы данных (CHAR, DATE, LONG, NUMBER, VARCHAR2) и в будущих версиях (после 9.0.1) поддержка этой команды не гарантируется.

Если в конструкции <база данных> пароль не указан, SQL*Plus запросит его перед выполнением команды.

Действия копирования описаны в .

Таблица 12. Действия копирования команды COPY.

Действие	Описание
APPEND	Вставляет возвращенные запросом строки в целевую таблицу, если она существует. Если же таблица не существует, она создается.
CREATE	Вставляет возвращенные запросом строки в целевую таблицу, предварительно создавая ее. Если же таблица уже существует, возвращается сообщение об ошибке.
INSERT	Вставляет возвращенные запросом строки в целевую таблицу. Если таблица не существует, возвращается сообщение об ошибке. В список выбора запроса должно входить по одному столбцу для каждого столбца целевой таблицы.
REPLACE	Заменяет целевую таблицу и ее содержимое строками, возвращенными запросом. Если целевая таблица не существует, она создается. В противном случае, существующая таблица удаляется и вместо нее создается новая, содержащая скопированные данные.

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

По умолчанию, утилита SQL*Plus выполняет фиксацию транзакции после успешного завершения команды COPY. Если выполнить команду , где n - положительное целое число, фиксация будет выполняться после каждого n-го пакета записей. Размер пакета задается с помощью команды .

Вся команда COPY должна поместиться на одной строке. Если необходимо перенести команду на следующую строку в конце строки необходимо ввести дефис (-).

Рассмотрим простой пример копирования таблицы:

SQL> copy from scott/tiger@training -

> create emp10 -

> using select * from emp where deptno = 10;

Размер массива выборки/привязки равен 15. (arraysize равен 15) Фиксация по завершении. (copycommit равен 0) Максимальная длина равна 80. (long равна 80) Таблица EMP10 создана.

3 строк выбрано из scott@training. 3 строк вставлено в EMP10. 3 строк зафиксировано в EMP10 по соединению DEFAULT HOST.

SQL> set linesize 100 SQL> select * from emp10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ------ --------- ------- -------- ---------- ------ ------ 7782 CLARK MANAGER 7839 09.06.81 2450 10 7839 KING PRESIDENT 17.11.81 5000 10 7934 MILLER CLERK 7782 23.01.82 1300 10

Начальный сценарий

При запуске можно выполнить начальный сценарий (файл с командами SQL*Plus, операторами SQL и анонимными PL/SQL-блоками) и передать ему аргументы. Этот файл может находиться в локальной файловой системе или (только в версии 9.0.1 для Windows) на Web-сервере. Начальный сценарий запускается так же, как если бы он был передан в качестве аргумента команде START.

Если для начального сценария не указан суффикс в имени файла, используется суффикс. заданный командой SET SUFFIX (по умолчанию, .sql).

Настройка среды SQL*Plus

Среда SQL*Plus - очень гибкая и имеет широкие возможности настройки по требованиям пользователя. Эти настройки выполняются, в основном, с помощью команды SET. Мы неоднократно использовали команду SET в примерах данного модуля. В следующем разделе представлено ее формальное описание.

Назначение SQL*Plus

Утилита SQL*Plus позволяет выполнять команды SQL и блоки PL/SQL, а также решать ряд других задач. С помощью SQL*Plus можно:

вводить, редактировать, запоминать, загружать и выполнять команды SQL и блоки PL/SQL;

форматировать, создавать, сохранять, печатать и публиковать в Web результаты выполнения запросов (отчеты);

получать описание (имена и типы столбцов) любой таблицы и представления;

обращаться к удаленным базам данных и копировать из них данные;

посылать и принимать сообщения от конечных пользователей;

администрировать базу данных.

Ограничения

Подставляемые переменные нельзя использовать в командах редактирования буфера SQL (, , , ) и в других командах, где эта подстановка "не имеет смысла", в частности, в комментариях. Команды редактирования буфера считают символ & обычным и используют его буквально (см. пример ).

Опции командной строки

Как следует из синтаксиса, при вызове SQL*Plus можно указать ряд опций. Они кратко описаны в .

Таблица 1. Опции командной строки утилиты SQL*Plus.

Опция	Назначение
HELP	Выдает информацию об использовании и синтаксисе вызова команды sqlplus и завершает работу.
VERSION	Выдает информацию о версии утилиты SQL*Plus и завершает работу.
MARKUP	Опция MARKUP позволяет генерировать полную HTML-страницу по результатам запроса или сценария.
RESTRICT	Позволяет отключить ряд команд, взаимодействующих с операционной системой. Если указан уровень ограничения 3, сценарий login.sql в текущем каталоге не читается. Стандартный сценарий запуска glogin.sql читается, но ограниченные команды в нем не работают. Команды, недопустимые на каждом из уровней ограничения, представлены ниже в .
SILENT	Подавляет выдачу всех информационных сообщений и приглашений SQLPlus, включая приглашение командной строки, эхо команд и начальную информацию, обычно выдаваемую при входе в SQLPlus. Используется при вызове SQL*Plus из другой программы, когда его желательно скрыть, и совместно с опцией MARKUP при создании Web- отчетов.

Таблица 2. Команды, отключаемые уровнями ограничения.

Команда	Уровень 1	Уровень 2	Уровень 3
EDIT	отключена	отключена	отключена
GET			отключена
HOST, !	отключена	отключена	отключена
SAVE		отключена	отключена
SPOOL		отключена	отключена
START, @, @@			отключена
STORE		отключена	отключена

Основные команды

После запуска утилита SQL*Plus выдает приглашение командной строки (обычно, это строка "SQL> "), после которого можно вводить команды. Команды состоят из одного или нескольких слов, разделенных произвольным количеством пробелов и/или символов табуляции. Регистр символов в командах обычно не имеет значения. При нажатии клавиши Enter команда (при выполнении определенных условий по ее завершению) выполняется. После выполнения команды снова выдается приглашение.

Некоторые команды, например, блоки PL/SQL, могут занимать несколько строк. Тогда SQL*Plus выдает дополнительные приглашения с номером строки, например, "2> ".

В командной строке SQL*Plus можно вводить команды трех видов:

SQL-операторы для работы с информацией в базе данных;

блоки PL/SQL для работы с информацией в базе данных;

команды SQL*Plus для форматирования результатов запроса, установки опций, редактирования команд и т.д.

От вида команды зависит способ продолжения ее на следующей строке, завершения и выполнения.

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

Команда SHUTDOWN в SQL*Plus останавливает текущий экземпляр Oracle, к которому подключен пользователь, и может при этом закрыть и демонтировать базу данных. Эту команду можно применять только для серверов версии 8 и выше.

Команда SHUTDOWN имеет следующий синтаксис:

<команда SHUTDOWN> ::=

SHUTDOWN <режим остановки>

<режим остановки> ::=

ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]

Назначение опций команды SHUTDOWN описано в :

Таблица 18. Опции команды SHUTDOWN.

Опция	Назначение
ABORT	Выполняет немедленную остановку базы данных, не дожидаясь завершения транзакций и отключения пользователей. Эта опция используется при аварийном завершении одного из фоновых процессов или при невозможности нормальной остановки. При перезапуске потребуется восстановление.
IMMEDIATE	Не ждет завершения транзакций (автоматически их откатывает) и отключения пользователей. Новые подключения запрещаются. База данных закрывается и демонтируется, затем останавливается экземпляр. При перезапуске восстановление не потребуется.
NORMAL	Ждет отключения всех пользователей от базы данных (новые подключения запрещены). База данных закрывается и демонтируется, затем останавливается экземпляр. При перезапуске восстановление не потребуется. Эта опция используется по умолчанию.
TRANSACTIONAL [LOCAL]	Ждет завершения активных транзакций. При попытке начать новую транзакцию происходит отключение сеанса. После завершения всех активных транзакций все сеансы автоматически отключаются. Затем остановка идет как при вводе опции IMMEDIATE. Режим LOCAL задает такой режим остановки только для локальных транзакций. Сервер не ждет завершения удаленных транзакций.

Эту команду может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA к выделенному серверному процессу. По умолчанию используется опция NORMAL.

Параметры командных файлов

Утилита SQL*Plus позволяет создавать командные файлы, запрашивающие у пользователя параметры и подставляющие их значение в команды по ходу выполнения. Для этого используются т.н. пользовательские переменные. Такие переменные определяются в командном файле, в частности, с помощью команды . На пользовательские переменные можно ссылаться в командах, предваряя их имя символами & или &&. Конструкцию &<имя переменной> называют подставляемой переменной.

Передача параметров командных файлов при вызове

При вызове командного файла можно передавать параметры в виде аргументов командной строки. Утилита SQL*Plus подставляет значение аргументов командной строки вместо позиционных параметров командного файла (&1, &2 и т.д.). Первый аргумент подставляется вместо параметра &1, второй - вместо &2, и так далее.

Рассмотрим простой пример:

SQL> clear buffer

buffer очищена SQL> input

1 select ename, sal from emp

2 where deptno = &1

3 SQL> save test

Создано файл test SQL> start test 10

прежний 2: where deptno = &1 новый 2: where deptno = 10 ENAME SAL ---------- ---------- CLARK 2450 KING 5000 MILLER 1300

Подключение к стандартной и удаленной базе данных

Для доступа к данным необходимо сначала подключиться к базе данных. При запуске SQL*Plus обычно происходит подключение к стандартной базе данных с указанным именем пользователя и паролем. После запуска и успешной регистрации можно в любой момент подключиться к схеме другого пользователя или к другой базе данных. Для этого используется команда CONNECT.

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

Подстановка без повторного запроса

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

SQL> select max(&col), avg(&col), min(&col) from &tab;

Введите значение для col: sal Введите значение для col: sal Введите значение для col: sal Введите значение для tab: emp прежний 1: select max(&col), avg(&col), min(&col) from &tab новый 1: select max(sal), avg(sal), min(sal) from emp

MAX(SAL) AVG(SAL) MIN(SAL) ---------- ---------- ---------- 5000 2073,21429 800

Чтобы значение переменной запрашивалось только один раз, используется подстановка с двумя амперсантами (&&):

SQL> c /(&col/(&&col

1* select max(&&col), avg(&col), min(&col) from &tab SQL> c /(&col/(&&col

1* select max(&&col), avg(&&col), min(&col) from &tab SQL> c /(&col/(&&col

1* select max(&&col), avg(&&col), min(&&col) from &tab SQL> /

Введите значение для col: sal Введите значение для tab: emp прежний 1: select max(&&col), avg(&&col), min(&&col) from &tab новый 1: select max(sal), avg(sal), min(sal) from emp

MAX(SAL) AVG(SAL) MIN(SAL) ---------- ---------- ---------- 5000 2073,21429 800

Подстановка параметров командных файлов

Подстановка переменных выполняется и для позиционных параметров, переданных при вызове сценария. На эти параметры можно ссылаться как на &1, &2 и т.д. Если значение для них в командной строке не передано, SQL*Plus запрашивает значения при вызове сценария. Подстановка позиционных параметров выполняется только при вызове сценария командами (или ее сокращенными формами @, @@).

Рассмотрим пример:

SQL> clear buffer

buffer очищена SQL> input

1 select &1 from &2 2 . SQL> save f:\subst.sql

Создано файл f:\subst.sql SQL> @f:\subst max(sal) emp

прежний 1: select &1 from &2 новый 1: select max(sal) from emp

MAX(SAL) ---------- 5000 SQL> @f:\subst

прежний 1: select &1 from &2 новый 1: select max(sal) from emp

MAX(SAL) ---------- 5000 SQL> undef 1 2

SQL> @f:\subst

Введите значение для 1: min(sal) Введите значение для 2: emp прежний 1: select &1 from &2 новый 1: select min(sal) from emp

MIN(SAL) ---------- 800

Получение информации о времени выполнения команды

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

<команда TIMING> ::=

TIMI[NG] <команда таймера>

<команда таймера> ::=

START [<имя таймера>] | SHOW | STOP

Назначение команд таймера описано в .

Таблица 3. Команды таймера SQL*Plus.

Команда	Назначение
START	Запускает таймер и дает ему указанное имя. Можно использовать несколько активных таймеров, запуская дополнительные с помощью команды START прежде, чем останавливать исходный. Последний запущенный таймер становится текущим.
SHOW	Выдает имя и данные (время работы) текущего таймера.
STOP	Выдает имя и данные (время работы) текущего таймера, а затем останавливает и удаляет таймер. Если активно несколько таймеров, следующий, предпоследний по времени запуска, становится текущим.

Команда TIMING без параметров выдает количество активных таймеров.

Рассмотрим пример использования команды TIMING:

SQL> timing start first

SQL> select * from dept;

DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

SQL> timing show

таймер для: first Затрач.время: 00:00:16.74 SQL> timing stop

таймер для: first Затрач.время: 00:00:21.20 SQL>

Для удаления всех таймеров используется команда CLEAR TIMING.

Можно также автоматически выдавать время работы каждой команды помощью установки SET AUTOTRACE. Подробнее см. далее в разделе .

Получение оперативной справки

Утилита SQL*Plus позволяет получить справочную информацию по своим командам, а также основным объектам схем.

Прекращение команды по ходу работы

Для прекращения долго выполняющейся команды по ходу работы, необходимо ввести символ прерывания, обычно, Ctrl+C. При этом SQL*Plus прекратит выдачу результатов команды и выдаст приглашение.

Прекратить таким способом команду, выдающую результаты в файл с помощью команды , нельзя. Придется прекратить работу утилиты SQL*Plus, в которой была выдана команда, средствами операционной системы.

Расширенные формулы Бэкуса-Наура

При описании синтаксиса команд SQL*Plus использованы расширенные формулы Бэкуса- Наура (БНФ). Расширенные БНФ являются способом сокращенной записи контекстно- свободных грамматик. Нетерминальные символы (определяемые понятия, конструкции и команды SQL*Plus) взяты в угловые скобки. Терминальные символы выделены жирным шрифтом. Учтите, что команды SQL*Plus не зависят от регистра терминальных символов.

Метасимвол ::= означает "по определению есть". Метасимвол | обозначает, что данная формула является сокращенной записью нескольких формул с одинаковыми левыми частями и различными цепочками в правой части (т.е. возможен один из разделенных этим символом вариантов). Пара метасимволов [ ] используется для обозначения того, что находящаяся между ними цепочка может содержаться, а может и не содержаться в правой части формулы. Пара метасимволов { } означает, что содержащаяся между ними цепочка может повторяться ноль или более раз. Приоритет метасимвола | выше, чем у пар [ ] и { }.

Немного подробнее о расширенных БНФ можно прочитать здесь.

Редактирование команд

Команды SQL*Plus не попадают в буфер, поэтому редактировать их можно только по ходу ввода с помощью клавиши Backspace.

Утилита SQL*Plus поддерживает несколько команд редактирования текста в буфере. Они представлены в .

Таблица 4. Команды редактирования SQL*Plus.

Команда	Назначение
A[PPEND] <текст>	Добавляет <текст> в конце строки.
C[HANGE] /<старая>[/<новая>[/]]	Заменяет в строке подстроку <старая> строкой <новая>. Если <новая> не указана, просто удаляет подстроку <старая>.
CL[EAR] BUF[FER]	Удаляет все строки из буфера.
DEL [*]	Удаляет текущую строку.
DEL <n> [* \| <m> \| LAST]	Удаляет строку с номером <n>, или со строки <n> до текущей, до строки <m> или до последней строки, соответственно.
DEL * [<n> \| LAST]	Удаляет с текущей строки по строку <n> или до конца, соответственно.
DEL LAST	Удаляет последнюю строку.
ED[IT] [<имя файла>[.<расширение>]]	Вызывает текстовый редактор операционной системы и загружает в него буфер или указанный файл. Если редактируется буфер, то в текущем каталоге создается файл afiedt.buf, в котором и находится результат редактирования. Загрузка изменений в буфер выполняется автоматически после выхода из редактора. Стандартный редактор в ОС UNIX задается переменной среды _EDITOR. По умолчанию в ОС UNIX используется редактор ed, а в Windows - Notepad.
I[NPUT] [<текст>]	Вставляет строку или <текст>, в том числе, из нескольких строк, в буфер после текущей строки.
LIST [<n> \| * \| LAST]	Выдает, соответственно, все строки в буфере SQL, строку с указанным номером, текущую или последнюю строку.
LIST <n> [* \| <m> \| LAST]	Выдает строку с номером <n>, все строки, начиная со строки с номером <n>, до текущей, строки с номером <m> или до конца буфера, соответственно.
LIST * [<n> \| LAST]	Выдает все строки с текущей строки до строки с номером <n> или до последней строки, соответственно.

Рассмотрим пример:

SQL> select ename, salary from emp; select ename, salary from emp * ошибка в строке 1: ORA-00904: неверно имя столбца

SQL> c /salary/sal

1* select ename, sal from emp SQL> i where deptno=30;

SQL> /

ENAME SAL ---------- ---------- ALLEN 1600 WARD 1250 MARTIN 1250 BLAKE 2850 TURNER 1500 JAMES 950

6 строк выбрано.

SQL> list

1 select ename, sal from emp 2* where deptno=30

Регистрационная информация

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

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

Если вместо имени пользователя и пароля указана косая (/), предполагается стандартная регистрация от имени пользователя операционной системы (аутентификация операционной системой). При этом нельзя указывать строку связи. Cервер регистрирует пользователя OPS$name, где name - регистрационное имя текущего пользователя операционной системы. Префикс "OPS$" можно заменить любым другим с помощью соответствующего параметра инициализации сервера (OS_AUTHENT_PREFIX).

Назначение роли позволяет получить привилегированное подключение пользователям, имеющим системные привилегии SYSOPER или SYSDBA. Такое привилегированное подключение можно получить и при запуске с опциями / или /NOLOG. При указании роли пользователя во многих операционных системах (например, в NT) необходимо взять все аргументы команды в кавычки:

SQLPLUS "/ AS SYSDBA" SQLPLUS "SYSTEM/MANAGER AS SYSOPER"

Опция /NOLOG позволяет запустить утилиту SQL*Plus без регистрации. Прежде, чем выполнять любые SQL-операторы, придется выполнить команду CONNECT для подключения. Эта опция обычно используется при запуске сценариев, явно подключающихся к базе данных с помощью команды CONNECT.

Системные переменные, влияющие на подстановку

В представлены системные установки, влияющие на подстановку пользовательских переменных.

Таблица 13. Системные установки, влияющие на подстановку переменных.

Установка	Описание
SET DEFINE	Задает символ подстановки (вместо стандартного - &) и позволяет включать и отключать подстановку.
SET ESCAPE	Задает символ маскировки, позволяющий маскировать символ подстановки. Стандартным символом маскировки является обратная косая (\).
SET VERIFY	Позволяет включать и отключать выдачу каждой строки команды до и после подстановки пользовательских переменных.
SET CONCAT	Задает символ, отделяющий имя подставляемой переменной от следующей непосредственно за ним строки. По умолчанию используется точка (.).

Сокращенные формы команды START (@, @@)

Утилита SQL*Plus поддерживает две сокращенные формы команды START: @ и @@. Команда @ функционально аналогична команде :

SQL> @test 20

прежний 2: where deptno = &1 новый 2: where deptno = 20

ENAME SAL ---------- ---------- SMITH 800 JONES 2975 SCOTT 3000 ADAMS 1100 FORD 3000

Примечание

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

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

Рассмотрим типичное использование команды @@ в файле ex_all.sql:

set serverout on

@@ex0304 @@ex0305 @@ex0308 @@ex0309 @@ex0603 @@ex0606 @@ex0607 @@ex0608 @@ex0705 @@excurvar

При его запуске с помощью команды @ из того же каталога будут выполнены все перечисленные в нем командные файлы, а результаты их работы будут выданы на экран:

SQL> @f:\usr\doc\orasdev\ex_all.sql SCOTT has a second highest salary!

Процедура PL/SQL успешно завершена.

Процедура создана.

Ошибок нет. ...

Создание командных файлов

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

Стандартный формат столбцов

Ширина числовых столбцов соответствует максимуму из ширины заголовка столбца, ширины, заданной с помощью опции COLUMN FORMAT, плюс один символ для знака, и ширины, заданной командой SET NUMWIDTH (по умолчанию, 10 символов). Если количество значащих цифр в числе больше, чем допускается шириной числового столбца, утилита SQL*Plus округляет число.

Для столбцов других типов ширина столбца соответствует его ширине в базе данных. Все эти столбцы по умолчанию выравниваются влево. Для столбцов типа DATE формат определяется соответствующими NLS-параметрами. Если они не заданы, предполагается формат A9 (см. ).

Связываемые переменные

Связываемые переменные - это создаваемые в SQL*Plus переменные, на которые можно ссылаться (как на хост-переменные) в блоках PL/SQL. Таким переменным можно, например, присваивать значения в блоках PL/SQL или использовать их значения во включенных в блоки SQL-операторах. Значения связываемых переменных можно затем выдавать в SQL*Plus.

Трассировка операторов

Утилита SQL*Plus позволяет автоматически получать отчет о способе выполнения оператора, выбранном оптимизатором SQL, а также статистическую информацию о выполнении. Этот отчет выдается после успешного выполнения операторов SELECT, INSERT, UPDATE и DELETE. Такой отчет полезен для контроля и настройки производительности этих операторов.

Для управления данным отчетом используется команда SET AUTOTRACE. Эта команда имеет пять опций:

OFF	Отчет не выдается. Это стандартный режим работы SQL*Plus.
ON EXPLAIN	Выдается только выбранный оптимизатором план выполнения оператора.
ON STATISTICS	Выдается только статистическая информация о выполнении оператора.
ON	Выдается план выполнения запроса и статистическая информация о выполнении оператора.
TRACEONLY	Выдается отчет о статистике, но не выдаются результаты выполнения оператора. Используется для трассировки запросов, возвращающих большие объемы данных.

Для использования этой возможности SQL*Plus необходимо создать в схеме пользователя таблицу PLAN_TABLE и получить роль PLUSTRACE (предоставить ее может только DBA). Выполним следующие действия:

SQL> set autotrace on

SP2-0613: Невозможно проверить формат или существование PLAN_TABLE SP2-0611: Ошибка разблокирования EXPLAIN report SP2-0618: Невозможно найти Идентификатор Сеанса. Проверьте, разрешена ли роль PLUSTRACE SP2-0611: Ошибка разблокирования STATISTICS report

Как видите, по умолчанию эта возможность не поддерживается. Создадим таблицу с помощью сценария $ORACLE_HOME/rdbs/admin/utlxplan.sql:

SQL> @g:\oracle\ora81\rdbms\admin\utlxplan

Таблица создана.

Затем создадим роль PLUSTRACE, дадим ей необходимые привилегии, а затем предоставим ее роли DBA (с помощью сценария $ORACLE_HOME/sqlplus/admin/plustrce.sql):

SQL> connect system/manager as sysdba

Соединено. SQL> @g:\oracle\ora81\sqlplus\admin\plustrce

SQL> SQL> drop role plustrace;

drop role plustrace * ошибка в строке 1: ORA-01919: роль 'PLUSTRACE' не существует

SQL> create role plustrace;

Роль создана.

SQL> SQL> grant select on v_$sesstat to plustrace;

Привилегии предоставлены.

SQL> grant select on v_$statname to plustrace;

Привилегии предоставлены.

SQL> grant select on v_$session to plustrace;

Привилегии предоставлены.

SQL> grant plustrace to dba with admin option;

Привилегии предоставлены.

SQL> set echo off

Теперь предоставим роль PLUSTRACE пользователю, который будет использовать трассировку:

SQL> grant plustrace to scott;

Привилегии предоставлены.

Проверяем, что трассировочный отчет теперь выдается:

SQL> connect scott/tiger

Соединено. SQL> set autotrace on

SQL> set pagesize 25

SQL> select ename, dname, sal

2 from emp, dept

3 where emp.deptno = dept.deptno;

ENAME DNAME SAL ---------- -------------- ---------- SMITH RESEARCH 800 ALLEN SALES 1600 WARD SALES 1250 JONES RESEARCH 2975 MARTIN SALES 1250 BLAKE SALES 2850 CLARK ACCOUNTING 2450 SCOTT RESEARCH 3000 KING ACCOUNTING 5000 TURNER SALES 1500 ADAMS RESEARCH 1100 JAMES SALES 950 FORD RESEARCH 3000 MILLER ACCOUNTING 1300

14 строк выбрано.

План выполнения ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=88) 1 0 HASH JOIN (Cost=3 Card=2 Bytes=88) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=44) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=41 Bytes=1353)

Статистика ---------------------------------------------------------- 62 recursive calls 8 db block gets 6 consistent gets 0 physical reads 0 redo size 1267 bytes sent via SQL*Net to client 424 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 14 rows processed

Подробнее использование возможностей трассировки в SQL*Plus рассмотрено в отдельном модуле, посвященном настройке производительности.

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

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

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

Для управления режимом архивирования журналов повторного выполнения утилита SQL*Plus предлагает команду ARCHIVE LOG, которая имеет следующий синтаксис:

<команда ARCHIVE LOG > ::=

ARCHIVE LOG <команда или журнал> [TO <место назначения>]

<команда или журнал> ::=

Эта команда позволяет начать или остановить автоматическое архивирование оперативных файлов журнала повторного выполнения, явно заархивировать указанные по номерам файлы или выдать информацию о них. Опции команды ARCHIVE LOG описаны в .

Таблица 19. Опции команды 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 list

Режим журнала базы данных Режим архива Автоматическое архивирование Включено Место размещения архива g:\oracle\oradata\training Самая старая последовательность оперативных журналов 745 Следующая последовательность журналов для архивирования 747 Текущая последовательность журналов 747 SQL> archive log 745

ORA-16013: журнал 1 с номером последовательности 745 не требует архивирования ORA-00312: оперативный протокол 1 процесса 1: 'G:\ORACLE\ORADATA\TRAINING\REDO01.LOG'

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

Напишите командный файл SQL*Plus, выдающий все записи указанной таблицы в файл с именем <имя таблицы>.unl в виде строк, поля которых разделены символом вертикальной черты (|). Заголовки столбцов не выдавать.
Например, для таблицы dept содержимое файла должно иметь следующий вид:
10|ACCOUNTING|NEW YORK 20|RESEARCH |DALLAS 30|SALES |CHICAGO 40|OPERATIONS|BOSTON 50|TRAINING |KIEV
Файл такого вида подходит для загрузки в базы данных Informix :).

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

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

Напишите командный файл SQL*Plus, выдающий по указанному имени представления текст оператора для его создания (CREATE VIEW) в файл с именем <имя>.sql. См. представление USER_VIEWS в словаре данных, описывающее представления данного пользователя. Длина каждой строки в файле не должна превышать 80 символов, чтобы файл было удобно читать.

Напишите сценарий SQL*Plus (без применения PL/SQL), увеличивающий вдвое зарплату всем сотрудникам отдела, средняя зарплата в котором ниже, чем средняя зарплата по всей организации (см. таблицы emp и dept). Затем сценарий должен выдать (в указанный при вызове файл) отчет о сотрудниках с указанием средней зарплаты по отделам. Снабдите столбцы отчета заголовками на русском языке.
Copyleft (no c) 2001-2004 В. Кравчук, OpenXS Initiative, Cоставление, перевод, дополнения, упражнения и примеры

Для освоения всех возможностей утилиты

Для освоения всех возможностей утилиты SQL*Plus необходим практический опыт ее использования. Упражнения позволят вам его получить.

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

Если база данных работала в режиме 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 кратко описаны в .

Таблица 20. Опции команды RECOVER.

Опция	Назначение
AUTOMATIC	Автоматически генерирует имя архивного файла журнала повторного выполнения, необходимого для продолжения операции восстановления. Для этого используются значения параметров конфигурации LOG_ARCHIVE_DEST и LOG_ARCHIVE_FORMAT (или соответствующие стандартные значения). Если файл с таким именем не найден, SQL*Plus запрашивает имя файла, выводя автоматически сгенерированное в качестве подсказки. Имя запрашивается также, если не указана ни опция AUTOMATIC, ни опция . Если заранее известно, что архивирование выполнялось в файл с нестандартным именем, имеет смысл сразу указать опцию LOGFILE.
FROM <местонахождение>	Задает местонахождение архивных файлов журнала повторного выполнения. По умолчанию используется значение параметра инициализации LOG_ARCHIVE_DEST. Можно также задать местонахождение архивных файлов с помощью команды SQL*Plus .
LOGFILE	Продолжает восстановление носителей, применяя указанный файл журнала повторного выполнения. При восстановлении в интерактивном режиме (AUTORECOVERY OFF), запрашивает новое имя файла, если указанный файл журнала не найден.
TEST ALLOW <целое число> CORRUPTION	В случае повреждения файла журнала указывает, при скольких поврежденных блоках еще можно продолжать восстановление. В ходе обычного восстановления это значение не должно быть более 1.
CONTINUE	Продолжает восстановление нескольких экземпляров после прерывания для отключения восстановления одного из них.
CONTINUE DEFAULT	Продолжает восстановление, используя автоматически сгенерированное имя архивного файла журнала повторного выполнения, если оно не указано явно. Аналогично опции , но не запрашивает альтернативное имя файла, если файл не найден.
CANCEL	Прерывает восстановление, ведущееся до 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 нельзя выполнять из того же сеанса, из которого была выполнена команда .
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;

Выход из SQL*Plus

Для выхода из SQL*Plus необходимо ввести команду EXIT. Будет выдана информация о версии сервера, к которому была подключена утилита, и о версии SQL*Plus. Затем работа программы завершается и либо закрывается окно, либо вы получаете приглашение командной строки операционной системы.

Команда EXIT имеет следующий синтаксис:

<команда EXIT> ::=

EXIT [<код возврата>][<завершение транзакции>]
QUIT [<код возврата>][<завершение транзакции>]

<код возврата> ::=

SUCCESS | FAILURE | WARNING

| <целое число> | <переменная> | :<связываемая переменная>

<завершение транзакции> ::=

COMMIT | ROLLBACK

По умолчанию при выполнении команды EXIT SQL*Plus фиксирует текущую транзакцию и завершает работу с кодом возврата 0 (SUCCESS).

Выполнение блоков PL/SQL

Для ввода блоков PL/SQL необходимо работать в режиме PL/SQL. Утилита SQL*Plus переходит в это режим, если:

в командной строке введено ключевое слово DECLARE или BEGIN;

введена одна из команд SQL, создающая хранимую программную единицу, например, CREATE PROCEDURE.

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

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

Из командной строки SQL*Plus можно выполнять любые команды базовой операционной системы. Для этого используется команда HOST, за которой идет командная строка для операционной системы.

Команду HOST можно сокращать до первых двух букв. Если команда введена без параметров, открывается сеанс стандартного командного интерпретатора операционной системы. В нем можно выполнять любое количество команд. Для выхода из командного интерпретатора UNIX достаточно выполнить команду EXIT или ввести Ctrl+D.

Доступ к команде HOST можно запретить, запуская утилиту SQL*Plus с любым уровнем ограничения.

Выполнение команд SQL*Plus

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

Если необходимо ввести длинную команду SQL*Plus, ее можно перенести на следующую строку. Для этого в конце строки необходимо ввести дефис (-) и только затем нажать клавишу Enter. Будет выдано приглашение, и ввод команды можно будет продолжить.

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

Выполнение командных файлов

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

Команда START имеет следующий синтаксис:

<команда START> ::=

STA[RT] <имя или ссылка> [<аргумент>{ <аргумент>}]

<имя или ссылка> ::=

<имя файла>[.<расширение>] | <URI>

Файл, передаваемый команде START по имени (или по ссылке на Web-сайт в Oracle9i на платформе Windows), может содержать любые команды, которые можно вводить в диалоговом режиме. Если расширение в имени файла не указано, предполагается стандартное расширение (SQL или заданное командой ).

Указанный по короткому имени файл будет сначала искаться в текущем каталоге, затем- в каталогах, входящих в стандартный путь поиска (обычно задается переменной среды SQLPATH).

Выполнение операторов SQL

Оператор SQL можно завершить тремя способами:

точкой с запятой (;)

строкой, содержащей только косую черту (/)

пустой строкой

Точка с запятой в конце строки означает выполнение оператора. Утилита SQL*Plus выполняет оператор и запоминает его в буфере SQL. Этот символ является признаком завершения команды и после него нельзя вводить комментарий.

Косая черта в качестве первого и единственного символа очередной строки означает, что оператор надо выполнить. Утилита SQL*Plus выполняет оператор и запоминает его в буфере SQL.

Пустая строка в операторе SQL или сценарии SQL*Plus обычно означает, что ввод команды завершен, но выполнять ее пока не надо (это поведение можно изменить с помощью команды SQL*Plus SET SQLBLANKLINES). Команда помещается в буфер SQL и остается там, пока не будет введена другая команда.

Взаимодействие с пользователем

Стандартный механизм запроса значений пользовательских переменных дает ограниченные средства взаимодействия с пользователем - ввод значений в ответ на стандартные приглашения. Утилита SQL*Plus позволяет управлять выдачей сообщений и запросом значений переменных.

Загрузка командных файлов

В любой момент в ходе работы с SQL*Plus можно загрузить содержимое любого текстового файла в буфер SQL. Для этого используется команда GET со следующим синтаксисом:

<команда GET> ::=

GET <имя файла>[.<расширение>] [<режим выдачи>]

<режим выдачи> ::=

LIS[T] | NOL[IST]

Если расширение не указано, предполагается стандартное расширение (SQL или заданное командой ).

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

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

По умолчанию и в режиме LIST содержимое загруженного файла выдается на экран. Подавить выдачу содержимого командного файла позволяет режим NOLIST.

Запись в файл и печать результатов выполнения запросов

Утилита SQL*Plus позволяет сбросить результаты выполнения команд в файл и распечатать их на стандартном принтере. Такой сброс называют спулингом. Для этого используется команда SPOOL (управляющая спулингом) со следующим синтаксисом:

<команда SPOOL> ::=

SPO[OL] [<файл или команда>]

<файл или команда> ::=

<имя файла>[.<расширение>] | OFF | OUT

Команда SPOOL выдает результаты выполнения команд SQL*Plus в указанный файл и, возможно, на стандартный принтер, независимо от их отображения на экране. При вызове без параметров команда выдает состояние спулинга. Если не указано расширение имени файла, используется стандартное расширение (обычно, LST или LIS).

Команда OFF прекращает спулинг. Команда OUT прекращает спулинг и посылает файл на стандартный принтер базовой операционной системы.

Чтобы сбрасываемые в файл результаты не выдавались на экран, необходимо выполнить команду .

В представлен пример сценария SQL*Plus, использующего команду SPOOL для выдачи исходного текста хранимой программной единицы в файл с соответствующим именем. Этот файл, в свою очередь, является сценарием, пригодным для повторного создания хранимой программной единицы. Такой прием,- генерация командных файлов в результате выполнения командных файлов - часто используется опытными администраторами баз данных при работе с SQL*Plus.

set feedback off set heading off set termout off set linesize 1000 set trimspool on set verify off spool &1..sql

prompt set define off select decode( type'-'to_char(line,'fm99999'), 'PACKAGE BODY-1', '/'chr(10), null) decode(line,1,'create or replace ', '' ) text text from user_source where name = upper('&&1') order by type, line; prompt / prompt set define on spool off

set feedback on set heading on set termout on set linesize 100

Вызывать данный сценарий можно, например, так:

SQL> @f:\getcode ListBlackFridays

Примечание

На платформе Windows при использовании оконной версии SQL*Plus (sqlplusw.exe) файл, указанный в команде SPOOL, по умолчанию (если не задан полный путь) создается в каталоге %ORACLE_HOME%\bin. Вряд ли это подходящее место для таких файлов...

Мы еще вернемся к сценарию getcode.sql в следующих разделах, посвященных параметрам и настройке среды SQL*Plus.

Запоминание установок среды SQL*Plus

Утилита SQL*Plus поддерживает многочисленные установки (см. раздел ""), которые имеет смысл запоминать между сеансами. Для этого используется команда STORE:

<команда STORE> ::=

STORE SET <имя файла>[.<расширение>] [<режим записи>]

Эта команда записывает значения переменных среды SQL*Plus в командный файл базовой операционной системы:

SQL> store set f:\env

Создано file f:\env

Вот примерное содержимое полученного файла f:\env.sql, созданного в SQL*Plus 8.1.6:

Листинг 1. Типичные установки среды SQL*Plus.

set appinfo OFF set appinfo "SQL*Plus" set arraysize 15 set autocommit OFF set autoprint OFF set autorecovery OFF set autotrace OFF set blockterminator "." set cmdsep OFF set colsep " " set compatibility NATIVE set concat "." set copycommit 0 set copytypecheck ON set define "&" set describe DEPTH 1 LINENUM OFF INDENT ON set markup HTML OFF SPOOL OFF ENTMAP ON PRE OFF set echo OFF set editfile "afiedt.buf" set embedded OFF set endbuftoken "" set escape OFF set feedback 6 set flagger OFF set flush ON set heading ON set headsep "|" set linesize 128 set logsource "" set long 80 set longchunksize 80 set newpage 1 set null "" set numformat "" set numwidth 10 set pagesize 25 set pause OFF set recsep WRAP set recsepchar " " set serveroutput ON size 2000 format WORD_WRAPPED set shiftinout invisible set showmode OFF set sqlblanklines OFF set sqlcase MIXED set sqlcontinue "> " set sqlnumber ON set sqlprefix "#" set sqlprompt "SQL> " set sqlterminator ";" set suffix "sql" set tab ON set termout ON set time OFF set timing OFF set trimout ON set trimspool OFF set underline "-" set verify ON set wrap ON

Режим записи указывает, будет ли файл просто создан (CREATE, используется по умолчанию), переписан, если существует (REPLACE), или же значения установок среды SQL*Plus будут добавлены в конец существующего файла (APPEND). Полученный командный файл может быть выполнен командой или ее сокращенными формами (@, @@).

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

Начиная с Oracle8, утилиту SQL*Plus можно использовать для запуска и остановки базы данных (ранее для этих целей использовались утилиты svrmgrl и SQL*DBA). Запуск базы данных состоит из трех шагов:

Запуск экземпляра.

Экземпляр - это совокупность фоновых процессов и областей памяти, необходимых для доступа к базе данных Oracle. Монтирование базы данных.

При монтировании база данных связывается с запущенным ранее экземпляром.

Открытие базы данных.

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

Запуск базы данных в SQL*Plus выполняется командой STARTUP, имеющей следующий синтаксис:

<команда STARTUP> ::=

STARTUP [FORCE] [RESTRICT] [PFILE=<имя файла параметров>] <действие запуска>

<действие запуска> ::=

MOUNT [<имя базы данных>]

| OPEN [<опция открытия>][<имя базы данных>]

| NOMOUNT

<опция открытия> ::=

READ ONLY

| READ WRITE [RECOVER]

| RECOVER

Все варианты команды STARTUP, так или иначе, запускают экземпляр (выделяется память и запускаются фоновые процессы). Опции команды STARTUP

представлены в .

Таблица 17. Опции команды 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	Требует выполнить восстановление носителей, если необходимо, перед запуском экземпляра. Применение этой опции аналогично выполнению команды с последующим обычным запуском. Так можно выполнять только полное восстановление.

Эту команду может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA к выделенному серверному процессу. По умолчанию используется опция OPEN. Команда STARTUP OPEN RECOVER монтирует и открывает базу данных, даже если полное восстановление закончилось неудачно.

Запуск SQL*Plus и выход из него

Для запуска утилиты SQL*Plus используется команда операционной системы, обычно - sqlplus. Запуск выполняется из командного интерпретатора или окна командной строки.

В версиях для операционных систем Microsoft использовались другие имена команды для вызова SQL*Plus, например, sqlplus30. В настоящее время (начиная с версии 8.1.5) в этих операционных системах имеется две версии этой утилиты: sqlplus, работающая в окне командной строки, и оконная версия, sqlplusw.

Общий синтаксис вызова SQL*Plus имеет вид:

sqlplus [<опции>] [<регистрационная информация>] [<начальный сценарий>]

где:

<опции> ::=

-H[ELP]

| -V[ERSION]

| -M[ARKUP] <опция разметки> [-R[ESTRICT] <уровень> [-S[ILENT]]]

<опция разметки> ::=

HTML [<вкл-выкл>] [HEAD <текст>] [BODY <текст>] [TABLE <текст>]
[ENTMAP <вкл-выкл>] [SPOOL <вкл-выкл>] [PRE[FORMAT] <вкл-выкл>]

<вкл-выкл> ::=

ON | OFF

<уровень> ::=

1 | 2 | 3

<регистрационная информация> ::=

<пользователь и база данных> [AS <роль пользователя>]

| /NOLOG

<пользователь и база данных> ::=

<имя пользователя>[/<пароль>][@<строка связи>] | /

<роль пользователя> ::=

SYSOPER | SYSDBA

<начальный сценарий> ::=

@<имя или ссылка> [<аргументы сценария>]

<имя или ссылка> ::=

<имя файла>[.<расширение>] | <URI>

<аргументы сценария> ::=

<аргумент>{<пробелы><аргумент>}

Аннотация

В версии 10 Oracle появился специальный режим работы оптимизатора затрат (cost optimizer), позволяющий провести углубленный анализ запроса и предложить для последующего выполнения более качественный, чем обычно, план. В статье рассматривается пример программного выполнения углубленного анализа запросов с помощью нового в версии 10 пакета DBMS_SQLTUNE.

Настройка отдельных запросов

Рассмотрим пример настройки с помощью пакета DBMS_SQLTUNE конкретного запроса.

Откроем в SQL*Plus сеанс связи с СУБД от имени SCOTT и сделаем необходимые приготовления:

CONNECT scott/tiger

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT ename, loc, sal, hiredate FROM emp, dept WHERE emp.deptno = dept.deptno

;

Ответ на запрос может выглядеть примерно так:

Execution Plan ---------------------------------------------------------- Plan hash value: 615168685

--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 532 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 532 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 84 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------

Predicate Information (identified by operation id): ---------------------------------------------------

1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note ----- - dynamic sampling used for this statement

Переключимся на другой сеанс от имени SYS, например так:

HOST sqlplus / AS SYSDBA

Создадим задание на автоматическую настройку запроса со стороны пользователя SCOTT (на углубленный анализ):

DECLARE my_task_name VARCHAR2 ( 30 ); my_sqltext CLOB;

BEGIN my_sqltext := 'SELECT ename, loc, sal, hiredate FROM emp, dept ' 'WHERE emp.deptno = dept.deptno' ;

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_text => my_sqltext , user_name => 'SCOTT' , task_name => 'my_sql_tuning_task'

); END; /

Узнать состояние задания можно из словаря-справочника:

SELECT status, execution_start start_time, execution_end end_time FROM dba_advisor_log WHERE owner = 'SYS' AND task_name = 'my_sql_tuning_task' ;

Оно будет 'INITIAL'.

Запустим задание для настройки запроса:

EXECUTE - DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( 'my_sql_tuning_task' );

Повторный запрос к DBA_ADVISOR_LOG даст STATUS = 'COMPLETED'. ( Для серьезных запросов задание могло бы выполняться намного дольше).

Задание выполнило углубленный анализ с применением контрольных прогонов фрагментов плана и подготовило рекомендации. Запросим их:

SET LONG 10000 SET LONGCHUNKSIZE 1000 SET LINESIZE 200

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'my_sql_tuning_task' ) FROM dual;

Получим примерно .

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

Ту же информацию можно извлечь из словаря-справочника, например:

SELECT type, message FROM dba_advisor_findings WHERE owner = 'SYS' AND task_name = 'my_sql_tuning_task' ;

Применим созданый профиль к запросу, правда чуть в иной форме, нежели чем рекомендует оптимизатор, но по сути так же:

BEGIN DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => 'my_sql_tuning_task' , name => 'my_sql_profile'

); END; /

Уточнить свойства (и наличие) созданного профиля запроса можно по словарю-справочнику так:

SELECT category, type, status FROM dba_sql_profiles WHERE name = 'my_sql_profile' ;

Вернемся в исходный сеанс, снова выдадим запрос и понаблюдаем план:

EXIT /

Получим примерно такой результат:

Execution Plan ---------------------------------------------------------- Plan hash value: 351108634

-------------------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 9 | 1 (0)| 00:00:01 | |*4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------

Predicate Information (identified by operation id): ---------------------------------------------------

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note ----- - SQL profile "my_sql_profile" used for this statement

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

select ename, LOC,SAL, hiredate from emp, dept WHERE emp.deptno = dept.deptno ;

Получим снова:

Execution Plan ---------------------------------------------------------- Plan hash value: 351108634

-------------------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 9 | 1 (0)| 00:00:01 | |*4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------

Predicate Information (identified by operation id): ---------------------------------------------------

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note ----- - SQL profile "my_sql_profile" used for this statement

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

select ename, LOC,SAL, hiredate from emp, scott.dept WHERE emp.deptno = dept.deptno ;

Хотя профиль и приписан запросу, отдельный сеанс может отказаться от его использования. Профиль был создан для "категории" (поле CATEGORY.DBA_SQL_PROFILES) DEFAULT и будет применен только при условии, что запрос выдается с установленым в DEFAULT значением параметра СУБД SQLTUNE_CATEGORY. Это значение умолчательное. Заменим его на другое:

ALTER SESSION SET SQLTUNE_CATEGORY = test;

SELECT ename, loc, sal, hiredate FROM emp, dept WHERE emp.deptno = dept.deptno;

План для этого (и только !) сеанса снова станет прежним: Execution Plan ---------------------------------------------------------- Plan hash value: 615168685

--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 532 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 532 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 84 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------

Predicate Information (identified by operation id): ---------------------------------------------------

1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note ----- - dynamic sampling used for this statement

Хотя профиль и имеется, но в этом запросе не учитывается.

См. следующую статью - "".

Text1.shtml

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')

------------------------------------------------------------------------

GENERAL INFORMATION SECTION

------------------------------------------------------------------------

Tuning Task Name : my_sql_tuning_task

Tuning Task Owner : SYS

Scope : COMPREHENSIVE

Time Limit(seconds) : 60

Completion Status : COMPLETED

Started at : 03/14/2006 20:57:05

Completed at : 03/14/2006 20:57:05

Number of Statistic Findings : 1

Number of SQL Profile Findings : 1

-------------------------------------------------------------------------

Schema Name: SCOTT

SQL ID : 3dcfttkf1kwmn

SQL Text : SELECT ename, loc, sal, hiredate FROM emp, dept

WHERE emp.deptno = dept.deptno

-------------------------------------------------------------------------

FINDINGS SECTION (2 findings)

-------------------------------------------------------------------------

1- Statistics Finding

---------------------

Table "SCOTT"."DEPT" and its indices were not analyzed.

Recommendation

--------------

- Consider collecting optimizer statistics for this table and its indices.

execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>

'DEPT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Rationale

---------

The optimizer requires up-to-date statistics for the table and its indices

in order to select a good execution plan.

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')

-------------------------------------------------------------------------

2- SQL Profile Finding (see explain plans section below)

--------------------------------------------------------

A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 38.11%)

------------------------------------------

- Consider accepting the recommended SQL profile.

execute dbms_sqltune.accept_sql_profile(task_name =>

'my_sql_tuning_task', replace => TRUE);

------------------------------------------------------------------------

EXPLAIN PLANS SECTION

------------------------------------------------------------------------

1- Original With Adjusted Cost

------------------------------

Plan hash value: 615168685

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 364 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 14 | 364 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| DEPT | 4 | 36 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

2- Using SQL Profile

--------------------

Plan hash value: 351108634

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')

------------------------------------------------------------------------

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 9 | 1 (0)| 00:00:01 |

|*4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

------------------------------------------------------------------------

Углубленный анализ запросов и его возможности

В обычной работе оптимизатор затрат строит план обработки "на скорую руку": опираясь на статистику используемых в запросе таблиц и индексов, значения некоторых параметров СУБД и, возможно, имеющиеся в запросе подсказки. В версии 10 можно попросить оптимизатор обработать запрос в специальном настроечном режиме (Automated Tuning Optimizer), при котором оптимизатор способен выполнить следующие виды анализа:

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

Углубленный анализ запроса требует времени, в том числе за счет проверочных прогонов частей плана для получения более точных оценок затрат. Анализ без построения профиля требует меньше времени и называется ограниченным (limited). Анализ с построением профиля требует больше времени и называется полным (comprehensive).

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

Версия Oracle 10g: специалисты по настройке запросов больше не нужны

,
Преподаватель технологий Oracle,

Моя поэзия здесь больше не нужна,

Да и, пожалуй, сам я тоже здесь не нужен.

С. Есенин. Русь советская.

Печь затопит, все заготовит, закупит,

Яичко испечет да сам и облупит.

Попадья Балдой не нахвалится ...

А. С. Пушкин. Сказка о попе и о работнике его Балде.

В версии 10 Oracle появился

В версии 10 Oracle появился специальный режим работы оптимизатора затрат (cost optimizer), позволяющий провести углубленный анализ запроса и предложить для последующего выполнения более качественный, чем обычно, план. Применить такой анализ можно с помощью пакета DBMS_SQLTUNE, который позволяет получить от СУБД рекомендации по настройке конкретных запросов и даже получить сценарий внесения изменений. Рекомендации строятся на основе дополнительного, сверх обычного, изучения данных, проверочных прогонов частей плана и накопленных специалистами по настройке запросов знаний.
Пакетом можно пользоваться как вручную (программно), так и через графические средства OEM. Далее рассматривается пример программного выполнения углубленного анализа запросов.