Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
Глава 14 Системный каталог (словарь данных) 14.1. Что такое системный каталог Для того чтобы эффективно управлять данными, СУБД должна отслеживать большое количество информации, определяющей структуру базы данных. В реляционной базе данных эта информация обычно хранится в системном каталоге — совокупности системных таблиц, используемых СУБД для внут- ренних целей. В файлах, хранящихся в системном каталоге, содержатся опи- сания таблиц, представлений, столбцов, привилегий и других структурных элементов базы данных. Несмотря на то, что словарь данных предназначен главным образом для внутреннего применения, пользователи базы данных могут получить доступ к системным таблицам с помощью стандартных запросов SQL. Таким обра- зом, реляционная база данных содержит описание своей структуры и пользо- ватель может получить его, выполняя запросы к системным таблицам. Это используется в таких клиентских приложениях общего назначения, как модули формирования запросов и программы генерации отчетов, где для уп- рощения доступа к базе данных пользователям предоставляется на выбор список таблиц и столбцов. Таблицы системного каталога создаются автоматически при создании базы данных. Обычно они объединяются под специальным ''системным идентифи- катором пользователя'' с таким именем, как SYSTEM , SYS и т. п. При обработке инструкций SQL СУБД постоянно обращается к данным сис- темного каталога. Например, чтобы обработать запрос на получение данных из нескольких таблиц, СУБД должна: проверить, существуют ли указанные таблицы; убедиться, что пользователь имеет разрешение на доступ к ним; проверить, существуют ли столбцы, на которые делаются ссылки в запросе; Глава 14. Системный каталог (словарь данных) 257 установить, к каким таблицам относятся неполные имена столбцов; определить тип данных каждого столбца. Так как информация о структуре базы данных хранится в системных табли- цах, СУБД может использовать свои собственные методы и алгоритмы, что- бы быстро и эффективно извлекать информацию, необходимую для выпол- нения этих задач (см. главу 15). Если бы системные таблицы служили только для удовлетворения внутренних потребностей СУБД, то для пользователей базы данных они не представляли бы никакого интереса. Однако, как уже отмечалось, системные таблицы (или созданные на их основе представления), как правило, доступны и для пользо- вателей. Запросы к системным каталогам разрешены почти во всех СУБД, если их не ограничил администратор базы данных с целью обеспечения безопасности базы данных. С помощью запросов к словарю данных можно получить информацию о структуре базы данных, даже если вы никогда раньше с ней не работали. Пользователи могут только извлекать информацию из системного каталога. СУБД запрещает пользователям модифицировать системные таблицы непо- средственно, так как это может нарушить целостность базы данных. СУБД сама вставляет, удаляет и обновляет строки системных таблиц во время мо- дификации структуры базы данных. Изменения в системных таблицах про- исходят также в качестве побочного результата выполнения таких инструк- ций DDL, как CREATE , ALTER , DROP , COMMIT и REVOKE . В некоторых СУБД даже инструкции DML, например INSERT и DELETE , могут модифицировать содер- жимое системных таблиц, отслеживающих количество записей в таблицах. Для определенности будем далее описывать системный каталог СУБД Oracle, который называется словарем данных. 14.2. Словарь данных Oracle Каждая таблица словаря данных содержит информацию об отдельном струк- турном элементе базы данных. В состав почти всех коммерческих реляцион- ных СУБД входят, с небольшими различиями, системные таблицы, каждая из которых описывает один из следующих пяти объектов: таблицы —описывается каждая таблица базы данных: указывается ее имя, владелец, число содержащихся в ней столбцов, их размер и т. д.; столбцы —описывается каждый столбец базы данных, приводится имя столбца, имя таблицы, которой он принадлежит, тип данных столбца, его размер, разрешены ли значения NULL и т. д.; Часть V. Язык SQL. Создание базы данных 258 пользователи —описывается каждый зарегистрированный пользователь базы данных: указываются имя пользователя, его пароль в зашифрован- ном виде и другие данные; представления —описывается каждое представление, имеющееся в базе данных: указываются его имя, имя владельца, запрос, являющийся опре- делением представления, и т. д.; привилегии —описывается каждый набор привилегий, предоставленных в базе данных: приводятся имена тех, кто предоставил привилегии, и тех, кому они предоставлены, указываются сами привилегии, объект, на кото- рые онираспространяются, и т. д. 14.2.1. Структура словаря данных В состав словаря данных базы данных входят базовые таблицы и доступные пользователю представления. Основу словаря данных составляет совокупность базовых таблиц, хранящих информацию о базе данных. Эти таблицы читаются и пишутся на системном уровне; они редко используются непосредственно пользователями. Словарь данных содержит доступные пользователю представления, которые суммируют и отображают в удобном формате информацию из базовых таб- лиц словаря. Эти представления декодируют информацию базовых таблиц, представляя ее в полезном виде, таком как имена пользователей или таблиц, и используют соединения и фразы WHERE , чтобы упростить информацию. Большинство пользователей имеют доступ к этим представлениям вместо базовых таблиц словаря. Словарь данных базы данных Oracle имеет два основных применения: Oracle обращается к словарю данных каждый раз, когда выдается предло- жение DDL; каждый пользователь Oracle может использовать словарь данных как только читаемый справочник по базе данных. Словарь данных всегда доступен при открытой базе данных. Он размещается в табличном пространстве SYSTEM , которое всегда находится в состоянии on- line, когда база данных открыта. Словарь данных состоит из нескольких наборов представлений. Во многих случаях такой набор состоит из трех представлений, содержащих аналогич- ную информацию и отличающихся друг от друга своими префиксами: USER — представление для пользователя; Глава 14. Системный каталог (словарь данных) 259 ALL — расширенное представление для пользователя; DBA — представление администратора. Столбцы в каждом представлении набора идентичны, но имеются исключе- ния. В представлениях с префиксом USER обычно нет столбца с именем OWNER (владелец); в представлениях USER под владельцем подразумевается пользо- ватель, выдавший запрос. Некоторые представления DBA имеют дополни- тельные столбцы, которые содержат информацию, полезную для АБД. Представления с префиксом USER : отражают окружение пользователя в базе данных, включая информацию о созданных им объектах, предоставленных им грантах и т. д.; выдают только строки, имеющие отношение к пользователю; имеют столбцы, идентичные с другими представлениями, с тем исключе- нием, что столбец OWNER подразумевается (текущий пользователь); возвращают подмножество информации, предоставляемой представле- ниями ALL ; могут иметь сокращенные общие синонимы для удобства. Представления с префиксом ALL отражают общее представление о базе данных со стороны пользователя. Эти представления возвращают информа- цию об объектах, к которым пользователь имеет доступ через общие или яв- ные гранты, помимо тех объектов, которыми владеет этот пользователь. Представления с префиксом DBA показывают общее представление о базе данных, и предназначены для администраторов базы данных. Во время своей работы Oracle поддерживает набор "виртуальных" таблиц, в которых регистрируется текущая информация о базе данных. Эти таблицы называются динамическими таблицами производительности. Так как дина- мические таблицы производительности не являются истинными таблицами, большинство пользователей не должно обращаться к ним. Динамические таблицы производительности принадлежат схеме SYS , а их имена начинаются с V_$ . По этим таблицам создаются представления, а для представлений соз- даются синонимы, имена которых начинаются с V$. 14.2.2. Краткое содержимое словаря данных Словарь данных Oracle 10g включает более 1000 статических представлений и около 500 — динамических. С ними можно познакомиться в документации, приведенной в приложении А. Здесь же мы покажем наиболее часто исполь- зуемые представления (табл. 14.1) и несколько примеров работы с ними. Часть V. Язык SQL. Создание базы данных 260 В табл. 14.1 после некоторых из имен представлений в скобках расположен перечень букв: (a,u) , (d,u) или (a,d,u) . Это означает, что такое имя являет- ся основой для имен двух или трех словарей, получаемых за счет добавки префикса ALL_ , DBA_ или USER_ . Например, TABLES(a,d,u) — основа для имен словарей: ALL_TABLES , DBA_TABLES и USER_TABLES . Кроме того, некоторые представления имеют синоним (столбец "Синоним"), заменяющий соответст- вующее представление с префиксом USER_ (например, CAT вместо USER_CATALOG ). Таблица 14.1. Ряд наиболее используемых представлений словаря данных Представление Синоним Описание Таблицы, представления, синонимы, последовательности ALL_TABLES(a,d,u) Описывает все объектные и реляционные таблицы CATALOG(a,d,u) CAT Информация обо всех объ- ектах базы данных COL Список столбцов в табли- цах пользователя COL_COMMENTS(a,d,u) Комментарии для столбцов таблиц и представлений CONSTRAINTS(a,d,u) Информация об ограниче- ниях ссылочной целостно- сти в БД CONS_COLUMNS(a,d,u) Информация о столбцах, участвующих в ограничени- ях ссылочной целостности SEQUENCES(a,d,u) SEQ Информация о последова- тельностях БД SYNONYMS(a,d,u) SYN Информация о синонимах БД TAB Таблицы пользователя TAB_COLUMNS(a,d,u) COLS Информация о столбцах таблиц и представлений БД TAB_COMMENTS(a,d,u) Комментарии для таблиц и представлений БД TABLES(a,d,u) TABS Информация о таблицах БД VIEWS(a,d,u) Информация о представле- ниях БД Глава 14. Системный каталог (словарь данных) 261 Таблица 14.1 ( продолжение) Представление Синоним Описание Объекты ARGUMENTS (a,u) Список всех параметров процедур и функций ERRORS(a,d,u) Информация об ошибках компиляции, обнаруженных в БД для процедур, функ- ций, спецификаций пакета и тел пакета DEPENDENCIES(a,d,u) Информация о зависимо- стях объекта в БД OBJECTS(a,d,u) OBJ Информация об объектах базы данных OBJECT_SIZE(d,u) Информация о размерах процедур, функций, специ- фикаций пакетов и тел па- кетов в БД PUBLIC_DEPENDENCY Информация о зависимо- стях объекта SOURCE(a,d,u) Исходный код процедур, функций, спецификаций пакетов и тел пакетов в БД TRIGGERS(a,d,u) Информация о триггерах БД Привилегии COL_PRIVS(a,d,u) Полномочия для столбцов DBA_PROFILES Все профили ограничений ресурсов в БД DBA_ROLES Информация о ролях в БД ROLE_PRIVS(d,u) Роли, назначенные пользо- вателю ROLE_ROLE_PRIVS Роли, назначенные другим ролям ROLE_SYS_PRIVS Системные полномочия, предоставляемые роли Часть V. Язык SQL. Создание базы данных 262 Таблица 14.1 ( продолжение) Представление Синоним Описание ROLE_TAB_PRIVS Полномочия на объект, предоставляемые роли SESSION_PRIVS Полномочия, предостав- ленные сеансу SESSION_ROLES Информация о доступных для сеанса ролях SYS_PRIVS(d,u) Предоставленные пользо- вателю системные полно- мочия TAB_PRIVS(a,d,u) Информация о заданных на объекты полномочиях TABLE_PRIVILEGES Информация о предостав- ленных на объекты полно- мочиях USERS(a,d,u) Информация о пользова- телях БД USER_RESOURCE_LIMITS Информация о лимитах ресурсов для текущего пользователя Индексы INDEXES(a,d,u) IND Информация об индексах БД IND_COLUMNS(a,d,u) Информация об индексах, соответствующих индексам таблицы Табличные пространства, кластеры, экстенты, файлы CLUSTERS(a,d,u) CLU Информация о всех кла- стерах базы данных CLUSTER_HASH_EXPRESSIONS(a,d,u) Список хеш - функций всех хешированных кластеров в БД CLU_COLUMNS(d,u) Отношение столбцов таб- лицы к ключам кластера Глава 14. Системный каталог (словарь данных) 263 Таблица 14.1 ( окончание) Представление Синоним Описание DBA_DATA_FILES Информация о файлах данных EXTENTS(d,u) Информация об экстентах объектов в БД FREE_SPACE(d,u) Информация о свободных экстентах в табличной об- ласти БД SEGMENTS(d,u) Информация о сегментах БД TABLESPACES(d,u) Информация о табличных областях БД Словарь DICTIONARY DICT Информация о таблицах и представлениях словаря данных DICT_COLUMNS Информация о столбцах словаря данных 14.2.3. Примеры использования словаря данных Все результаты примеров этого раздела показаны для текущего пользователя (схемы) "COOK". Пример 14.1. Получить перечень всех объектов текущей схемы, используя для этого представление USER_OBJECTS словаря данных. Для этого создадим запрос SELECT object_name, object_type,created FROM user_objects; включив в него следующий набор столбцов: имя объекта, тип объекта и дата его создания. Результат запроса имеет вид: OBJECT_NAME OBJECT_TYPE CREATED ---------------------------- ------------------ ----------- БЛЮДА TABLE 20.09.2007 Блюдо не уникально ! INDEX 20.09.2007 ВИДЫ_БЛЮД TABLE 20.09.2007 ВЫБОР TABLE 04.10.2007 Часть V. Язык SQL. Создание базы данных 264 Вид блюда не уникален ! INDEX 20.09.2007 Выбор не уникален ! INDEX 04.10.2007 Данные строки не уникальны ! INDEX 04.10.2007 МЕНЮ TABLE 04.10.2007 НАЛИЧИЕ VIEW 06.11.2007 ПОСТАВКИ TABLE 20.09.2007 ПОСТАВЩИКИ TABLE 20.09.2007 ПРОДУКТЫ TABLE 20.09.2007 Повтор кода блюда ! INDEX 20.09.2007 Повтор кода вида блюда ! INDEX 20.09.2007 Повтор кода поставщика ! INDEX 20.09.2007 Повтор кода продукта ! INDEX 20.09.2007 Повтор кода рецепта ! INDEX 08.10.2007 Повтор номера трапезы ! INDEX 20.09.2007 Поставка не уникальна ! INDEX 20.09.2007 Поставщик не уникален ! INDEX 20.09.2007 Продукт не уникален ! INDEX 20.09.2007 РЕЦЕПТЫ TABLE 08.10.2007 СОСТАВ TABLE 20.09.2007 Состав не уникален ! INDEX 20.09.2007 Строка меню не уникальна ! INDEX 04.10.2007 ТРАПЕЗЫ TABLE 20.09.2007 Трапеза не уникальна ! INDEX 20.09.2007 Пример 14.2. Получить перечень таблиц текущей схемы с указанием количе- ства столбцов в каждой из них. Рассмотрим, не подойдет ли для этой цели представление USER_TAB_COLUMNS Если, находясь в схеме COOK , воспользоваться запросом SELECT * FROM USER_TAB_COLUMNS; то получим строки, приведенные далее (здесь сохранены данные только из пяти столбцов, которые, с нашей точки зрения, могут подойти для решения поставленной задачи и задачи из следующего примера). TABLE_NAME COLUMN_NAME DATA_TYPE ... DATA_LENGTH DATA_PRECISION ... ---------- -------------- --------- ... ----------- -------------- ... БЛЮДА КОД_БЛЮДА NUMBER 22 2 БЛЮДА БЛЮДО VARCHAR2 16 БЛЮДА КОД_ВИДА NUMBER 22 1 БЛЮДА ОСНОВА VARCHAR2 6 Глава 14. Системный каталог (словарь данных) 265 БЛЮДА ВЫХОД NUMBER 22 4 БЛЮДА ТРУД NUMBER 22 3 ВИДЫ_БЛЮД КОД_ВИДА NUMBER 22 1 ВИДЫ_БЛЮД ВИД VARCHAR2 7 ВЫБОР МЕСТО NUMBER 22 2 ВЫБОР СТРОКА NUMBER 22 9 МЕНЮ СТРОКА NUMBER 22 9 МЕНЮ КОД_ТРАПЕЗЫ NUMBER 22 1 МЕНЮ КОД_БЛЮДА NUMBER 22 2 МЕНЮ ДАТА DATE 7 НАЛИЧИЕ КОД_ПРОДУКТА NUMBER 22 2 НАЛИЧИЕ К_ВО NUMBER 22 НАЛИЧИЕ СТОИМОСТЬ NUMBER 22 ПОСТАВКИ КОД_ПОСТАВЩИКА NUMBER 22 2 ПОСТАВКИ КОД_ПРОДУКТА NUMBER 22 2 ПОСТАВКИ ЦЕНА NUMBER 22 5 ПОСТАВКИ К_ВО NUMBER 22 4 ПОСТАВКИ ДАТА DATE 7 ПОСТАВЩИКИ КОД_ПОСТАВЩИКА NUMBER 22 2 ПОСТАВЩИКИ НАЗВАНИЕ VARCHAR2 8 ПОСТАВЩИКИ СТАТУС VARCHAR2 10 ПОСТАВЩИКИ ГОРОД VARCHAR2 9 ПОСТАВЩИКИ АДРЕС VARCHAR2 14 ПОСТАВЩИКИ ТЕЛЕФОН VARCHAR2 7 ПРОДУКТЫ КОД_ПРОДУКТА NUMBER 22 2 ПРОДУКТЫ ПРОДУКТ VARCHAR2 8 ПРОДУКТЫ БЕЛКИ NUMBER 22 4 ПРОДУКТЫ ЖИРЫ NUMBER 22 4 ПРОДУКТЫ УГЛЕВ NUMBER 22 4 ПРОДУКТЫ K NUMBER 22 4 ПРОДУКТЫ CA NUMBER 22 4 ПРОДУКТЫ NA NUMBER 22 4 ПРОДУКТЫ B2 NUMBER 22 4 ПРОДУКТЫ PP NUMBER 22 4 ПРОДУКТЫ C NUMBER 22 4 РЕЦЕПТЫ ИД NUMBER 22 9 РЕЦЕПТЫ КОД_БЛЮДА NUMBER 22 2 РЕЦЕПТЫ РЕЦЕПТ VARCHAR2 560 РЕЦЕПТЫ ВАРИАНТ NUMBER 22 2 Часть V. Язык SQL. Создание базы данных 266 СОСТАВ КОД_БЛЮДА NUMBER 22 2 СОСТАВ КОД_ПРОДУКТА NUMBER 22 2 СОСТАВ ВЕС NUMBER 22 3 ТРАПЕЗЫ КОД_ТРАПЕЗЫ NUMBER 22 1 ТРАПЕЗЫ ТРАПЕЗА VARCHAR2 7 48 rows selected Легко заметить, что для решения поставленной задачи достаточно данных из двух первых столбцов. Однако из этих данных надо удалить три строки, опи- сывающих представление ( VIEW ) НАЛИЧИЕ (см. объекты примера 14.1). Это можно сделать при помощи запроса: SELECT table_name, COUNT(column_name) Столбцов FROM USER_TAB_COLUMNS WHERE table_name NOT IN (SELECT view_name FROM USER_VIEWS) GROUP BY table_name; в котором исключаются строки, описывающие представления, и производит- ся группировка по именам таблиц с получением количества столбцов в них: TABLE_NAME СТОЛБЦОВ -------------- ---------- БЛЮДА 6 ВИДЫ_БЛЮД 2 ВЫБОР 2 МЕНЮ 4 ПОСТАВКИ 5 ПОСТАВЩИКИ 6 ПРОДУКТЫ 11 РЕЦЕПТЫ 4 СОСТАВ 3 ТРАПЕЗЫ 2 10 rows selected Пример 14.3. Получить перечень столбцов всех таблиц текущей схемы с ука- занием комментариев и типов хранимых в них данных. Для этого восполь- зуемся представлениями словаря данных USER_TAB_COLUMNS , USER_OBJECTS и USER_COL_COMMENTS : SELECT t.table_name,t.column_name,data_type|| CASE WHEN data_type = 'VARCHAR2' THEN ' ('||data_length||')' WHEN data_type = 'NUMBER' THEN ' ('||data_precision||')' ELSE NULL END ТИП_ДАННЫХ, c.comments Глава 14. Системный каталог (словарь данных) 267 FROM USER_TAB_COLUMNS t, user_objects o, USER_COL_COMMENTS c WHERE t.TABLE_NAME = o.object_name AND t.COLUMN_NAME = c.column_name AND t.TABLE_NAME = c.table_name AND object_type = 'TABLE'; TABLE_NAME COLUMN_NAME ТИП_ДАННЫХ COMMENTS ---------- -------------- -------------- ---------------------------------- БЛЮДА КОД_БЛЮДА NUMBER (2) Код блюда БЛЮДА БЛЮДО VARCHAR2 (16) Название блюда БЛЮДА КОД_ВИДА NUMBER (1) Код вида блюда БЛЮДА ОСНОВА VARCHAR2 (6) Основной продукт в блюде БЛЮДА ВЫХОД NUMBER (4) Масса порции готового блюда БЛЮДА ТРУД NUMBER (3) Стоимость приготовления блюда (коп) ВИДЫ_БЛЮД КОД_ВИДА NUMBER (1) Код вида блюда ВИДЫ_БЛЮД ВИД VARCHAR2 (7) Вид блюда (Закуска, Суп, ...) ВЫБОР МЕСТО NUMBER (2) Номер места в столовой пансионата ВЫБОР СТРОКА NUMBER (9) Строка меню МЕНЮ СТРОКА NUMBER (9) Номер строки в меню МЕНЮ КОД_ТРАПЕЗЫ NUMBER (1) Номер трапезы МЕНЮ КОД_БЛЮДА NUMBER (2) Код блюда МЕНЮ ДАТА DATE Меню составлено на дату ПОСТАВКИ КОД_ПОСТАВЩИКА NUMBER (2) Код поставщика ПОСТАВКИ КОД_ПРОДУКТА NUMBER (2) Код продукта ПОСТАВКИ ЦЕНА NUMBER (5) Договорная цена продукта (руб) ПОСТАВКИ К_ВО NUMBER (4) Наличие продукта у поставщика (кг) ПОСТАВКИ ДАТА DATE Дата поставки продукта ПОСТАВЩИКИ КОД_ПОСТАВЩИКА NUMBER (2) Код поставщика ПОСТАВЩИКИ НАЗВАНИЕ VARCHAR2 (8) Название поставщика ПОСТАВЩИКИ СТАТУС VARCHAR2 (10) (Кооператив, совхоз, база, ...) ПОСТАВЩИКИ ГОРОД VARCHAR2 (9) Город, где расположена контора ПОСТАВЩИКИ АДРЕС VARCHAR2 (14) Улица и номер дома конторы ПОСТАВЩИКИ ТЕЛЕФОН VARCHAR2 (7) Телефон конторы ПРОДУКТЫ КОД_ПРОДУКТА NUMBER (2) Код продукта ПРОДУКТЫ ПРОДУКТ VARCHAR2 (8) Название продукта ПРОДУКТЫ БЕЛКИ NUMBER (4) Белков в 1000 г продукта (г) ПРОДУКТЫ ЖИРЫ NUMBER (4) Жиров в 1000 г продукта (г) ПРОДУКТЫ УГЛЕВ NUMBER (4) Углеводов в 1000 г продукта (г) ПРОДУКТЫ K NUMBER (4) Калия в 1000 г продукта (мг) ПРОДУКТЫ CA NUMBER (4) Кальция в 1000 г продукта (мг) ПРОДУКТЫ NA NUMBER (4) Натрия в 1000 г продукта (мг) Часть V. Язык SQL. Создание базы данных 268 ПРОДУКТЫ B2 NUMBER (4) Рибофлавина в 1000 г продукта (мг) ПРОДУКТЫ PP NUMBER (4) Ниацина в 1000 г продукта (мг) ПРОДУКТЫ C NUMBER (4) Аскорбинки в 1000 г продукта (мг) РЕЦЕПТЫ ИД NUMBER (9) Идентификатор рецепта РЕЦЕПТЫ КОД_БЛЮДА NUMBER (2) Код блюда РЕЦЕПТЫ РЕЦЕПТ VARCHAR2 (560) Технология приготовления блюда РЕЦЕПТЫ ВАРИАНТ NUMBER (2) Номер варианта рецепта СОСТАВ КОД_БЛЮДА NUMBER (2) Код блюда СОСТАВ КОД_ПРОДУКТА NUMBER (2) Код продукта СОСТАВ ВЕС NUMBER (3) Масса продукта в блюде ТРАПЕЗЫ КОД_ТРАПЕЗЫ NUMBER (1) Номер трапезы ТРАПЕЗЫ ТРАПЕЗА VARCHAR2 (7) Название трапезы |