Советы по работе с sqlplus

1. При работе с sqlplus последний выполненный оператор всегда сохраняется в текстовый файл afiedt.buf. Можно использовать этот файл для того чтобы выполнить последнюю команду или подредактировать ее. Для этого нужно просто ввести в командной строке sqlplus команду edit (или просто ed). При закрытии окна редактора измененная команда автоматически станет текущей в sqlplus.

2. Выполняя SQL-операторы, не завершайте их точкой с запятой (;), а указывайте символ косой черты (/) в строке, следующей за оператором.

3. Можно сначала написать sql-запрос в тектовом файле, а потом загрузить его в sqlplus и выполнить. Для этого сохраните файл с расширением .sql и загрузите командой get или @, например:
1) сохраняем оператор в файл my_select.sql
2) выполняем команду

SQL>@my_select

4. Если Вам нужно сохранить выходные данные скрипта в файл, используйте команду SPOOL.
Пример:

SQL>spool out.txt
SQL>select * from DUAL

Результат запишется в файл out.txt

5. Для более удобного чтения результатов запросов можно использовать форматирование при помощи команды column. Например, операторы

SQL>column ID a3
SQL>column FIRSTNAME a12
SQL>column LASTNAME a12
SQL>select ID, FIRSTNAME, LASTNAME from EMPLOYEE

выведут результат в виде 3 колонок, первая из которых будет иметь ширину 3 символа, а вторая и третья – по 12 символов.

Неправильная кодировка в sqlplus

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

Проблема возникает при несовпадении кодировки Oracle клиента с кодировкой, заданной для Dos-консоли. Соответственно, надо либо изменить первую, либо вторую.

Кодировка клиента Oracle задается в реестре
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG, либо переменной окружения NLS_LANG. Поэтому проще всего сменить кодировку клиента командой:

set NLS_LANG=RUSSIAN_RUSSIA.RU8PC866

Кодировка Dos-консоли меняется командой chcp:

C:\>chcp 1251
Текущая кодовая страница: 1251

Замечание: при смене кодовой страницы для DOS-консоли на 1251, следует убедиться что для неё установлены шрифты Lucida Console.

Полезные SQL-запросы

-- Получение данных о версии БД Oracle
SELECT * FROM V$VERSION;

-- Получение текущей даты и времени на сервере
SELECT TO_CHAR(sysdate,'dd/mm/yyyy hh24:mi:ss') FROM DUAL;

-- Получение имён всех таблиц в БД
SELECT TABLE_NAME FROM SYS.ALL_TABLES;

-- Получение имён пользователей в базе данных
SELECT USERNAME FROM USERS;

-- Получения всех объектов пользователя (таблиц, представлений, последовательностей, индексов и т.д.)
SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='...';

-- Получение количества записей в таблице
SELECT T.NR FROM (SELECT ROWNUM R, NUM_ROWS NR, TABLE_NAME TN FROM SYS.ALL_TABLES) T WHERE TN='...';

-- Получение имён всех колонок в заданной таблице
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='...';

-- Получение информации о типе заданной колонки в заданной таблице
SELECT DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME='...' AND COLUMN_NAME='...';

Кто такой Том Кайт?

Томac Кайт (Tom Kyte) - самый авторитетный гуру по СУБД Оракл, вице-президент Oracle Corporation и архитектор СУБД Oracle. Том работает с базами данных Oracle еще с 1987 года, а в 1993 году Том работает непосредственно в корпорации.

Любой ораклоид знает Тома как автора популярных книг, в том числе Oracle for professionals, Expert One on One Oracle, Effective Oracle by Design и других, а также популярного сайта AskTom (asktom.oracle.com), где Том отвечает на многочисленные вопросы по Ораклу.

Кстати, у тома есть свой персональный блог на блогспоте - tkyte.blogspot.com.

Виртуальные машины с Oracle 9i, 10g, 11g

Если Вы хотите попробовать что-то сделать в Oracle, но не хотите заниматься установкой и настройкой СУБД, то в сети есть ресурсы, где можно скачать готовые виртуальные машины с Oracle, например
1. Oracle VM Templates
2. Образы с Oracle Database 9i, 10g, 11g, Oracle Enterprise Linux и образы для построения Oracle EE DB 11g RAC

Новый поиск по сайтам о СУБД Oracle

Обратите внимание на поисковую форму от Google в правом верхнем углу блога - с помощью нее теперь Вы можете искать необходимую информацию по ведущим ресурсам сети, посвященным СУБД Оракл, в том числе asktom.oracle.com, otn.oracle.com, forums.oracle.com, а также российским сайтам с полезной информацией о базе данных №1.

Описание некоторых распространенных сообщений об ошибках Oracle

