Sql plus как подключиться к бд oracle

Обновлено: 04.07.2024

В посте рассматривается способ разблокировки и доступа к учебному и тестовому пользователю (схемы) HR в базе данных Oracle Database 18c Express Edition. Рассмотрены следующие вопросы:

Краткий обзор Multitenant архитектуры
Разблокировка пользователя HR

Multitenant
Flashback Table
Flashback Database
Oracle Partitioning
In-Memory Column Store и Aggregation
Advanced Analytics и Security
Online Index Rebuild
Online Table Redefinition
Query Results Cache и PL/SQL Function Result Cache
Oracle Advanced Compression
Materialized View Query Rewrite
Oracle Spatial and Graph
Bitmap Indexes

Разблокировка пользователя (схемы) HR

Предполагается, что есть успешно установленная Oracle Database 18c Express Edition. При необходимости, можно установить Oracle Database 18c Express Edition используя следующие материалы: установка Oracle Database 18c Express Edition на Linux и установка Oracle Database 18c Express Edition на Windows. Нижеописанные шаги будут работать с Oracle Database 18c Express Edition, установленной, как на операционную систему Linux, так и на Windows.

Вариант разблокировки с помощью SQL*Plus.

Шаг 1. Подключение к CDB

Выполняется подключение к CDB с помощью пользователя sys с ролью as sysdba:

Подключение успешно прошло к CDB. Далее проверяется имя и идентификатор CDB.

Результат запроса показывает, что CDB имеет имя XE и ее уникальный идентификатор = 0. По умолчанию, после установки Oracle Database 18c Express Edition есть одна PDB с именем XEPDB1. Следующий запрос покажет существующие PDB.

Проверяется наличие пользователя HR в CDB.

Запрос не вернул данные. Это означает, что пользователя HR нет в CDB. Далее необходимо подключиться к PDB и найти там HR.

Шаг 2. Подключение к PDB

Есть два способа подключиться к PDB с использованием SQL*Plus.

Способ 1. Находясь в CDB, подключиться к PDB используя команду alter session. В примере ниже происходит переключение из сеанса CDB к PDB с именем XEPDB1:

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

Запросы показывают характеристики существующей PDB (Шаг 1.).

Способ 2. Можно подключиться к PDB с консоли операционной системы, указав параметры подключения.

Ниже выполняется подключение к PDB под пользователем sys с указанием IP адреса сервера БД, порта и имени PDB (по умолчанию для созданной PDB (XEPDB1) используется порт 1539):

Подключение прошло успешно.

Для информации: Администраторы баз данных временами выполняют подключение к БД используя аутентификацию на уровне операционной системы с помощью команды sqlplus / as sysdba и без указания пароля. При запуске этой команды в среде с Multitenant архитектурой будет осуществлено подключение к CDB. Для того, чтобы напрямую подключиться к PDB минуя CDB, используется sqlplus / as sysdba и без указания пароля, также необходимо в переменную среду операционной системы добавить новый системный параметр ORACLE_PDB_SID и в его значении указать название PDB. Этот параметр для подключения к PDB без указания пароля могут осуществлять только пользователи sys и system. Остальные пользователи будут автоматически подключены к CDB, если не укажут параметры подключения к PDB. Ниже описываются шаги подключения к PDB для пользователя sys с применением параметра ORACLE_PDB_SID в переменной среде операционной системы. Это очень удобный способ для администраторов баз данных:

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

После успешного подключения к PDB c использованием одного из двух способов определяется наличие пользователя HR, а также его статус.

Запускается запрос поиска пользователя HR среди всех существующих пользователей в XEPDB1:

Получен результат, подтверждающий наличие пользователя HR в PDB.

При помощи запроса определяется имя, статус и дата блокировки пользователя HR:

Шаг 3. Разблокировка пользователя HR

После установки Oracle Database 18c Express Edition учетная запись HR заблокирована и пароль у нее просрочен (необходимо задать новый пароль) (см. предыдущий шаг – Шаг 2.). В этом случае, система позволяет сделать запросы к объектам HR (таблицам, представлениям, функциям и т.п.) от имени других пользователей при наличии соответствующих привилегий. Например, при выполнении запроса на определение количества строк в таблице EMPLOYEES пользователя HR под пользователем SYS система успешно выдаст следующий результат:

