Oracle Database Data Dictionary Learning

When creating an Oracle database, some built-in database objects will be created, which can be roughly classified into four categories:
1. Data dictionary; 2. Dynamic Performance view; 3. PL/SQL packages; 4. Database event triggers. This note briefly introduces 1 and 2 data dictionaries and dynamic performance views, and introduces some commonly used data dictionaries and dynamic performance views.

 

I. Data Dictionary
Data dictionary is the core of each Oracle database. It stores very important control information, which describes the database itself and various objects in the database, they are stored in the system tablespace in the form of read-only tables and views. These objects are owned by sys users and managed by the Oracle server.

 

The Oracle Data Dictionary contains two parts:

1. base table: the description of the database is saved and created when the create database Command is executed. The created script is in <ORACLE_HOME> \ RDBMS \ admin \ SQL. bsq; (usually do not directly access the base table)

2. Data Dictionary view: simplifies the information of the base table and presents it to users. It is accessed through public aliases and created by the database script <ORACLE_HOME> \ RDBMS \ admin \ catalog. SQL.

 

The data dictionary provides the following types of information: 1. logical and physical structures of the database; 2. Definition and space allocation of database objects; 3. Data integrity constraints; 4. User information; 5. role information; 6. Permission information; 7. Audit information.

 

The data dictionary view is classified into three categories, which are identified by three prefixes:

1. DBA _ view: all views in the database, which can be accessed only by DBA and users with relevant permissions;

2. All _ view: stores objects accessible to the current user (the current user does not need to own them );

3. User _ view: stores the objects owned and accessible to the current user.

The data stored between the three elements is overlapped, but the access scope is different.

In addition, there are two general data dictionary views: dictionary and dict_columns, which are saved

Information about all data dictionary views in the database, including the dynamic performance view that will be introduced later.

 

The following uses the user _ prefix view as an example to describe some commonly used data dictionary views:

1. user_users: Describes the user information, including the user name, account ID, account status, and table space name;

2. user_tablespaces: The tablespace that the current user can access;

3. user_tables: describes the information of the table owned by the current user;

4. user_views: Information about views owned by the current user;

5. user_objects: describes information about all objects of the current user, includes sequence, procedure, database link, package, package body, type body, trigger, materialized view, dimension, index, table, synonym, view, function, type, and Other types;

6. user_tab_privs: stores the permissions of the current user on all tables;

7. user_errors: stores errors in the objects owned by the current user;

8. user_source: contains the source code of objects in the system;

In fact, many objects, such as db_link, extents, index, job, sequence, and segment, can be accessed through the table name of user _ <Complex Object Name>. The detailed meanings of fields in the data dictionary are described in Oracle Database reference. Oracle Database reference is a very important document, not only the interpretation of these data dictionaries, including the detailed meanings of each parameter in the parameter file and the dynamic performance view described later are described in this document.

 

Ii. Dynamic Performance View
The data dictionary view is static, which means that the content is stored on the disk and will not change frequently. The dynamic performance view is dynamic and does not exist on the disk, instead, there are some virtual tables in the memory, which are mainly used to record the activity of the current database. It is dynamic because, while the database is running, its content will change frequently according to the running status of the database. It is used to monitor and adjust the database. Like the data dictionary view, it is also owned by the user SYS. In general, they are all synonyms starting with V $, GV $, and x $. Similar to the dictionary view in the data dictionary, a view in the dynamic performance view also maintains an overview view of all the dynamic performance views: V $ fixed_table, another view, V $ fixed_view_definition, contains definitions of these dynamic performance views.

Starting from Oracle 8i, the GV $ view is introduced, which means global v $. Except for some special exceptions, each V $ view has a corresponding GV $ view, the GV $ view is generated to meet the needs of the OPS (Oracle Parallel Server, Oracle Parallel Server) environment. In the ops environment, query the GV $ view to return messages of all instances, each v $ view is created based on the corresponding GV $ view. After the inst_id column is added, it is used to include information about the current instance.

Run the following statements:

Select * fromv $ fixed_view_definition vwherev. view_name = 'v $ fixed_table ';
Find the definition of V $ fixed_table and get the following result:
Select name, object_id, type, table_num from GV $ fixed_table where inst_id = userenv ('instance ')
It is found that V $ fixed_table is based on GV $ fixed_table and the inst_id = userenv ('instance') restriction is added;

Let's look for the definition of GV $ fixed_table:

Select inst_id, kqftanam, kw.taobj, 'table', indx from x $ kqfta
Union all
Select inst_id, k1_vinam, k1_viobj, 'view', 65537 from x $ k1_vi
Union all
Select inst_id, kqfdtnam, kqfdtobj, 'table', 65537 from x $ k1_dt
The V $ and GV $ views are created using the catalog. SQL script. The script for creating the V $ fixed_table view is as follows:

Create or replace view V _ $ fixed_table as select * from V $ fixed_table;
Create or replace public synonym v $ fixed_table for V _ $ fixed_table;
First, the V _ $ view is created and then created based on the synonym of V _ $. Therefore, the V $ view we access is actually a synonym, the final v $ view is created based on the x $ table.

 

X $ table is the basis for running Oracle databases. It is used by Oracle Applications When the database is started.ProgramDynamic Creation. This sub-table is critical to the database, so Oracle does not allow direct access from users outside of sysdba, even if authorization is not allowed, such as executing the following command: grantselectonx $ kqftatoscott; will receive an error: ORA-02030: can only be queried from a fixed table/view.

Common dynamic performance views are as follows:

1. V $ controlfile: displays the control file list;

2. V $ Database: information about the database obtained from the control file;

3. V $ datafile: displays the data file information;

4. V $ instance: displays the status of the current instance;

5. V $ parameter: displays the parameter information in the memory.

6. V $ session: displays information about the current session;

7. V $ SGA: displays the SGA information;

8. V $ spparameter: displays the parameter information in the spfile file. If spfile is not used to start an instance, all isspecified columns in the view will be false;

9. V $ tablespace: information about the tablespace obtained from the control file;

10. V $ thread: the thread information obtained from the control file;

11. V $ version: Obtain the version information of key components.