Александр В.Хьюнольд
Следом за SCOTT/TIGER
(Following SCOTT/TIGER, by Alexander W. Hunold)
Источник: журнал Oracle Magazine, no.4, 2002 http://www.oracle.com/oramag/oracle/02-jul/o42schema.html
В этой статье подробно
рассматриваются новые демонстрационные схемы Oracle9i Database.
В большинстве примеров на SQL и PL/SQL, представленных в документации Oracle , учебных курсах, демонстрационных примерах и литературе третьих фирм, которые вы видели, используются, скорее всего, объекты пользователя scott(с паролем «tiger»). Схема scott названа в честь Брюса Скотта (Bruce Scott), одного из первых сотрудников Software Development Laboratories (компании, которая стала Oracle ). Тигром (tiger) звали кота Брюса Скотта.
В Oracle9i Database появилась новая коллекция демонстрационных схем. Эта статья описывает новые схемы и дает информацию, которая поможет собрать соответствующую схему для тестирования. А всем, кто регулярно тестирует SQL-предложения или скрипты, статья подскажет простой способ пересоздания демонстрационных схем.
История
Первоначально в Oracle была только схема scott. Для демонстрирования технологий в Oracle много лет использовалось несколько наборов таблиц, включая множество вариантов схемы scott и популярной схемы Summit Sporting Goods (Высококачественные спортивные товары).
Однако с конца 1999г. богатый выбор типов данных и сложных объектов, доступных в Oracle8i Database, стал намного опережать демонстрационные возможности простых объектов схемы scott. Поэтому для демонстрирования мощности Oracle8i, корпорация Oracle создала целый набор взаимосвязанных схем, каждая из которых имеет свой уровень сложности и назначение.
Описание схем
Процесс установки Oracle9i Database исполняет набор скриптов SQL*Plus и других файлов из директории $ORACLE_HOME/demo/schema. Эти скрипты добавляют демонстрационные схемы Oracle9i в созданную во время установки базу данных. Заметьте, что создание демонстрационных схем можно отложить, выбрав установку "Software Only" («Только программы»).
В набор входят двенадцать демонстрационных схем Oracle9i:
hr (Human Resources - Персонал)
oe (Order Entry - Заказы)
pm (Product Media – Медиа-товары)
sh (Sales History - История продаж)
qs (Queued Shipping - Продажи в
порядке очереди)
qs_adm (QS Administration -
Администрирование очередей продаж)
qs_cb (QS Customer Billing (CB) -
Регистрация поставщиков очереди продаж)
qs_cbadm (QS CB Administration -
Администрирование регистрации поставщиков)
qs_cs (QS Customer Service - Очереди
потребителей продаж)
qs_es (QS Eastern Shipping - Отгрузка
на восток в порядке очереди)
qs_os (QS Overseas Shipping - Отгрузка
за море в порядке очереди)
qs_ws (QS Western Shipping - Отгрузка
на запад в порядке очереди)
Установка демонстрационных схем
Независимо от момента создания демонстрационных схем, во время установки базы данных или нет, Database Configuration Assistant (DBCA) проходит через процесс создания или удаления баз данных, а также добавления новых компонентов, например interMedia или демонстрационных схем. Для поддержки секретности DBCA по умолчанию создает базу данных с набором учетных записей, которые заблокированы и имеют недокументированные пароли. По окончании процесса создания базы данных можно нажать на кнопку Password Management («Управление паролем»), чтобы разблокировать учетные записи и установить пароли.
Рис 1: Демонстрационные схемы hr и oe
Для того чтобы выполнить какой-нибудь запрос или предложение из примеров этой статьи, необходимо установить Oracle9i Database Enterprise Edition. Во время установки создайте базу данных "General Purpose " («Общего назначения») и не забудьте нажать кнопку Password Management, как только база данных будет создана. Чтобы установить пароль, который необходим для доступа к созданным схемам, разблокируйте учетные записи демонстрационной схемы, щелкнув мышью в графе Locked, и введите пароль в полях «пароль» и «подтверждение пароля», расположенных в таблице управления паролем. Для запуска примеров этой статьи необходимо присоединиться к серверу, на котором установлена база данных Oracle9i General Purpose. Установите переменную окружения ORACLE_SID в соответствии с документацией по ОС. Когда появится приглашение для соединения, введите тот же пароль, который был введен и подтвержден во время управления паролем демонстрационных схем.
Выбор описаний схем
Схема hr (Human Resources) – это схема, которая предоставляет поддержку большинства примеров, где использовались таблицы emp и dept схемы scott. hr – простая схема, в которой используются только скалярные типы данных и хранятся небольшие таблицы. Присоединившись пользователем hr, выполните следующий запрос, чтобы получить список таблиц схемы hr и их размеры:
SQL> SELECT table_name, num_rows "# of rows", 2 COUNT(*) "# of columns" 3 FROM user_tables NATURAL JOIN user_tab_columns 4 GROUP BY table_name, num_rows 5 ORDER BY 2;
|
Web-локатор Документация по демонстрационным схемам находится в otn.oracle.com/docs |
Схема oe (Order Entry) состоит из двух частей: самой схемы и ее объектно-реляционной части – подсхемы oc (Online Catalog). Структура схемы oe поддерживает основные упрощенные сценарии заказов и включает большой выбор типов данных, в том числе и новых, появившихся в Oracle9i. Схема oe содержит также синонимы таблиц схемы hr. В большинстве случаев можно присоединяться к схеме oe и использовать таблицы обеих схем без переключения соединений.
Схема oe содержит 10 таблиц и 27 объектных типов. Чтобы убедиться в этом и увидеть количество индексов, LOB-ов, триггеров и других объектов схемы, выполните следующий запрос, присоединившись пользователем oe:
SQL> SELECT object_type, COUNT(*) 2 FROM user_objects 3 GROUP BY object_type;
Одна из таблиц схемы oe, product_descriptions (описания товаров), содержит названия товаров и их описания на 30 языках. Если выполнить запрос к представлению products (товары) при различных значениях параметра NLS, то можно заметить, что описания товаров изменяются в соответствии со значением этого параметра. На рисунке 1 показаны основные таблицы и их столбцы, которые содержатся в схемах hr и oe. Имена столбцов, выделенные красным, обозначают первичные ключи.
Схема pm (Product Media) предназначена для управления содержимым. Таблица online_media схемы pm позволяет использовать Oracle interMedia, а в таблице print_media используются типы данных для больших объектов (large-object – LOB). Столбец ad_textdocs_ntab таблицы print_media представляет собой вложенную таблицу. Таблицы схемы pm содержат видео, аудио, изображения, документы и текстовые файлы для некоторых товаров схемы oe.
Схема sh (Sales History) – намного больше всех демонстрационных схем. Эта схема может использоваться для тестирования на больших объемах данных. В некоторых случаях в схеме sh используется секционирование, двоичные соединенные (bitmap join) индексы, внешние таблицы и аналитическая обработка в режиме реального времени (OLAP - class=bodycopy> Garamondonline analytical processing). Две существующие таблицы, sales и costs, содержат три четверти миллиона и миллион строк, соответственно. На рисунке 2 показаны таблицы схемы sh.
Логическая схема qs (Queued Shipping) состоит из группы схем. Все названия схем группы начинаются с "qs". Эти схемы управляют очередями сообщений и позволяют экспериментировать с возможностями Advanced Queuing.
Рис. 2: Схема sh
Обзор метаданных
Для ознакомления с новыми объектами и типами базы данных демонстрационных схем используйте Oracle Enterprise Manager (OEM). Модуль Schema Manager из OEM позволяет просмотреть список объектов демонстрационных схем, сгруппированный по типам объектов. Щелкнув два раза правой кнопкой мыши на объекте демонстрационной схемы, выберите один из следующих вариантов:
Dependencies (Зависимости).
Показывает, как объект используется в других объектах, и от какого
родительского объекта зависит.
Show Object DDL (Показать DDL
объекта). Генерирует из метаданных SQL-предложение для создания объекта.
Возможно, вы предпочитаете строковый режим просмотра метаданных. Запрос, показанный ниже, представляет собой быстрый способ использования этого режима для получения данных о демонстрационных схемах. Присоединитесь пользователем oe и выполните следующий запрос:
SQL> DESCRIBE customer_typ customer_typ is NOT FINAL Name Type____________ _________CUSTOMER_ID NUMBER(6)CUST_FIRST_NAME VARCHAR2(20)CUST_LAST_NAME VARCHAR2(20)CUST_ADDRESS CUST_ADDRESS_TYPPHONE_NUMBERS PHONE_LIST_TYPNLS_LANGUAGE VARCHAR2(3)NLS_TERRITORY VARCHAR2(30)CREDIT_LIMIT NUMBER(9,2)CUST_EMAIL VARCHAR2(30)CUST_ORDERS ORDER_LIST_TYP
Для получения более подробной информации можно увеличить глубину описания.
SQL> SET DESCRIBE DEPTH 2SQL> DESCRIBE customer_typSQL> SET DESCRIBE DEPTH 3SQL> DESCRIBE customer_typ
Использование объектов демонстрационной схемы
Предположим, вы решили проверить, как работают соединения типа natural, возможность которых появилась в Oracle9i Database. Схема hr хороша для реляционных запросов, но чтобы создать более интересный запрос обратимся к некоторым таблицам схемы oe.
Результат примерного запроса для соединения типа natural– это список адресов всех складов с товарами, заказ которых оплачен, а товар отгружен. Посмотрите на диаграмму схемы (Рис.1) для определения таблиц, необходимых для этого запроса:
oe.warehouses можно соединить с
таблицей hr.locations , чтобы получить адреса.
oe.product_information можно соединить
с oe.warehouses через oe.inventories. Это классическая взаимосвязь
«многие-ко-многим».
oe.orders со столбцом order_status и
oe.order_items со столбцом product_id формируют две последние связи в цепочке.
В соединение входит шесть таблиц, поэтому от одной таблицы к
другой требуется пять переходов. Вместо написания, по крайней мере, пяти
предикатов для оператора WHERE можно построить natural-соединение. Если учетные
записи еще не разблокированы, а пароли не введены во время установки, начните с
разблокировки и присоединения к необходимым схемам, подтверждая возможность
доступа к необходимой информации, как показано на Листинге 1. Замените пароль
8Wpo7 из листинга на свой собственный.
|
Подробнее На Oracle Technology Network (OTN) предоставлены наборы
примеров по Oracle9i (OBE - Oracle9 by Example), в которых
содержатся пошаговые инструкции по применению различных технологических
решений бизнес задач, основанных на новых демонстрационных схемах Oracle9i. otn.oracle.com/obe |
Из Листинга 1 видно, что доступ к требуемым объектам имеется, хотя они и расположены в схеме схеме hr. Как идентифицировать открытые заказы? Столбец order_status – числовой. Каждая из таблиц демонстрационной схемы и их столбцы содержат комментарии. Выберите комментарии к столбцу order_status, как показано ниже:
SELECT comments FROM user_col_comments WHERE column_name='ORDER_STATUS';COMMENTS___________________________________________0: Not fully entered, 1: Entered, 2: Canceled - bad credit,3: Canceled - by customer, 4: Shipped - whole order,5: Shipped - replacement items, 6: Shipped - backlog on items,7: Shipped - special delivery, 8: Shipped - billed,9: Shipped - payment plan, 10: Shipped - paid
Теперь, когда имеются идентифицирующие таблицы и список открытых заказов, выполните запрос-соединение типа natural из Листинга 2.
Переустановка демонстрационных схем
После выполнения в демонстрационных схемах команд data manipulation language (DML – манипулирование данными) и data definition language (DDL – манипулирование метаданными), необходимо иметь возможность приведения демонстрационных схем обратно в их исходное состояние путем их пересоздания. Заметьте, что в демонстрационных схемах не следует хранить важные данные, так как при пересоздании схем восстанавливаются только те данные, которые созданы по умолчанию. Для переустановки схем выполните следующую команду:
$ sqlplus /nologSQL> @?/demo/schema/mksample
Знак вопроса указывает на переход в $ORACLE_HOME. Это SQL-предложение работает как под UNIX, так и под Microsoft Windows. Перед тем, как начать переустановку демонстрационных схем, убедитесь, что никто к ним не присоединен. Пользователь или схема не могут быть удалены и, следовательно, пересозданы, если есть присоединенные к ним сессии. Чтобы проверить, есть ли присоединенные пользователи, выполните следующий запрос:
SELECT username FROM v$sessionWHERE (username in ('HR','OE','SH','PM') OR username like 'QS%');
Скрипт mksample.sql не блокирует пересозданные учетные записи демонстрационных схем; он запрашивает только пароль.
Заключение
С появлением все большего числа демонстрационных приложений, документов и учебных курсов, использующих стандартный набор схем, можно тратить меньше времени на попытку понять логическую модель и больше времени на создание своих собственных тестов, демонстрационных приложений и примеров. Многочисленные виды и размеры объектов демонстрационных схем позволяют легче найти точное соответствие для большинства возможных примеров. Лучше всего ваши примеры будут работать без дополнительной подготовки почти в любой базе данных Oracle9i.
Александр В. Хьюнольд (alexander.hunold@oracle.com) является управляющим учебным курсом по QA в Oracle
Corporation.
Листинг 1: Получение доступап и присоединение к схеме OE
$ sqlplus oe/8Wpo7 ERROR:ORA-28000: the account is lockedEnter user-name: system/ SQL> ALTER USER oe ACCOUNT UNLOCK IDENTIFIED BY 8Wpo7;User altered. SQL> SELECT * FROM tab;TNAME TABTYPE CLUSTERID_____________________ _______ __________ BOMBAY_INVENTORY VIEWCATEGORIES_TAB TABLECOUNTRIES SYNONYMCUSTOMERS TABLEDEPARTMENTS SYNONYMEMPLOYEES SYNONYMINVENTORIES TABLEJOBS SYNONYMJOB_HISTORY SYNONYMLOCATIONS SYNONYMOC_CORPORATE_CUSTOMERS VIEWOC_CUSTOMERS VIEWOC_INVENTORIES VIEWOC_ORDERS VIEWOC_PRODUCT_INFORMATION VIEWORDERS TABLEORDER_ITEMS TABLEPRODUCTS VIEWPRODUCT_DESCRIPTIONS TABLEPRODUCT_INFORMATION TABLEPRODUCT_PRICES VIEWPRODUCT_REF_LIST_NESTEDTAB TABLESUBCATEGORY_REF_LIST_NESTEDTAB TABLESYDNEY_INVENTORY VIEWTORONTO_INVENTORY VIEWWAREHOUSES TABLE 26 rows selected. Листинг 2: SELECT с использованием соединения из пяти таблиц типа NaturalSQL> SELECT 2 DISTINCT (street_address),(postal_code),(city) 3 FROM 4 locations NATURAL JOIN warehouses 5 NATURAL JOIN inventories 6 NATURAL JOIN products 7 NATURAL JOIN order_items 8 NATURAL JOIN orders 9 WHERE 10 order_status = 10 11 ; STREET_ADDRESS POSTAL_CODE CITY ___________________ ___________ _____ 12-98 Victoria Street 2901 Sydney1298 Vileparle (E) 490231 Bombay147 Spadina Ave M5V 2L7 Toronto2004 Charade Rd 98199 Seattle2007 Zagora St 50090 South Brunswick2011 Interiors Blvd 99236 South San Francisco2014 Jabberwocky Rd 26192 Southlake40-5-12 Laogianggen 190518 BeijingMariano Escobedo 9991 11932 Mexico City