Для пользователя HR назначается новый пароль:

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

Необходимо заново подключиться к PDB под пользователем sys:

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

Операции назначения пароля и разблокировки пользователя HR прошли успешно. Проверяется статус пользователя:

Пользователь HR разблокирован и новый пароль активен. Это означает, что теперь можно подключиться к PDB с именем XEPDB1 под учебным тестовым пользователем HR и начать работу.

Шаг 4. Подключение к PDB с учетной записью HR.

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

На этом завершается определение наличия пользователя, назначение ему пароля и разблокировка HR в PDB Oracle Database 18c Express Edition, а также выполнение запроса к его объекту с помощью SQL*Plus.

Вариант разблокировки с помощью SQL Developer.

Шаг 1. Подключение к CDB

Для этого создается новое подключение в SQL Developer и указываются необходимые параметры подключения к CDB, такие как:

Name: XE_18c
Указывается имя соединения, которое позволяет однозначно идентифицировать CDB при подключении.

IP: 192.168.0.1
IP адрес сервера БД.

Port: 1539
Порт подключения к БД.

SID: XE
SID или имя CDB.

Username: sys
Указывается имя пользователя для подключения к БД.

Role: SYSDBA
Подключение к БД осуществляется пользователем sys. Данный пользователь может подключиться только с ролью SYSDBA.

Password:
Пароль пользователя sys, который был назначен во время установки базы данных.

После нажатия Connect произойдет успешное подключение к CDB с именем XE. Далее проверяется имя, идентификатор и версия CDB, а также выводятся существующие PDB.

Как и ожидалось, выведенные выше данные идентичны полученным с помощью SQL*Plus.

Далее проверяется наличие пользователя HR в CDB.

Запрос не вернул данные, это означает, что пользователя HR нет в CDB. Теперь необходимо подключиться к PDB и проверить наличие HR в PDB.

Шаг 2. Подключение к PDB

Создается новое подключение в SQL Developer и указываются необходимые параметры подключения к подключаемой базе данных XEPDB1, такие как:

Name: XEPDB1_18c
Указывается имя соединения, которое позволяет однозначно идентифицировать PDB при подключении.

IP: 192.168.0.1
IP адрес сервера БД.

Port: 1539
Порт подключения к БД.

SID: XEPDB1
SID или имя PDB.

Username: sys
Указывается имя пользователя для подключения к БД.

Password:
Пароль пользователя sys, который был назначен во время установки базы данных. Пользователи sys и system могут подключиться с одним и тем же паролем и к CDB и к PDB.

После нажатия Connect произойдет успешное подключение к подключаемой БД XEPDB1. Далее проверяется имя и идентификатор.

Результаты показывают, что было подключение к PDB с именем XEPDB1 и идентификатором 3. Определяется наличие пользователя HR в этой PDB. В иерархии дерева надо выбрать «Other Users» в соединении с именем XEPDB1_18c как показано на скриншоте:

В списке пользователей необходимо найти пользователя HR и нажать на правую кнопку. Из контекстного меню выбрать «Edit User». Откроется новое модальное окно «Edit User» как показано на скриншоте. Как видно на скриншоте учетная запись HR заблокирована (Account is Locked) и пароль у нее просрочен (Password Expired):

Шаг 3. Разблокировка пользователя HR:

В продолжение предыдущего шага необходимо:

Задать идентичный пароль в полях New Password (новый пароль) и Confirm Password (подтвердить пароль).
Снять галочку из пункта Password Expired (user must change next login).
Снять галочку из пункта Account is Locked для разблокировки пользователя.
Нажать Apply.

Пользователь HR разблокирован и ему назначен пароль. Это означает, что теперь можно подключиться к PDB с именем XEPDB1 под учебным тестовым пользователем HR и начать работу.

Шаг 4. Подключение к PDB с учетной записью HR.

Создается новое подключение в SQL Developer и указываются необходимые параметры подключения к подключаемой базе данных XEPDB1 с пользователем HR, такие как:

Name: XEPDB1_18c_hr
Указывается имя соединения, которое позволяет однозначно идентифицировать PDB при подключении с пользователем HR.

