Data dictionary and system tables

Data dictionary and system tables

Address: http://weibo.com/p/1001603822234199319043

A data dictionary describes the basic information of a database system, such as the definition of a table structure. It describes the meta-information of a database, and any database object requires a corresponding data dictionary object to describe it. The representation of a data dictionary is a set of system tables,

The table that starts with SYS in Dream 7 is the system table, and its owner is the SYS user built into the system.

Using system tables to hold data dictionaries is not a unique data dictionary representation. Some systems, such as MySQL, have a corresponding frm file for each table to describe the table structure, which is easy to implement, but requires special syntax to query, such as a show statement .

The advantage of using system tables is that it is easy to query using the standard SQL language (for example, SQL Server select * from sys.dm_exec_xxxx). Dameng 7 system tables, including system performance dynamic Views (v$), can be accessed using complex SQL standard syntax,

This is also one of the relationship criteria for relational databases.

Dameng 7 has a core system of about dozens of tables, keeping it as simple as possible. For some extended functions, such as automatic workload awr, timed task system, and so on, some system tables are also used, but the corresponding function modules need additional creation work.

The information recorded in the system table mainly includes:
? users, Roles
? mode and schema objects, including tables, views, indexes, constraints, sequences, stored procedures, triggers, packages, etc.
? permissions, Authorization information
? security, Audit information
? Some static information of the system, such as: error code, keyword list
? Dynamic performance information for the system (v$)

The information of the system table reflects the management object and the current running state of the system, the authorized user can query, but cannot modify the system table directly . System tables are automatically maintained by the Dream 7, and users can modify the system tables indirectly only through DDL .

For ease of use, the system defines some specific object information for system views, such as All_tables, user_tables and other reaction systems. Many views have fields and information that are basically consistent with the Oracle corresponding view.

Sysobjects is one of the most important system tables, each of which represents a database object, such as creating a table, a view, and the system inserts a record in sysobjects.

Most objects also require additional system tables to hold extra information, such as: Table column information is stored in syscolumns, and obtaining complete object information for a table requires access to multiple system tables.

This requires a correlation operation .

Dameng 7 provides a tool dminit to create a physical file of the database. The main task of this tool is to create these system tables. A system table like Sysobjects/syscolumns, which initially contains a lot of data, because they need to describe themselves .

this data needs to be dminit hard-coded to create (as if the system database is installed automatically when the install_db script installs the database), and the normal user table is created by the INSERT statement on the system table.

Data dictionary and system tables