The Oracle data dictionary consists of a set of tables and views that store the activity information of the Oracle system and the definition information of all user databases. Depending on the content stored in the data dictionary, you can divide them into two categories: static data dictionary and dynamic performance tables.
1, static data dictionary
The data dictionary is part of the database system. Its table space is SYSTEM table space, which is why we generally do not add private information. There are almost all of the system resource information, user login information, and database information, which are automatically established and maintained by the system. Users only need to use the data dictionary to get the information they want.
The data dictionary is composed of some views, which can be divided into four types, which are distinguished by the prefix of their views, as shown in the following table.
Data Dictionary Prefix Description All objects of all objects User users private object information DBADBA managed database information V $ running dynamically changed information
The data dictionary is the same as the viewing way of the table and view, just use the select command.
【example】
SQL> SELECT * from Dict - Dict Representation Data Dictionary
Where Table_name Like '% _ synonyms';
The results are as follows:
Table_namecomments ------------------------------------- --- All_Synynymsall synonyms accessible to the userdba_synonymsall synonyms in the databaseuser_synonymsthe user's private synonyms
Note: Whether it is a user or a DBA administrator, you must master how to effectively use the data dictionary.
2, dynamic performance table
Dynamic performance tables are a set of virtual tables that record the activity and performance parameters of the current database. The database administrator can understand the problem of system operation, diagnostics, and resolve system operations by querying dynamic performance tables.
Dynamic performance table provides basic interfaces of Oracle system performance information, management tools provided by Oracle, such as Oracle Enterprise Manager and Oracle TRACE, etc., but also through dynamic performance tables to get database run status information.
For ease of access, Oracle creates a common synonym (starting with V $ starting), the database administrator or authorized user using the V $ object, and can access system performance table data using the V $ object (starting at V-$), the Database Administrator or Authorized User.
[Example] Let's take a v $ SGASTAT view as an example to illustrate the application of the dynamic performance table.
The V $ SGASTAT view contains details on the system global zone, as shown below:
● Pool: shared pool / Java pool / bulk memory pool.
● NAME: Buffer Name
● BYTES: The size of the memory is.
SQL> SELECT * FROM V $ SGASTAT;
PoolNameBytes ---------------------------------------------- Fixed_SGA282576DB_BLOCK_BUFFERS33554432LOG_BUFFER524288Shared pool1m Buffer10490088shared poolcheckpoint queue141152 ... shared pooltrigger infor1140shared pooltrigger source116java poolfree memory27996160java poolmemory in use5558272
Excerpted from the "Oracle9i Concise Tutorial" Tsinghua University Press