IP: 192.168.0.1
IP адрес сервера БД.

Port: 1539
Порт подключения к БД.

SID: XEPDB1
SID или имя PDB.

Username: HR
Указывается имя пользователя для подключения к БД.

Role: default
Подключение к БД осуществляется пользователем HR. Данный пользователь не может использовать роль SYSDBA.

Password:
Пароль, который был назначен пользователю HR на третьем шаге, то есть hr.

После нажатия Connect произойдет успешное подключение к PDB с именем XEPDB1 под пользователем HR. Выполняется запрос для определения количества строк в таблице EMPLOYEES:

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

а затем использовать это в sqlplus

Однако в моих обстоятельствах изменение локальных имен tnsname невозможно. Можно ли подключиться к удаленной базе данных, просто используя аргумент sqlplus, не меняя tnsnames? Что-то вроде

еще короче - sqlplus ИД пользователя / пароль @ база данных @GlennLong - но в вашей версии database все еще псевдоним TNS, который должен существовать в tnsnames.ora , так что это то же самое, что Луи пытался избежать?

Возможно, и это может зависеть от среды командной строки, которую вы используете, вам нужно заключить строку в кавычки, что-то вроде

Вы можете использовать Easy Connect для этого:

Чтобы включить easy connect на вашем компьютере, вам нужно добавить его NAMES.DIRECTORY_PATH в файл sqlnet.ora, например:

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

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

Создайте копию файла tnsnames.ora в каталоге, в который вы можете записать, измените файл соответствующим образом, затем установите переменную среды TNS_ADMIN в расположение этого каталога.

Этот подход работает с системой, в которой не установлена инфраструктура Oracle, кроме клиента sqlplus. Просто скопируйте tnsnames.ora с сервера базы данных и следуйте указаниям Фила.

В системе Unix / Linux вы можете использовать файлы конфигурации уровня пользователя для переопределения записей уровня системы.

Файлы конфигурации системного уровня можно найти в каталоге $TNS_ADMIN . Если переменная TNS_ADMIN не установлена, то они ищутся в каталоге $ORACLE_HOME/network/admin .

Файлы конфигурации уровня пользователя не заменяют файлы конфигурации уровня системы в целом (так как TNS_ADMIN каталог заменяет весь $ORACLE_HOME/network/admin каталог), но они добавляют или изменяют записи файлов конфигурации уровня системы. Если запись существует в файле конфигурации на уровне пользователя, то используется эта запись, если она не существует в файле конфигурации на уровне пользователя, то используется запись в файле конфигурации на уровне системы.

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

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

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

Запуск сеанса SQL*Plus

Интерфейс SQL*Plus представляет собой утилиту, которая чаще всего применяется для подключения и работы с базами данных Oracle. Он поставляется в составе как серверного программного обеспечения Oracle Database 11g, так и клиентского программного обеспечения Oracle Client, а также нового программного обеспечения Oracle Instant Client.

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

Настройка среды

Перед вызовом SQL*Plus потребуется сначала правильно настроить среду Oracle.В частности, необходимо установить значения для таких переменных среды, как ORACLE_SID, ORACLE_HOME и LD_LIBRARY_PATH. Помимо этого иногда нужно установить значения и для таких переменных среды, как NLS_LANG и ORA_NLS11.

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

В случае получения показной выше ошибки достаточно установить значение для переменной среды ORACLE_HOME:

Программное обеспечение SQL*Plus Instant Client

Для использования SQL*Plus инсталлировать полностью все серверное программное обеспечение Oracle Database вовсе не обязательно. Если нужно взаимодействовать через интерфейс SQL*Plus с базой данных, которая находится на другом сервере,вполне хватит и программного обеспечения SQL*Plus Instant Client. С помощью этого программного обеспечения к любой базе данных Oracle, функционирующей под управлением любой операционной системы, можно подключаться удаленным образом за счет указания ее имени и применения идентификатора сетевого подключения Oracle.Единственным требованием для подключения к удаленной базе данных подобным образом является указание имени удаленной базы данных в файле tnsnames.ora. Именно поэтому для SQL*Plus Instant Client требуется задавать переменную среды ORACLE_HOME.Существует также метод, который не требует применения на клиентском сервере файла tnsnames.ora. Называется он методом простого подключения (easy connect). Ниже приведен пример, показывающий, как с помощью идентификатора простого подключения установить от имени пользователя OE подключение к базе данных testdb, расположенной на сервере myserver:

