Oracle data dictionary and dynamic performance table

Important reference 1:

Important reference 2:

Important reference 3:

Reference slides

11g Data Dictionary reference

Importance:

Because the data dictionary contains user information, we need to query the data dictionary when logging on, and have permission information. We also need to query the data dictionary when doing operations with constraint information.

We also need to query the data dictionary when operating data. Therefore, the data dictionary is very important for Oracle databases.

This article mainly describes some built-in objects of the database, the understanding and use of data dictionaries, how to create a view of data dictionaries, and how to query these tables.

The data dictionary mainly describes the database and its objects, including read-only tables and views. These read-only tables and views are stored in the system tablespace, and Oracle server automatically

For maintenance, normal users do not have the permission to view, oralce DBA can only view. These tables belong to SYS users. Only select access is allowed.

This section mainly describes oralce's data dictionary and dynamic performance table.

Data Dictionary plays an important role in Oracle. It is a read-only table that provides oralce information. The data dictionary includes:

I. What information does the data dictionary contain?

1: Definitions of all schema objects in the database include (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)

2: How much space has been allocated to the schema object and how much space is used currently

3: stores the physical and logical structure of the database structure

4: Data integrity constraints

5: user information, role information, permission information, and audit and object space allocation.

A data dictionary consists of tables and views. All data dictionary tables and views are provided by the system tablespace in the storage space.

Data Dictionary is important not only to databases, but also to users and DBAs. You can only use SQL statements to access the data dictionary because it is read-only, you can only access these tables and views using select statements.

Ii. Data Dictionary composition:

The data dictionary consists of the following two parts:

1: base tables base table

The description of the storage database. It is determined when you execute create database. Script for creating a base table: SQL. bsq when creating a database, this script will automatically call to create a base table.

This script is under ORACLE_HOME/rdbms/admin. If you are interested, you can check it out. My Oracle address is:

D: \ app \ topwqp \ product \ 11.1.0 \ db_1 \ RDBMS \ admin

The information about these base tables is mysterious and generally does not need to be moved. Even DBA generally does not need to operate the tables.

2: Data Dictionary views (views accessible to users)

These views summarize and display the information of the base table of the data dictionary. These views convert the information in the base table into more intuitive and needed information,

Most users need to access views instead of base table.

The main view is the data dictionary view generated by running the catalog. SQL script after the view is created,

The script is also located at: D: \ app \ topwqp \ product \ 11.1.0 \ db_1 \ RDBMS \ admin

Data Dictionary view is a static view, which is rarely changed because of its small changes.

Two categories are described:

When will the data dictionary be changed?

When using the DDL (Data define language) language, Oracle Server modifies the data dictionary information, such as creating tables, deleting tables, and adding constraints and indexes. The owner (owner) of the data dictionary is a sys user. Other users cannot modify the information of this user, so the security is high.

Note: modifying and operating a data dictionary table may affect data operations for a long time. Therefore, be careful.

Iii. Data Dictionary usage

How to use a data dictionary?

Data Dictionary has three main functions:

1: Oracle needs data dictionaries when looking for user information, schema objects, and storage structures

2: each time you operate DDL (Data Definition Language), the Oracle server modifies the data dictionary information.

3: Some authorized oralce users can view the data dictionary information through the SELECT statement.

PS data dictionaries use synonymous aliases and cache for faster access

How to use data dictionary:

All database users can access the data dictionary view and access through SQL statements. Some views can be accessed by all users, but some views can only be accessed by DBAs.

When the database is open, the data dictionary can be accessed. The data dictionary contains many views, which are roughly divided into three types, different types of views have different prefixes.

The following is a good description:

Prefix	Scope
USER	User's view (what is in the user's schema)
ALL	Expanded user's view (what the user can access)
DBA	Database Administrator's view (what is in all users' Schemas)

Differences between different views:

A view with a user prefix usually does not contain the owner column, which is usually invisible to users.

Some DBA prefix views contain more column information.

The three main differences are that some columns have different information.

User_xxx

This view mainly describes the information that the user cares about:

1: You have your own private environment in the database, including the schema object information created by the user and the permission information of the user.

2: Display User-related columns

3: There is no owner column compared with the view of all_xxx, and the others are consistent.

4: The returned result is of course a subset of all_view. subset is nonsense;

Example: Select object_name, object_type from user_objects;

All_xxx

SELECT owner, object_name, object_type FROM ALL_OBJECTS; 

DBA_XXX

SELECT owner, object_name, object_type FROM SYS.DBA_OBJECTS; 
Data Dictionary list: Select table_name from dictionary;
Dynamic Performance view (dynamic performance V $ views)
11g online document reference
Important references
Dynamic Performance View:
1: Your Ural tables database information is displayed as a table
2: records the current activities of the database.
3: update operations from time to time
4: Dynamic Performance Table information comes from memory and Control Files
5. Main functions: Monitoring and optimizing Databases
6: Owner owner: SYS user
7: The table name starts with V $
8: the dynamic performance table is listed in V $ fixed_table;
Desc v $ fixed_table
Some common dynamic performance tables:
V $ control file in contrllfile System
V $ parameter database parameter information
V $ spparameter
V $ SGA
V $ tablespace
V $ thread
And other table information
In Oracle online document
The dynamic view is useful for real-time database monitoring:
Three points are discussed below:
1: The purpose of the Dynamic View (why do we need a dynamic view)
2: Dynamic View System
3: Use of dynamic views
1: The purpose of the dynamic performance views
The dynamic view can monitor the internal organs of Oracle in real time, and monitor the performance of each part of Oracle, for example, the health of each part of a human.
What is the difference between data dictionary and dynamic view?
The data dictionary is like a pair of parts. It lists all the parts of a car and shows the position of these parts in the car.
It is easy to understand that if you want to replace some of the parts, if you want to adjust the speed of the car, changing the parts is an undesirable choice.
The V $ dynamic view is like a vehicle speed meter and a speed meter. It tells us how fast the current car is. It helps us adjust the speed through the speed lamp, and the database is the same, the dynamic view provides feedback on the status of each part of the database.
2: Architecture of the dynamic performance View
The dynamic performance view is a very low-level Oracle view, which is rarely accessed. These views are called X $, which reflects the internal structure of oracle.
The x $ view is located at the bottom layer of Oracle, and is generally not accessible by experts. V $ is a view created on top of x $. I think the V $ view is synonymous with x $. It is best to use v $ for query.
Use of the V $ View:
The following are examples of using the V $ View:
If Oracle DBA wants to know who is connected to this system, the following query can meet this requirement:
SQL> select sid, serial#, username, osuser, machine from v$session  2 where username is not NULL;
Note This Sid. The serial # column is a user ID connecting to the Oracle database. Username is the username used to connect to the database. The osuser column displays the operating system name of the user connected to the database,
There are many dynamic performance views in Oracle, providing a lot of valuable information.
See the document for more dynamic view information