Introduction to Oracle Data Dictionary

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

1.1 X $ table

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

These sub-tables are crucial to the database. Therefore, Oracle does not allow users outside of SYSDBA to access the table directly, indicating that authorization is not allowed.

If authorization is displayed, 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 $ 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, after the INST_ID column is added, it is created and 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

----------------------------------------------------------

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

-----------------------------------------------------------

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.

1.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 careful understanding of the Oracle Data Dictionary mechanism helps us to gain an in-depth understanding of and learn about Oracle databases.

1.4 Further

1.4.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.00 0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 1024K MINEXTENTS 1 M8 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 MAXT14 14 CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBE5 5 CREATE TABLE CLU$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL6 6 CREATE CLUSTER C_TS#("TS#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 2557 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. Thus, the database is started based on this part of information, which realizes database guidance, similar to the initialization of the operating system. 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.

1.4.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 records 394 + 259 + 259 objects in total.

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_MODIFIABLE , ISMODIFIED , ISADJUSTED , DESCRIPTION, UPDATE_COMMENT from GV$PARAMETER where 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.indx = y.indx) and ((translate(ksppinm,’_’,’#’) not like ’#%’) or (ksppstdf = ’FALSE’))

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.

(