В этом примере 1521 — это порт, используемый слушателем для получения запросов на установку подключения.

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

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

После указания базы данных, которая должна использоваться по умолчанию (в приведенном примере это orcl) в переменной среды ORACLE_SID, можно получать доступ к SQL*Plus из приглашения командной строки, просто вводя команду sqlplus безо имени пользователя и пароля. SQL*Plus предложит ввести имя пользователя и пароль. В случае предоставления имени пользователя вместе с командой (например: sqlplus salapati),SQL*Plus будет приглашать ввести только пароль. Администратор баз данных должен входить от имени одной из административных учетных записей.

На заметку! На серверах UNIX ввод должен обязательно выполняться в нижнем регистре. В Windows интерфейс не чувствителен к регистру символов. За исключением этой небольшой детали, во всем остальном командный интерфейс SQL*Plus работает одинаково и на платформе Windows, и на всех вариантах платформ UNIX и Linux.

Разумеется, вводить имя пользователя и пароль можно и непосредственно при вызове SQL*Plus, но тогда пароль будет виден другим при его вводе. Ниже приведен пример:

Приглашение SQL (SQL>) означает, что соединение с SQL*Plus инициировано, и можно начинать вводить команды и операторы SQL, PL/SQL и SQL*Plus.

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

Определенные операции, например запуск и завершение работы, разрешено выполнять только в случае подключения к SQL*Plus с привилегиями SYSDBA или SYSOPER. При наличии привилегий SYSDBA (или SYSOPER) подключаться к SQL*Plus можно следующим образом:

Конструкция AS позволяет устанавливать привилегированные подключения пользователям, которым были выданы системные привилегии SYSDBA или SYSOPER.

Если в базе данных была создана учетная запись аутентифицированного пользователя операционной системы (ранее называвшаяся OPS$имя; см. главу 12), устанавливать подключение можно и просто указанием символа косой черты (/), как показано ниже:

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

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

На заметку! Имя экземпляра, имя базы данных и имя службы могут как совпадать, так и отличаться.

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

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

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

Например, вот как подключиться с помощью этого метода к базе данных orcl:

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

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

Установка подключения с помощью команды CONNECT

В SQL*Plus поддерживается команда CONNECT, которая позволяет после входа в SQL*Plus выполнять подключение от имени другого пользователя. Кроме того, она позволяет после подключения к одной базе данных подключаться к другой базе данных.Ниже приведен пример использования команды CONNECT для выполнения подключения от имени другого пользователя:

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

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

Команду CONNECT можно использовать в SQL*Plus вместе с синтаксисом / AS SYSDBA и / AS SYSOPER, как показано ниже:

Запуск сеанса SQL*Plus без установки подключения к базе данных с помощью параметра /NOLOG

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

Ниже приведен пример использования параметра /NOLOG:

Подключение к SQL*Plus через графический интерфейс Windows

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

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

Ниже показан синтаксис команды подключения к SQL*Plus:

На заметку! В Oracle Database 11g команда SQLPLUS поддерживает новый аргумент -F, позволяющий SQL*Plus получать от базы данных RAC события FAN (Fast Application Notification — быстрое уведомление приложений).

Подключаться от имени пользователя с привилегиями SYSOPER, SYSDBA или SYSASM необходимо для выполнения привилегированных операций, вроде завершения работы и запуска базы данных или резервного копирования либо восстановления базы данных.Привилегия SYSAM является новой в Oracle Database 11g и предназначена для разделения обычных операций по администрированию баз данных и операций автоматического управления памятью (Automatic Storage Management — ASM).

Работа в SQL*Plus

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

В качестве символа продолжения можно использовать дефис (-), хотя при окончании первой строки применять символ продолжения вовсе не обязательно. В каждой строке SQL можно вводить любое количество символов или слов и затем просто нажимать клавишу <Enter> для продолжения на следующей строке. SQL*Plus будет автоматически добавлять перед каждой строкой ее номер.В некоторых случаях, однако, символ продолжения (-) оказывается полезным, как в следующем примере, где требуется ввести SQL-оператор SELECT 200 - 100 FROM dual:

