Открыто о СУБД Oracle на русском : SQL*Plus - завершаем тему...

Выпуск 64

Уважаемые подписчики рассылки!

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

Команда 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.

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

Рассмотрим пример использования ряда опций команды 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 

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

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

Команда 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 успешно завершена. 

Команда PASSWORD

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

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

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

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

 
18:02:49 SQL> set time off 
SQL> connect system/manager 
Соединено. 
SQL> password scott 
Изменение пароля для scott 
Новый пароль: ***** 
Повторите новый пароль: ***** 
SQL> connect scott/tiger 
Соединено. 

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

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

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

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

1. Запуск экземпляра.
   
   Экземпляр - это совокупность фоновых процессов и областей памяти, необходимых для доступа к базе данных Oracle.
2. Монтирование базы данных.
   
   При монтировании база данных связывается с запущенным ранее экземпляром.
3. Открытие базы данных.
   
   Открытие базы данных делает ее доступной пользователям для обычных операций.

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

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

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

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

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

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

READ ONLY
  | READ WRITE [RECOVER]
  | RECOVER

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

Таблица 17. Опции команды STARTUP.

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

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

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

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

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

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

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

ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]

Назначение опций команды SHUTDOWN описано в табл. 18:

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

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

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

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

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

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

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

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

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

LIST | STOP | START | NEXT | ALL | <номер журнала>

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

Таблица 19. Опции команды ARCHIVE LOG.

Если место назначения для архива не указано явно в командной строке, используется параметр инициализации 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' 

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

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

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

Для выполнения команды 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; 

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

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

Упражнения

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

Упражнение 1

Напишите командный файл SQL*Plus, выдающий все записи указанной таблицы в файл с именем <имя таблицы>.unl в виде строк, поля которых разделены символом вертикальной черты (|). Заголовки столбцов не выдавать.

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

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

Файл такого вида подходит для загрузки в базы данных Informix :).

Упражнение 2

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

Упражнение 3

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

Упражнение 4

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

Упражнение 5

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

Copyleft (no c) 2001-2004 В. Кравчук, OpenXS Initiative, Cоставление, перевод, дополнения, упражнения и примеры

Прежние выпуски

Все вышедшие выпуски рассылки можно найти на сайте рассылки. Там же реализована возможность поиска материалов по ключевым словам (с помощью Google. Работает.)

Дальнейшие планы автора

Вот вам песня Умки на прощанье:

 
Райком закрыт - все ушли на фронт  
Адком открыт - добро пожаловать в ад  
Сияют аксельбанты, заседает бомонд  
Играют музыканты, инструменты блестят  
 
В своих стерильных перчатках  
входит главный герой  
Земля без звука и наркоза  
ложится под нож  
Сейчас ударит фонтаном ее черная кровь  
И съемочная группа  
толпится, подставляя стакан  
 
Райком закрыт - все ушли на фронт!  
 
Приходит ночь, все отходит ко сну  
Солдат не спит, солдат читает устав  
Устав гласит: "Солдат, не спи на посту"  
Ночная птица тихо свистит в кустах  
 
Райком закрыт - все ушли на фронт  
 
Забудь свое имя, забудь свое лицо  
Забудь все то, зачем тебя сделал Бог  
И как автомат отправляйся в военкомат  
Хватай автомат и топай куда пошлют  
 
Запомни только свой номер, запомни код  
Из детства помни одно - как смотрел салют  
Закрой свой рот и тупо шагай вперед  
И если тебя не убьют, то потом нальют  
 
Закрыты магазины, закрыты ларьки  
Закрыта звукозапись, закрыт видеопрокат  
Пираты не дремлют, зато солдаты не спят  
Райком закрыт - добро пожаловать в ад  
 
Райком закрыт - все ушли на фронт!  

Такие дела.

P.S. В марте рассылка, видимо, выходить не будет. По крайней мере, пока не будет сделано все то, что я уже себе наметил. Следите за сайтом.

В следующем выпуске

Пишите, что вам хотелось бы узнать о Oracle. Например, сюда.

С наилучшими пожеланиями,

  В.К.