Oracle Data Dictionary

Each database provides a schema for its own data dictionary, albeit in a different form, but with the same purpose and function as in MySQL, where the data dictionary is represented in Information_schema, and SQL Server is shown in the SYS system schema.
Oracle's data dictionary is a series of database objects that are automatically created after the Oracle database is installed. A data dictionary is metadata information for the structure of an Oracle database object. Familiarity with and in-depth study of data dictionary objects can help us to understand the internal mechanisms of Oracle to a great extent.
The Oracle dictionary view includes four levels of x$ internal tables, underlying data dictionary tables, data dictionary views, and dynamic performance views.

There is no difference between a data dictionary table and a user-created table, but the data in the data dictionary is the system data stored by the Oracle system, and the ordinary table holds the user's data.

When manually using the CREATE DATABASE command, the $ORACLE_HOME/RDBMS/ADMIN/SQL.BSQ file is called, and this executes the data dictionary tables that are generated for us. Open SQL.BSQ will find many data dictionaries almost end with $, such as col$,tab$ and so on.

The user of the data dictionary table here is the SYS, exists in the system This table space, the table name with "$" end, in order to facilitate the user to the Data dictionary table query, such names are not conducive to our memory, so Oracle to these data dictionaries have set up a user view, not only easier The accepted name, also hides the relationship between the data dictionary table, let us directly through the view to query, simple and image, Oracle for the scope of these objects, respectively, the view named Dba_xxxx, All_xxxx and User_xxxx

User_ Object View: Describes the object under the current user schema;
All_ Object View: Describes the information of all objects that the current user has access to;
Dba_ Object View: Includes information for all database objects;

Note: When you create a database, you run two scripts. Run Catalog.sql first, which is used to create the internal dictionary table for the database. Then run Catrpoc.sql, which is used to create Pl\sql objects such as stored procedures, packages, etc. that are built into the database. If we are using DBCA to create the database, DBCA will call both scripts automatically. Otherwise, you will need to run both scripts manually when you execute the CREATE DATABASE command.

Dynamic Performance View Overview

The so-called dynamic performance view refers to the data in memory or control files in the form of a table to show. They are actually virtual tables, not real tables. Just so that we can better manage the performance of the database, so the memory of the activity in the form of a table to show. As long as the database is still running, dynamic performance views are constantly being updated. Once the database is closed or crashed, the data in the dynamic performance view is lost and recalculated the next time it restarts. Just like all the nouns of a data dictionary exist in dictionary, the v$fixed_table stores all the names of the dynamic performance views. All dynamic properties of the owner are SYS, and all start with v$_, and the same noun is created with v$.

There are a lot of data dictionary views that we cannot remember, but there is a view that we must know that is the dictionary view, which records the names of all the data dictionary views. So when we need to find a data dictionary without knowing which view it is in, we can find it in the dictionary view. The view also has a noun dict. Now, let's track down Dba_synonymssql.> descdict NameNull? Type----------------------------------------- -------- ----------------------------table_nameVARCHAR2( -) COMMENTSVARCHAR2(4000) SQL> Select *  fromDictwheretable_name like '%dict%'; SQL> Select Count(*) fromdict; COUNT(*)----------      2553SQL> Select *  fromDictwheretable_name like '%synonym%'; TABLE_NAME COMMENTS------------------------------ -----------------------------------------------------------------User_synonyms theUser's private synonymsall_synonyms all synonyms for base objects accessible to the user and sessiondba_s Ynonyms all synonyms in the database under Dba_synonyms verification dba_synonyms is the same noun sql> select Owner,synonym_name, table_name from dba_synonyms; Sure enough, dba_synonyms is the same name as the Dba_synonyms table. Continue looking for dba_objects to verify which object dba_synonyms. sql> column object_name format a30sql> select Owner,object_name,object_type from dba_objects where object_name= 'Dba_synonyms'; OWNER object_name object_type-------------------------------------------                         ------------------------------------SYS dba_synonyms viewpublic Dba_synonyms synonym can see that dba_synonyms is the view under the SYS user, continue to find dba_views see the specific definition of dba_synonyms. Sql> Select View_name,text from dba_views where View_name='Dba_synonyms'; View_name TEXT--------------------------------------------------------------------------------------                               ------------------------dba_synonyms Select U.name, O.name, S.owner, S.name, S.node From sys.user$ u, sys.syn$ s, sys you can see that dba_synonyms is a view created based on the two data dictionary tables user$ and syn$ under the SYS user. It also verifies that the data dictionary is organized in the first place: The data dictionary view is created based on the data dictionary, then the same noun is created in the data dictionary view, and the same noun is assigned to public, so that all users can view the data dictionary information by the same noun. The following is an example of v$session tracking sql> Select Owner,synonym_name,table_name from dba_synonyms where Synonym_name='V$session'; OWNER synonym_name table_name-------------------------------------------- ----------------------------------------------public v$session v_$session View V _$session Object Type and owner sql> select Owner,object_name,object_type from dba_objects where Object_name='V_$session'; OWNER object_name object_type------------------------------------------- ------------------------------------SYS v_$session View Here you know v_$session is the view To view the specific definition of the view. Sql> Select TEXT from dba_views where View_name='V_$session'; You can see that the V_$session view is based on the v$session view of the dynamic performance view, while the definition information for the dynamic performance view is recorded in V$fixed_view_definition select View_definition From V$fixed_view_definition where View_name='V$session'; You can see that the dynamic performance view V$session is created based on the dynamic performance View Gv$session View and continues to track the select View_definition from v$fixed_view_definition where view _name='Gv$session'you can see that gv$session is the final definition information from X$ksuse s and X$ksle, which are the base tables for dynamic performance views. However, these tables are memory tables, and the data in the table does not exist in the data file, only in memory. 

Oracle Data Dictionary