В этом примере из-за перехода на вторую строку после дефиса (-), который еще так же является и знаком минус, утилита SQL*Plus автоматически интерпретировала его как символ продолжения и выдала ошибку, потому что оператор получился синтаксически некорректным (select 200 100 from dual). Избежать этой проблемы можно за счет использования в конце первой строки второго дефиса (знака минус) для выполнения роли символа продолжения:

В Oracle для выполнения определенных запросов необходимо использовать таблицу DUAL, поскольку в поддерживаемом Oracle синтаксисе SQL наличие конструкции FROM в операторе SELECT является обязательным (например, SELECT sysdate FROM dual;).В базах данных Microsoft SQL Server, с другой стороны, использовать таблицу DUAL не требуется, потому что в синтаксисе SQL Server допускается применение операторов SELECT без конструкции FROM.

Завершение сеанса SQL*Plus

Завершается сеанс SQL*Plus вводом команды EXIT, причем как в нижнем, так и в верхнем регистре. С помощью команды QUIT осуществляется выход в операционную систему (регистр символов тоже роли не играет).

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

SQL*Plus - программа для работы с СУБД Oracle посредством командной строки. Используя SQL*Plus можно выполнять команды SQL и PL/SQL в интерактивном режиме или используя сценарий.

Основное преимущество SLQ*Plus - доступность, т.к. инструмент доступен практически всегда, когда в системе установлен Oracle. При этом это достаточно мощный инструмент, используя который можно решать различные задачи на удаленных машинах.

К написанию этой статьи меня подтолкнула книга "Oracle SQL*Plus: The Definitive Guide, 2nd Edition", написанная Jonathan Gennick. Как обычно, появилось желание систематизировать информацию и представить ее в удобном виде.

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

Область возможного использования SQLplus при этом гораздо шире, например - построение отчетов, в том числе в формате HTML.

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

Выполнить несколько строк кода (не передавая отдельный файл со скриптом), unix:

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

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

Запрос может быть выполнен тремя способами:

точка с запятой в конце запроса

строка с слешем "/" после запроса

пустая строка после запроса (будет помещен в буфер, но не выполнен немедленно)

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

Пример PL/SQL блока:

Правила выполнения PL/SQL блоков:

Первое слово в PL/SQL блоке должно быть из списка: BEGIN, DECLARE, CREATE PROCEDURE,
CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE TYPE, CREATE TYPE BODY. Регистр не важен.
Блок может состоять из нескольких строк
Можно вставлять /* комментарии */, они также могут быть на несколько строк
Пустые строки не разрешены внутри блока

Сигнал к выполнению блока может быть подан двумя путями:

Строка, содержащая только слеш "/" после блока - выполнить сразу

Строка, содержащая точку "." после блока - поместить в буфер. Содержимое буфера может быть выведено командой LIST и выполнено, путем указания одиночного слеша "/" или команды RUN.

Одиночное выражение PL/SQL может быть выполнено, используя:

В случае ошибок, при выполнении процедуры, можно отобразить их выполнив команду:

Работа с переменными

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

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

Если была определена &&variable, и скрипт запущен повтороно в ходе той же сессии работы с SQLplus - будет использовано старое значение переменной. Чтобы этого избежать - можно запрашивать интерактивный ввод в скрипте принудительно, испольтзуя команду:

ACCEPT можно использовать для валидации:

Для ввода дат в определенном формате:

SQL*Plus поддерживает четыре типа переменных: CHAR, NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. При вводе с клавиатуры переменная будет типа CHAR.

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

Bind-переменные могут использоваться для передачи данных между PL/SQL и SQL блоками:

Присвоить bind-переменной значение &-переменной:

Вывести значение bind-переменной:

Присвоить &-переменной значение bind-переменной:

Получаем OUT-параметр процедуры в bind-переменную:

Условное выполнение в SQLplus:

Пример скрипта, принимающего несколько переменных на вход в формате c возможностью задать дефолтные значения:

Настройки выполнения скриптов

Действуют на протяжении сессии в SQLplus.

Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:

