Что такое data dictionary
Словарь данных
Словарь данных, описанный в Словаре вычислений от IBM (IBM Dictionary of Computing) как «центральное хранилище информации о данных, такой как значение, взаимосвязи с другими данными, их иcточник, применение и формат.» [1] Термин может иметь одно из близких по смыслу значений, относясь к базам данных и СУБД:
Содержание
Документация словаря данных
Пользователи баз данных и разработчики приложений могут получить выгоду от единого стандартизированного документа словаря данных, который перечисляет организацию, содержимое, соглашения по одной или более баз данных. [2] Это обычно включает в себя имена и описания различных таблиц и полей в каждой базе данных, дополнительные детали такие, как тип и длина каждого элемента данных. Не существует универсального стандарта, описывающего уровень детализации в подобном документе, но есть основное описание метаданных о структуре базы данных, а не о самих данных. Документ словаря данных также может включать в себя дополнительную информацию, описывающую кодирование элементов данных. Одним из преимуществ хорошо спроектированного документа словаря данных является то, что он помогает упорядочить структуру базы данных или большого комплекса распределенных баз данных. [3]
Словарь данных как промежуточное ПО
В области создания приложений для баз данных, может быть полезным добавление дополнительного программного слоя словаря данных, то есть подпрограммного ПО, который будет взаимодействовать с нижележащим словарем данных СУБД. Такой «высокоуровневый» словарь данных может обеспечить дополнительные возможности и степень гибкости, который обойдет ограничения естественного «низкоуровневого» словаря данных, чье главное назначение заключается в поддержке основных функций СУБД, а не требований обычных приложений. Например, высокоуровневый словарь данных может реализовывать альтернативные ER-модели данных, приспособленных под различные приложения, которые совместно используют распространенные базы данных. [4] Расширения словаря данных также могут помочь и в области оптимизации запросов в распределенных базах данных. [5]
4
The Data Dictionary
This chapter describes the central set of read-only reference tables and views of each Oracle database, known collectively as the data dictionary. The chapter includes:
Introduction to the Data Dictionary
One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:
The data dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database’s SYSTEM tablespace.
Not only is the data dictionary central to every Oracle database, it is an important tool for all users, from end users to application designers and database administrators. Use SQL statements to access the data dictionary. Because the data dictionary is read-only, you can issue only queries ( SELECT statements) against it’s tables and views.
«The SYSTEM Tablespace» for more information about SYSTEM tablespaces
Structure of the Data Dictionary
The data dictionary consists of the following:
Base Tables
The underlying tables that store information about the associated database. Only Oracle should write to and read these tables. Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.
User-Accessible Views
The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information, such as user or table names, using joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.
SYS, Owner of the Data Dictionary
Altering or manipulating the data in data dictionary tables can permanently and detrimentally affect the operation of a database.
How the Data Dictionary Is Used
The data dictionary has three primary uses:
How Oracle Uses the Data Dictionary
No data in any data dictionary table should be altered or deleted by any user.
During database operation, Oracle reads the data dictionary to ascertain that schema objects exist and that users have proper access to them. Oracle also updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data.
Public Synonyms for Data Dictionary Views
Oracle creates public synonyms for many data dictionary views so users can access them conveniently. The security administrator can also create additional public synonyms for schema objects that are used systemwide. Users should avoid naming their own schema objects with the same names as those used for public synonyms.
Cache the Data Dictionary for Fast Access
Much of the data dictionary information is kept in the SGA in the dictionary cache, because Oracle constantly accesses the data dictionary during database operation to validate user access and to verify the state of schema objects. All information is stored in memory using the least recently used (LRU) algorithm.
Parsing information is typically kept in the caches. The COMMENTS columns describing the tables and their columns are not cached unless they are accessed frequently.
Other Programs and the Data Dictionary
Other Oracle products can reference existing views and create additional data dictionary tables or views of their own. Application developers who write programs that refer to the data dictionary should refer to the public synonyms rather than the underlying tables: the synonyms are less likely to change between software releases.
How to Use the Data Dictionary
The views of the data dictionary serve as a reference for all database users. Access the data dictionary views with SQL statements. Some views are accessible to all Oracle users, and others are intended for database administrators only.
The data dictionary is always available when the database is open. It resides in the SYSTEM tablespace, which is always online.
The data dictionary consists of sets of views. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes:
Table 4-1 Data Dictionary View Prefixes
User’s view (what is in the user’s schema)
Expanded user’s view (what the user can access)
Database administrator’s view (what is in all users’ schemas)
The set of columns is identical across views, with these exceptions:
Oracle9i Database Reference for a complete list of data dictionary views and their columns
Views with the Prefix USER
For example, the following query returns all the objects contained in your schema:
Views with the Prefix ALL
Views with the prefix ALL refer to the user’s overall perspective of the database. These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema objects that the user owns. For example, the following query returns information about all the objects to which you have access:
Views with the Prefix DBA
Oracle9i Database Administrator’s Guide for detailed information on system privileges restrictions
The DUAL Table
Oracle9i SQL Reference for more information about the DUAL table
Dynamic Performance Tables
Dynamic performance tables are not true tables, and they should not be accessed by most users. However, database administrators can query and create views on the tables and grant access to those views to other users. These views are sometimes called fixed views because they cannot be altered or removed by the database administrator.
Oracle9i Database Reference for a complete list of the dynamic performance views’ synonyms and their columns
Database Object Metadata
The DBMS_METADATA package provides interfaces for extracting complete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL. Two styles of interface are provided:
СОДЕРЖАНИЕ
Документация
Если система словарей данных используется только разработчиками, пользователями и администраторами, а не программным обеспечением СУБД, она называется пассивным словарем данных. В противном случае он называется активным словарем данных или словарем данных. Когда пассивный словарь данных обновляется, это делается вручную и независимо от любых изменений в структуре СУБД (базы данных). При активном словаре данных словарь обновляется первым, и в результате изменения происходят в СУБД автоматически.
В активном словаре данных ограничения могут накладываться на базовые данные. Например, диапазон может быть наложен на значение числовых данных в элементе данных (поле), или запись в таблице может быть ПРИНУЖДЕНА для участия в установленной взаимосвязи с другим типом записи. Кроме того, распределенная СУБД может иметь определенные особенности местоположения, описанные в ее активном словаре данных (например, где физически расположены таблицы).
Словарь данных состоит из типов записей (таблиц), созданных в базе данных с помощью командных файлов, созданных системой, адаптированных для каждой поддерживаемой серверной СУБД. В Oracle есть список конкретных представлений для пользователя «sys». Это позволяет пользователям искать точную информацию, которая им необходима. Командные файлы содержат операторы SQL для CREATE TABLE, CREATE UNIQUE INDEX, ALTER TABLE (для ссылочной целостности) и т. Д. С использованием специального оператора, необходимого для этого типа базы данных.
Универсального стандарта в отношении уровня детализации в таком документе не существует.
ПО промежуточного слоя
Некоторые отрасли используют словари обобщенных данных в качестве технических стандартов для обеспечения взаимодействия между системами. Отрасль недвижимости, например, соблюдает Словарь данных РЕСО, которому Национальная ассоциация РИЭЛТОРОВ требует соблюдения своих MLS в своем справочнике по политике. Этот промежуточный слой отображения для собственных баз данных MLS поддерживается компаниями-разработчиками программного обеспечения, которые предоставляют услуги API для организаций MLS.
Примеры для конкретных платформ
Типичные атрибуты
Вот неполный список типичных элементов словаря данных для столбцов или полей:
7 The Data Dictionary
This chapter contains the following topics:
Introduction to the Data Dictionary
The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
How much space has been allocated for, and is currently used by, the schema objects
Default values for columns
Integrity constraint information
The names of Oracle users
Privileges and roles each user has been granted
Auditing information, such as who has accessed or updated various schema objects
Other general database information
The data dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database’s SYSTEM tablespace.
Not only is the data dictionary central to every Oracle database, it is an important tool for all users, from end users to application designers and database administrators. Use SQL statements to access the data dictionary. Because the data dictionary is read only, you can issue only queries ( SELECT statements) against it’s tables and views.
«Bigfile Tablespaces» for more information about SYSTEM tablespaces
Structure of the Data Dictionary
The data dictionary consists of the following:
Base Tables
The underlying tables that store information about the associated database. Only Oracle should write to and read these tables. Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.
User-Accessible Views
The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information, such as user or table names, using joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.
SYS, Owner of the Data Dictionary
Altering or manipulating the data in data dictionary tables can permanently and detrimentally affect the operation of a database.
How the Data Dictionary Is Used
The data dictionary has three primary uses:
Oracle accesses the data dictionary to find information about users, schema objects, and storage structures.
Oracle modifies the data dictionary every time that a data definition language (DDL) statement is issued.
Any Oracle user can use the data dictionary as a read-only reference for information about the database.
How Oracle Uses the Data Dictionary
No data in any data dictionary table should be altered or deleted by any user.
During database operation, Oracle reads the data dictionary to ascertain that schema objects exist and that users have proper access to them. Oracle also updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data.
Public Synonyms for Data Dictionary Views
Oracle creates public synonyms for many data dictionary views so users can access them conveniently. The security administrator can also create additional public synonyms for schema objects that are used systemwide. Users should avoid naming their own schema objects with the same names as those used for public synonyms.
Cache the Data Dictionary for Fast Access
Parsing information is typically kept in the caches. The COMMENTS columns describing the tables and their columns are not cached unless they are accessed frequently.
Other Programs and the Data Dictionary
Other Oracle products can reference existing views and create additional data dictionary tables or views of their own. Application developers who write programs that refer to the data dictionary should refer to the public synonyms rather than the underlying tables: the synonyms are less likely to change between software releases.
How to Use the Data Dictionary
The views of the data dictionary serve as a reference for all database users. Access the data dictionary views with SQL statements. Some views are accessible to all Oracle users, and others are intended for database administrators only.
The data dictionary is always available when the database is open. It resides in the SYSTEM tablespace, which is always online.
The data dictionary consists of sets of views. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes:
Table 7-1 Data Dictionary View Prefixes
User’s view (what is in the user’s schema)
Expanded user’s view (what the user can access)
Database administrator’s view (what is in all users’ schemas)
The set of columns is identical across views, with these exceptions:
Some DBA views have additional columns containing information useful to the administrator.
Oracle Database Reference for a complete list of data dictionary views and their columns
Views with the Prefix USER
Refer to the user’s own private environment in the database, including information about schema objects created by the user, grants made by the user, and so on
Display only rows pertinent to the user
Have columns identical to the other views, except that the column OWNER is implied
Return a subset of the information in the ALL views
Can have abbreviated PUBLIC synonyms for convenience
For example, the following query returns all the objects contained in your schema:
Views with the Prefix ALL
Views with the prefix ALL refer to the user’s overall perspective of the database. These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema objects that the user owns. For example, the following query returns information about all the objects to which you have access:
Views with the Prefix DBA
Oracle Database Administrator’s Guide for detailed information on system privileges restrictions
The DUAL Table
Oracle Database SQL Reference for more information about the DUAL table
Dynamic Performance Tables
Throughout its operation, Oracle maintains a set of virtual tables that record current database activity. These tables are called dynamic performance tables.
Dynamic performance tables are not true tables, and they should not be accessed by most users. However, database administrators can query and create views on the tables and grant access to those views to other users. These views are sometimes called fixed views because they cannot be altered or removed by the database administrator.
Oracle Database Reference for a complete list of the dynamic performance views’ synonyms and their columns
Database Object Metadata
The DBMS_METADATA package provides interfaces for extracting complete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL. Two styles of interface are provided:
A flexible, sophisticated interface for programmatic control
6 Data Dictionary and Dynamic Performance Views
This chapter contains the following sections:
Overview of the Data Dictionary
An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database.
A data dictionary contains information such as the following:
The definitions of every schema object in the database, including default values for columns and integrity constraint information
The amount of space allocated for and currently used by the schema objects
The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users
The data dictionary is a central part of data management for every Oracle database. For example, the database performs the following actions:
Accesses the data dictionary to find information about users, schema objects, and storage structures
Modifies the data dictionary every time that a DDL statement is issued
Because Oracle Database stores data dictionary data in tables, just like other data, users can query the data with SQL. For example, users can run SELECT statements to determine their privileges, which tables exist in their schema, which columns are in these tables, whether indexes are built on these columns, and so on.
Contents of the Data Dictionary
The data dictionary consists of base tables and views.
These objects are defined as follows:
These store information about the database. Only Oracle Database should write to and read these tables. Users rarely access the base tables directly because they are normalized and most data is stored in a cryptic format.
These decode the base table data into useful information, such as user or table names, using joins and WHERE clauses to simplify the information. The views contain the names and description of all objects in the data dictionary. Some views are accessible to all database users, whereas others are intended for administrators only.
Typically, data dictionary views are grouped in sets. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes, as shown in the following table. By querying the appropriate views, you can access only the information relevant for you.
Table 6-1 Data Dictionary View Sets