Oracle data dictionary and dynamic performance table

Importance of oracle data dictionary and dynamic performance table: 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 performing operations, and we also need to query the data dictionary when operating data with constraints. 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 are automatically maintained by the oracle server. Normal users do not have the permission to view them, oralce DBA can only view it. These tables belong to sys users. Only select access is allowed. We mainly learn about oralce's data dictionary and dynamic performance table. Some data dictionaries play an important role in oracle. It is a read-only table that provides oralce information. The data dictionary includes: 1: 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 currently used 3: storage of the physical and logical structure of the database structure 4: Data Integrity Constraint Information 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 through the SELECT statement. 2. Composition of the data dictionary: the data dictionary consists of the following two parts: 1. Description of the Base tables Base table storage database, when you execute create database, make sure that. 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: the basic table information of \ app \ topwqp \ product \ 11.1.0 \ db_1 \ RDBMS \ ADMIN is mysterious and generally does not need to be moved. Even DBAs do not operate 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 tables. The creation of views is done by executing catalog after creating views. the data dictionary view generated by the SQL script. The script is also located at: D: \ app \ topwqp \ product \ 11.1.0 \ db_1 \ RDBMS \ ADMINdata dictionary view is a static view, which is rarely changed because of its small changes. It illustrates two categories: 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. 3. How to use a data dictionary? Data Dictionary has three main functions: 1. oracle needs Data dictionary for user information, schema objects, and storage structures; 2. Data definition language (DDL) for each operation) oracle server modifies the data dictionary information. 3. Some authorized oralce users can view the data dictionary information through the select statement. The PS data dictionary uses many synonymous aliases and uses Cache to accelerate access to the data dictionary: All database users can access the data dictionary view and access it 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 scopeuseruser's view (what is in the user's schema) ALLExpanded user's view (what the user can access) DBADatabase administrator's view (what is in all users 'schemas): Views with USER prefixes usually do not contain the OWNER column. Some DBA prefix views contain more column information. The three main differences are that some columns have different information. The USER_XXX view mainly describes the user's concerned information: 1. You have a private environment in the database, including the schema object information created by the user and the user's permission information. 2: Display User-related Column 3: No OWNER column compared with the view of ALL_XXX, and others are consistent. 4: According to the third returned result, of course, the subset is a subset of ALL_VIEW. For example, SELECT object_name, object_type FROM USER_OBJECTS; ALL_XXXSELECT owner, object_name, object_type FROM ALL_OBJECTS; DBA_XXXSELECT 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 reference dynamic performance view: 1: virtural tables database information is displayed in the form of tables 2: records the current activity of the database 3: updates from time to time during operations 4: Dynamic Performance Table Information 5 In the self-memory and control file: Main Function: Monitoring and tuning database 6: owner: sys user 7: Table name starting from V $8: the dynamic performance table is listed in V $ FIXED_TABLE; DESC v $ FIXED_TABLE some commonly used dynamic performance tables: V $ contrllfile system control file v $ parameter database parameter information v $ spparameter v $ SGAV $ TABLESPACEV $ THREAD and other table information dynamic view in oracle online document for real-time monitoring of database performance very useful: the following three points are discussed: 1. The purpose of the Dynamic View (why do we need a dynamic view) 2. The system of the dynamic view 3: the use of the Dynamic View 1: the purpose of the Dynamic Performance Views Dynamic view can monitor The internal organs of oracle in real time, and monitor the Performance of each part of oracle, such as The health of various parts of a human. What is the difference between data dictionary and dynamic view? Imagine: 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. This is easy to understand, if you want to replace some of the parts, if you want to adjust the speed of the car, changing 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: The system structure dynamic performance view of the dynamic performance view is a very low-level oracle view, which we seldom need to access. These views are called X $, this view 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 V $ view: below are some examples of using V $ view: if oracle dba wants to know who is connected to this system, the following query can meet this requirement: [SQL] SQL> select sid, serial #, username, osuser, machine from v $ session 2 where username is not NULL; pay attention to 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.