Oracle рекомендует следующий порядок действий для запуска БД: запуск Database Control, запуск listener-а, запуск БД. Запуск БД также является составным процессом. При запуске более сложного окружения, такого как cluster system или любого другого управляемого Enterprise Manager Grid Control могут быть дополнительные этапы, однако для Single Instance архитектуры этой последовательности вполне достаточно.

Запуск и подключение к Database Control

Для запуска Database Control необходимо запустить программу emctl, расположенную в папке $ORACLE_HOME/bin. Ниже перечислены команды для запуска, остановки и проверки состояния Database Control

emctl start dbconsole

emctl stop dbconsole

ecmtl status dbconsole

Для успешного выполнения этих команд должны быть установлены следующие системные переменные: PATH, ORACLE_HOME и ORACLE_SID. PATH используется операционной системой для поиска пути к программе emctl. ORACLE_HOME и ORACLE_SID используются для поиска командой emctl файлов конфигурации. Эти файлы расположены в трёх местах: папка ORACLE_HOME/sysman/config содержит общие настройки для всех экземпляров Database Control работающих с текущей домашней директорией Oracle. ORACLE_HOME/hostname_sid/sysman/config и ORACLE_HOME/oc4j/j2ee/ OC4J_DBConsole_ hostname_sid/config содержат дополнительные расширенные настройки для каждой БД(hostname – имя компьютера, sid – значение переменной ORACLE_SID).

На рисунке 3-3 показан результат выполнения команды запуска Database Control

Первая попытка запуска была неудачной так как не была установлена переменная ORACLE_SID. Без корректного значения этой переменной emctl не может найти необходимые файлы конфигурации (значение используется в названии папок). Команда проверки состояния ни что иное как запрос по адресу URL; доступность этого URL так же можно проверить в браузере

где hostname – это сетевое имя компьютера на котором запущен Database Control и port – это порт ответственный за входящие подключения. Если у вашего сервера несколько сетевых имён или несколько сетевых интерфейсов – можно использовть любой. Для определения порта можно использовать команду emctl либо посмотреть конфигурационный файл ORACLE_HOME/install/portlist.ini где указаны все порты настроенные OUI и DBCA. При незапущенном listener-е при подключении к Database Control вы увидите окно изображенное на рисунке 3-4.

Запуск listener-а БД

Listener – это процесс который следит за запросами к порту для подключения к базе данных. Запросы к БД (и весь остальной трафик после создания сессии) использует Oracle Net, закрытый протокол Oracle. Oracle Net – это прокотор который работает над любым низлежащим сетевым протоколом, обычно над TCP/IP. Управление listener-ом более детально расммотрим в главе 4, сейчас же рассмотрим как запустить listener. Это можно сделать двумя (в windows тремя) способами: используя программу lsnrctl, с помощью Database Control, запустить windows сервис.

Программа lsnrctl расположена в каталоге ORACLE_HOME/bin. Параметрами могут быть

lsnrctl start [listener name]

lsnrctl status [listener name]

Обратите внимание на первую строчку – там указаны сетевое имя и порт listener-а, а также на пятую снизу строку, которая обозначает что listener будет принимать подключения для сервиса ocp11g который создан для экземпляра ocp11g. Это критически важная информация для подключения к БД. Если БД была успешна создана с помощью DBCA значит listener настроен и запущен. Если нет вы увидите другой ответ команды lsnrctl status, тогда используйте команду lsnrctl для запуска или нажмите кнопку START LISTENER в окне Database Control показанном на рисунке 3-4.

Запуск SQL *Plus

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

Запуск и остановка БД

Если быть точным – нельзя запустить и остановить БД: только экземпляр может быть запущен и остановлен, а база данны может быть подключена, открыта, отключена и закрыта. Данные операции можно совершить с помощью SQL *Plus выполнив команды STARTUP и SHUTDOWN или используя Database Control. В Windows это можно сделать также с помощью управления сервисом созданным для экземпляра БД. Системный журнал содержит подробную информацию об этих операциях когда бы они не были вызваны. Запуск и остановка – очень важные операции, информация об их выполнении всегда записывается и они могут быть инициированы только пользователями с особым уровнем доступа.

Подключение с повышенными правами доступа

