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, which means Global V $. In addition to 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, kqftaobj, ’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

In this way, we find the statement for creating the GV $ FIXED_TABLE view, which is created based on the X $ table:

1 2 Next Page

[Content navigation]
Page 4: Oracle Data Dictionary	Page 4: Oracle Data Dictionary