First, Oracle's dictionary tables and views can basically be divided into three levels.
1.1 x $ table
This part of the table is the operational basis of the Oracle database, and is created dynamically created by Oracle Application when the database is started.
This part of the table is critical to the database, so Oracle does not allow users other than Sysdba to directly access, and the license is not allowed.
If you have a license you will receive the following error:
SQL> Grant SELECT ON X $ KSPPI to Eygle;
Grant SELECT ON X $ KSPPI to Eygle
*
Error At Line 1:
ORA-02030: Can Only Select from fixed Tables / Views
1.2 GV $ and V $
Starting from Oracle8, GV $ view began to be introduced, its meaning for Global V $.
In addition to some special cases, each V $ view has a corresponding GV $ view existence.
The generation of GV $ view is to meet the needs of the OPS environment. In the OPS environment, Query GV $ view returns all instance information, and each V $ view is based on GV $ view, and then the inst_id column judgment is established, only the current connection is included. Instance information.
Note that each V $ view contains similar statements:
Where INST_ID = Userenv ('Instance')
Used to limit the return of the current instance information.
We start with GV $ fixed_table and v $ fixed_table
SQL> Select View_Definition from v_ $ fixed_view_definition where view_name = 'v $ fixed_table';
View_definition
-------------------------------------------------- ----------------------------
Select Name, Object_ID, TYPE, TABLE_NUM from GV $ fixed_table where inst_id = uchesserenv ('instance ")
Here we see V $ fixed_table based on GV $ fixed_table creation.
SQL> Select View_Definition from v_ $ fixed_view_definition where view_name = 'gv $ fixed_table';
View_definition
-------------------------------------------------- ----------------------------
Select Inst_ID, KqFtanam, Kqfta, 'Table', Indx from x $ kqfta
Union all
Select Inst_ID, Kqfvinam, Kqfviobj, 'View', 65537 from x $ kqfvi
Union all
SELECT INST_ID, KQFDTNAM, KQFDTOBJ, 'TABLE', 65537 from x $ kqfdt
This way we found the creation statement of the GV $ fixed_table view, which is created based on the X $ table.
1.3 GV _ $, v_ $ view and V $, gv $ synonym
These views are created via Catalog.QL.
When Catalog.sql is running:
Create or Replace View V_ $ fixed_table as self * from v $ fixed_table;
Create or Replace Public Synonym V $ fixed_table for v_ $ fixed_table; create or replace view gv_ $ fixed_table asselect * from gv $ fixed_table;
Create Or Replace Public Synonym GV $ fixed_table for gv_ $ fixed_table;
We noticed that the first view V_ $ and GV_ $ first created, V_ $ and GV_ $ two views.
The synonym based on the V_ $ view is then created.
So, actually we usually access the V $ view, actually pointing to the synonym of the V_ $ view.
The V_ $ view is based on the real V $ view (this view is based on X $ table).
And the V $ fixed_view_definition view is an entry we studying the Oracle object relationship, carefully understands the Oracle's data dictionary mechanism to help understand and learn Oracle database knowledge.
1.4 further
1.4.1 x $ table
With regard to the X $ table, it creates information. We can also peek from the data dictionary.
First we examine the Bootstrap $ table, which records the basic and driver information of the database startup.
SQL> SELECT * from bootstrap $;
Line # Obj # SQL_Text
-------------------------------------------------- ----------------------------
-1 -1 8.0.0.0.0
0 0 Create Rollback Segment System Storage (Initial 112k Next 1024K MineXtents 1 m
8 8 Create Cluster C_File # _Block # ("TS #" Number, "Segfile #" Number, "Segblock #" Number)
9 9 Create Index i_file # _block # on cluster c_file # _block # pctfree 10 initrans 2 maxt
14 14 CREATE TABLE SEG $ ("File #" Number Not Null, "Block #" Number Not Null, "Type #" Numbe
5 5 Create Table CLU $ ("Obj #" Number Not Null, "DataObj #" Number, "TS #" Number Not NULL
6 6 Create Cluster C_TS # ("TS #" Number) PCTFree 10 PCTUSED 40 INITRANS 2 MAXTRANS 255
7 7 Create Index I_TS # on cluster c_ts # Pctfree 10 initrans 2 MaxTrans 255 Storage (
....
This part of the information, the first load process that is first loaded and traces the database startup during the database, and we found that the first action of the database start is:
Create Table Bootstrap $ (Line # Number Not Null, Obj #
Number Not Null, SQL_Text Varchar2 (4000) Not Null Storage (Initial
50k Objno 56 Extents (File 1 Block 377))
This part of the code is written in the Oracle application. After the Bootstrap $ in the memory, Oracle can read additional information from File 1, Block 377, create an important database object. This starts the database based on this part of the information, which implements the database boot, similar to the initialization of the operating system. This section you can refer to Biti_Rainy's article.
The X $ table is created. This part of the table can be viewed from V $ fixed_table:
SQL> SELECT Count (*) from v $ fixed_table where name like 'x $%';
Count (*)
------------
394
A total of 394 X $ objects were recorded.
1.4.2 GV $ and V $
After the X $ table is created, the X-list GV $ and V $ view are created.
This part of the view we can also get it by query V $ fixed_table.
SQL> SELECT Count (*) from v $ fixed_table where name like 'gv $%;
Count (*)
------------
259
This part of a total of 259 objects.
SQL> SELECT Count (*) from V $ fixed_table where name like 'v $%';
Count (*)
------------
259
It is also 259 objects.
v $ fixed_table a total of recorded:
394 259 259 a total of 912 objects.
We track the architecture of the database via the V $ Parameter view:
SQL> Select View_definition from v $ fixed_view_definition a where a.view_name = 'v $ parameter';
View_definition
-------------------------------------------------- ----------------------------
SELECT NUM, NAME, TYPE, VALUE, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIA
Ble, Ismodified, Isadjusted, Description, Update_Comment from GV $ Parameter Wh
ERE INST_ID = Userenv ('Instance')
We see that V $ Parameter is created by GV $ Parameter.
SQL> Select View_definition from V $ fixed_view_definition a where a.view_name = 'GV $ Parameter';
View_definition
-------------------------------------------------- ---------------------------
Select X.inst_ID, X.indx 1, Ksppinm, Ksppity, Ksppstvl, Ksppstdf, Decode (Bitand (KSPP
IFLG / 256, 1), 1, 'True', 'false'), Decode (Bitand (Ksppiff / 65536, 3), 1, 'Immediate', 2,
'DeferRed', 3, 'Immediate', 'False'), Decode (bit
AND (Ksppstvf, 7), 1, 'Modified', 4, 'System_MOD', 'FALSE'), DECODE (Bitand (Ksppstvf, 2), 2, 'True', 'False'), Ksppdesc, KsppstCmnt from x $ KSPPI X, X $ Ksppcv y Where (xi
NDX = Y.INDX) and ((Translate (ksppinm, '_', '#') Not Like '#%') or (ksppstdf = 'f
Alse '))
Here we see GV $ Parameter comes from X $ KSPPI, x $ ksppcv two x $ table. X $ KSPPI, X $ KSPPCV basically contains all database adjustable parameters, V $ Parameter is displayed in parameters that do not contain "_". The parameters starting with "_" We are often referred to as implied parameters, which is generally not recommended, but many are widely known because of the powerful use of power.