Обычный пользователь не может запустить или остановить БД – потому что он авторизуется используя словарь данных. Это логически невозможно поскольку в момент запуска словарь данных ещё не доступен. Таким образом для запуска необходимо подключаться к серверу используя механизм внешней авторизации: системная авторизация пользователя как члена группы Oracle, или авторизация с использованием файла паролей. Тип авторизации указывается при выполнении команды CONNECT. Ниже представлены различные комбинации команды CONNECT после подключения к серверу используя программу SQL *Plus с параметром /NOLOG

connect user/pwd[@connect_alias] as sysdba

connect user/pwd[@connect_alias] as sysoper

connect / as sysdba

connect / as sysoper

где user – имя пользователя, pwd – пароль, connect_alias – сетевой идентификатор (рассмотрим в главе 4). Первый пример использует авторизацию с помощью словаря данных, база данных должны быть открыта или команда вернёт ошибку. Любой пользователь после подключения к БД используя данный синтаксис не сможет выполнить команды запуска и остановки базы данных. Два следующих примеры указывают Oracle использовать авторизацию с помощью файла паролей. Последние команды используют авторизацию операционной системы: Oracle проверяет является ли текущий пользователь членом группы Oracle, и если проверка успешна – пользователь подключается к БД как SYSOPER или SYSDBA. Пользователь подключившийся к базе данных любым способом из последних четырёх может выполнить команды запуска и остановки БД вне зависимости от состояния базы данных – она может быть даже не создана на этом этапе.

Если Database Control обнаруживает запущенный listener – то он использует авторизацию через словарь данных или файл паролей (в зависимости от выбора пользователя – рисунок 3-6). Если же listener не запущен (рисунок 3-4) при нажатии на кнопку STARTUP Database Control запрашивает системные имя пользователя и пароль для подключения к серверу.

SYSOPER и SYSDBA

SYSOPER и SYSDBA – это уровни доступа с повышенными полномочиями. Они доступны только при системной авторизации или авторизации с помощью файла паролей. Уровень доступа SYSOPER может выполнять команды

ALTER DATABASE [MOUNT|OPEN|CLOSE|DISMOUNT]

ALTER [DATABASE|TABLESPACE][BEGIN|END] BACKUP

Уровень доступа SYSDBA также может выполнять эти команды, плюс возможность создавать БД, запускать неполное восстановление и давать полномочия SYSOPER и SYSDBA другим пользователям.

Вам может быть интерестно под каким пользователем вы подключаетесь к БД когда используется системная авторизация. Чтобы это выяснить, после подключения к базе данных выполните команду show user (эту команду можно вызвать набрав sho user – не стоит недооценивать сокращения, они могут ускорить время набора команд) – результат показан на рисунке 3-7.

Уровень доступа SYSDBA использует пользоватля SYS – суперпользователя в системе и владельца словаря данных. Уровень доступа SYSOPER подключается как пользователь PUBLIC. PUBLIC – не пользователь в нормальном смысле, это пользователь который используется для задач администрирования, но (по умолчанию) не может просматривать или изменять данные. Подключаться с данными уровнями доступа стоит только для выполнения задач, которые не могут быть выполнены обычными пользователями.

Запуск: NOMOUNT, MOUNT и OPEN

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

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

Когда база данных остановлена (SHUTDOWN) все файлы закрыты и экземпляр не существует. В отключенном состоянии (NOMOUNT) – экземпляр БД построен в памяти (SGA создана и фоновые процессы запущены согдасно файлу параметров), но база данных недоступна и может быть даже ещё не создана. В подключенном состоянии (MOUNT) экземпляр находит и читает файл контроля. В открытом состоянии (OPEN) все файлы найдены и открыты – т.е. база данных доступна для пользователей. Когды вы запускаете команду STARTUP – будут выполнены все шаги, однако команда может быть разбиты на этапы. Напирмер если файл контроля испорчен или копия недоступна – вы не сможете подключить базу данных. Однако вы можете запустить базу в неподключенном режиме (NOMOUNT) и восстановить файл контроля. Точно так же если у вас возникли проблемы с файлами данных или логовов, вы можете попробовать восстановить данные в MOUNT состоянии, перед тем как открывать БД.

