Explore data dictionaries to improve self-learning Oracle capabilities

We know that Oracle uses data dictionaries to manage and display database information, which is crucial.
Correct understanding of this part helps to enhance our Oracle learning capabilities.

Next we will introduce how to study the database through the database itself.

First, Oracle dictionary tables and views can be divided into three layers.

1. x $ table
This table is the basis for running Oracle databases. It is dynamically created by Oracle Applications When the database is started.

2. gv $ and V $ views
Starting from oracle8, the GV $ view is introduced and its meaning is global v $.
Except for some special cases, each V $ view has a corresponding GV $ view.

The GV $ view is generated to meet the needs of the OPS environment. In the ops environment, query the GV $ view to return information about all instances, and each V $ view is based on the GV $ view, added the inst_id column creation, which only contains information about the currently connected instance.

Note that each V $ view contains similar statements:
Where inst_id = userenv ('instance ')
Restrict the information of the current instance returned.

Starting from GV $ fixed_table and V $ fixed_table

SQL> select view_definition from V _ $ fixed_view_definition where view_name = 'v $ fixed_table ';

View_definition
Bytes ------------------------------------------------------------------------------------------------------
Select name, object_id, type, table_num from GV $ fixed_table where inst_id = userenv ('instance ')

Here we can see that V $ fixed_table is created based on GV $ fixed_table.

SQL> select view_definition from V _ $ fixed_view_definition where view_name = 'gv $ fixed_table ';

View_definition
Bytes --------------------------------------------------------------------------------------------------------
Select inst_id, kqftanam, kw.taobj, 'table', indx from x $ kqfta
Union all
Select inst_id, k1_vinam, k1_viobj, 'view', 65537 from x $ k1_vi
Union all
Select inst_id, kqfdtnam, kqfdtobj, 'table', 65537 from x $ k1_dt

In this way, we find the statement for creating the GV $ fixed_table view, which is created based on the x $ table.

3. GV _ $, V _ $ view and V $, GV $ Synonym
These views are created through catalog. QL.

When catalog. SQL is run:
Create or replace view V _ $ fixed_table as select * from V $ fixed_table;
Create or replace public synonym v $ fixed_table for V _ $ fixed_table;

Create or replace view GV _ $ fixed_table as select * 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 _ $ were created first, and the views V _ $ and GV _ $ were created first.
The table is created based on the synonym of the V _ $ view.

Therefore, the V $ view we usually access is actually a synonym pointing to the V _ $ view.
The V _ $ view is based on the real v $ view (this view is created based on the x $ table ).

The V $ fixed_view_definition view is an entry point for us to study the relationship between Oracle Objects. a closer understanding of the Oracle Data Dictionary mechanism helps us to understand and learn about Oracle databases.

Further
1. x $ table

We can also look at the creation Information of the x $ table in the data dictionary.
First, we will examine the bootstrap $ table, which records the basic and driver information for 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 information is first loaded when the database is started. We track the database startup process and find that the first action to start the database 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 Bootstrap $ is created in the memory, Oracle can read other information from File 1, block 377 and create important database objects.
For this part, refer to biti_rainy's article.

The x $ table is created accordingly. This part of the table can be found in V $ fixed_table:

SQL> select count(*) from v$fixed_table where name like 'X$%'; COUNT(*) ---------- 394

A total of 394 x $ objects are recorded.

2. gv $ and V $ views
After the x $ table is created, the GV $ and V $ views of the x $ table are created.

You can also obtain this view by querying v $ fixed_table.

SQL> select count(*) from v$fixed_table where name like 'GV$%'; COUNT(*) ---------- 259

A total of 259 objects are contained in this Part.

SQL> select count(*) from v$fixed_table where name like 'V$%'; COUNT(*) ---------- 259

259 objects.

V $ fixed_table:
394 + 259 + 259 a total of 912 objects.

We track the database architecture through 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 can 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(ksppiflg/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 (x.i ndx = y.indx) and ((translate(ksppinm,'_','#') not like '#%') or (ksppstdf = 'F ALSE'))

Here we can see that GV $ parameter comes from two x $ tables, x $ ksppcv and x $ ksppcv. X $ ksppi, x $ ksppcv basically contains all database adjustable parameters. V $ parameter shows parameters that do not start. Parameters starting with "_" are generally called implicit parameters and are not recommended to be modified. However, they are widely known for their powerful functions.