ORA-00001 : unique constraint violated - Вы пытаетесь выполнить вставку или изменение поля, значение которого будет нарушать ограничение уникальности поля.
ORA-01001 : invalid cursor - Вы пытаетесь использовать несуществующий курсор. Вероятная причина – курсор не был открыт, или уже закрыт.
ORA-01012 : not logged on - Вы не авторизованы, но пытаетесь выполнить запрос. Попробуйте залогиниться и повторить запрос.
ORA-01017 : invalid username/password; logon denied - Неверная комбинация логин/пароль.
ORA-01476 : divisor is equal to zero - Вы пытаетесь выполнить деление на ноль.
ORA-01000 : maximum open cursors exceeded - количество открытых курсоров превысило значение параметра OPEN_CURSORS базы данных. Уменьшите количество используемых курсоров в БД в Вашей программе. Если это не помогло, затормозите сервис БД, увеличьте значение параметра OPEN_CURSORS в init.ora и перезапустите сервис БД.
ORA-06511 : PL/SQL: cursor already open - Вы пытаетесь открыть уже открытый курсор
ORA-12545: Connect failed because target host or object does not exist – что-то не то с хостом или ip-адресом, с которым вы пытаетесь соединиться. Проверьте существование хоста командами tnsping в unix и ping в windows.
ORA-12541 : TNS:no listener – Убедитесь, что на сервере запущен прослушивающий процесс (листенер). Если нет, то используйте команду lsnrctl, либо запустите сервис в службах Windows. Если процесс запущен, проверьте правильность настроек файла listener.ora.
ORA-12500 : TNS:listener failed to start a dedicated server process – вероятно, сервис базы данных не запущен. Для Windows проверьте что сервис запущен, обычно сервис имеет имя OracleService, где SID – название базы данных. Для Unix убедитесь, что запущен процесс smon при помощи команды ps: ps -ef | grep ora_smon
ORA-12154 : TNS:could not resolve service name – Вы пытаетесь выполнить подключение к БД, которая неизвестна Вашему клиенту. Проверьте существование и настройки файла tnsnames.ora.
ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor – Во-первых, проверьте что запущен сервис базы данных. Ошибка также может возникать, если сервис БД и листенер еще стартуют, в этом случае подождите минуту-две. Если сервисы запущены, то причина ошибки – сервисы БД и листенер не настроены друг на друга. Проверьте настройки файлов tnsnames.ora и listener.ora.
ORA-12560 : TNS:protocol adapter error – вероятно, не запущен сервис базы данных. Запустите сервис в службах Windows или выполните команду startup в sqlplus.
ORA-20001 : A user specified error message – ошибки с кодами 20000-20999 отданы на откуп разработчикам приложений. Разработчики могут возбуждать такие ошибки в своих приложениях функцией raise_application_error. Обратитесь к разработчикам приложения за более подробной информацией.

Роли и полномочия (привилегии) в Oracle

После аутентификации пользователя в базе данных, ему разрешается выполнять в ней действия над данными. Но какие действия? Над какими данными? Ответы на
эти вопросы зависят от полномочий, предоставленных пользователю.
Не имея никаких полномочий, пользователь не может выполнять никакие действия с БД, даже установить соединение с ней. Поэтому для работы, пользователь как
минимум должен иметь право на подключение к БД. С другой стороны, большинству пользователей не требуется создавать или удалять таблицы БД, поэтому
привилегии на создание и удаление таблиц они не имеют.
Полномочия могут назначаться как отдельным пользователям, так и ролям. Роль – это именованный набор полномочий. Использование ролей существенно облегчает
управление привилегиями. Вместо того, чтобы предоставлять десятки полномочий каждому пользователю, можно создать несколько типичных ролей (например -
пользователь, администратор, …), наделить их необходимыми полномочиями и назначить пользователям их роли. В дальнейшем, будет гораздо легче лишить
пользователя роли, нежели отобрать у него список нежелательных полномочий.
И полномочия, и роли предоставляются пользователям оператором GRANT, а отбираются соответственно - REVOKE.

GRANT INSERT, UPDATE, DELETE, SELECT ON ZVEZDA.MY_TABLE TO SCOTT;
- Даст пользователю SCOTT полномочия на вставку, изменение, удаление и выборку данных из таблицы MY_TABLE схемы ZVEZDA.

REVOKE DBA FROM SCOTT;
- Лишит пользователя SCOTT полномочий роли DBA.

Создаются, изменяются и удаляются роли стандартными операторами DDL – CREATE, ALTER и DELETE.

CREATE ROLE ZVEZDA_MANAGER IDENTIFIED BY xyz123;
- Создаст защищенную паролем «xyz123» роль ZVEZDA_MANAGER;

GRANT SELECT ON ZVEZDA.CUSTOMERS TO ZVEZDA_MANAGER;
- Предоставит полномочия на выборку данных из таблицы ZVEZDA.CUSTOMERS роли ZVEZDA_MANAGER;

GRANT ZVEZDA_MANAGER TO SCOTT;
- Предоставит полномочия роли ZVEZDA_MANAGER пользователю SCOTT.