Как же экземпляр находит файлы которые ему нужны на каждом из шагов? Начнём с NOMOUNT. Когда вы запускаете команду STARTUP, Oracle будет искать файл параметров в определённом порядке как отображено на рисунке 3-8.

Всего существует три пути и имени файла. На Unix подобных системах это

Во всех случаях – SID это имя экземпляра. Порядок поиска очень важен. Oracle будет использовать первый найденный файл вне зависимости от наличия остальных. Если ни одного файла не существует – экземпляр не будет запущен. В режиме NOMOUNT используются только файл параметров и системный журнал. Значения параметров из файла параметров используются для создания SGA в памяти и запуска фоновых процессов. В системный журнал записывается информация об этот процессе. Где находится системный журнал? Путь можно узнать посмотрев параметр BACKGROUND_DUMP_DEST в файле параметров или выполнив команду

sho parameter background_dump_dest

Если системный журнал существует во время выполнения команды STARTUP то новые данные будут добавляться, иначе будет созда новый файл. Если возникнут какие-либо проблемы во время выполнения команды – так же будут созданы файлы трассировки.

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

Как часть процесса подключения, все именя файлов данных и логов и пути к ним считываются из файла контроля, но Oracle просто запоминает эти значения, не пытаясь найти файлы. Поиск и чтение файлов происходит во время открытия базы данных (OPEN). Если какой-либо файл поврежден или отсутствует база данных останется в режиме MOUNT пока вы не исправите ошибки. Более того, все файлы должны быть синхронизированы перед тем как база данных будет открыта. Если последнее выключение было выполнено в определённом порядке, то все буферы из буфера кэша БД записаны на диск процессом DBWn и файлы синхронизированы, и Oracle будет знать при запуске что все подтверждённые транзакции сохранены в файлах данных и нет неподтвержденных транзакций ожидающих отмены. Если же последнее выключение было не запланированным (к примеру от потери питания или системной перезагрузке сервера без правильного выключения экземпляра) то Oracle должен синхроинизировать файлы данных и файлы логов (отменив неподтверждённые транзакции). Процесс который подключает и открывает БД (и синхронизирует данные) называется SMON. Только когда база данных успешно открыта будет возможно подключение пользователей. Процесс запуска графически представлен на рисунке 3-9.

Остановка процесс зеркальный запуску. Вначале закрывается БД (CLOSE), затем отключается (DISMOUNT) и далее останавливается экземпляр. Во время закрытия БД все сессии отключаются: текущие транзакции отменяются процессом PMON, подтверждённые транзакции записываются в файлы данных DBWn и файлы данных и логов закрываются. Во время отключения закрывается файл контроля. И экземпляр останавливается с освобождением памяти и остановкой фоновых процессов.

Выключение: NORMAL, TRANSACTIONAL, IMMEDIATE и ABORT

Существуют параметры которые используются с командой SHUTDOWN – вызов SHUTDOWN команды требует уровня доступа SYSDBA или SYSOPER

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

TRANSACTIONAL: новые подключения недоступны; существующие сессии которые не выполняют транзакции отключаются; сессии которые выполняют транзанкцию завершают транзакцию и отключаются. Когда все сессии будут отключены, база данных останавливается.

IMMEDIATE: новые подключения не разрешены. Все активные сессии отключаются. Все активные транзакции отменяются и база данных выключается.

ABORT: это эквивалент отключению питания. Экземпляр останавливается без записи чего либо на диск, закрытия файлов, отмены транзакций.

Параметр “abort” оставляет базу данных в рассинхронизированном состоянии: возможно что подтверждённые транзакции не записаны в файлы данных, так как на момент выключения они были сохранены в памяти и DBWn не записал изменения из буфера в файлы. Также может быть и такое, что неподтверждённые транзакции записаны в файлы данных. Это определение испорченной БД: она содержит некорректные данные. Эти повреждения должны быть восстановлены используя instance recovery. Таким образом можно протестировать что произойдёт если к примерну непредвиденно обесточить сервер в процессе работы БД.

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

alter database close;

alter database dismount;

Эти команды полная противоположность командам запуска. На практике SHUTDOWN это единственная команда которой пользуются DBA. Пошаговый процесс также недоступен из Database Control.

Читайте также: