In-depth understanding of Oracle data Dictionaries

oracle| data
First, Oracle's dictionary tables and views can basically be divided into three levels.
1.1 x$ Table
This section of the table is the basis for the operation of the Oracle database, which is dynamically created by the Oracle application when the database is started.

This part of the table is critical to the database, so Oracle does not allow direct access to users outside of SYSDBA, showing authorization is not allowed.

If you display authorization 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 with Oracle8, the gv$ view is introduced, which means global v$.

In addition to some exceptions, each v$ view has a corresponding gv$ view.



The gv$ view is generated to meet the needs of the OPS environment, where the query gv$ view returns all instance information, and each v$ view is based on the gv$ view, and the inst_id column is added to the decision, which contains only the current connection instance information.



Note that each v$ view contains a similar statement:

where inst_id = USERENV (' Instance ')



Used to restrict 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 = USERENV (' 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, 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



So we found the Gv$fixed_table view creation statement, which is based on the x$ table.
1.3 gv_$,v_$ View and v$,gv$ synonyms
These views are created by CATALOG.QL.



When Catalog.sql is running:

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 note that the first view v_$ and gv_$ are created first, v_$ and gv_$ two views.

The synonyms are then created based on the v_$ view.



So, in fact, the v$ view that we usually visit is actually a synonym for the v_$ view.

The v_$ view is based on a true v$ view (this view is based on the x$ table).



While the V$fixed_view_definition view is an entry point for our research into Oracle object relationships, a careful understanding of Oracle's data dictionary mechanisms can help you understand and learn about Oracle database knowledge.


1.4 Further 1.4.1 x$ table
As for the x$ table, we can also get a glimpse of the information in the data dictionary.

First we examine the bootstrap$ table, which records the basic and driving 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 Initrans 2 maxt

The CREATE TABLE seg$ ("file#" number not NULL, "block#" number is not NULL, "type#" Numbe

5 5 CREATE TABLE clu$ ("obj#" number not NULL, "dataobj#" number, "ts#" number is not null

6 6 CREATE CLUSTER c_ts# ("ts#" number) PCTFREE pctused-Initrans 2 Maxtrans 255

7 7 CREATE INDEX i_ts# on CLUSTER c_ts# PCTFREE Initrans 2 Maxtrans 255 STORAGE (

....



This part of the information, at the start of the database was first loaded, tracking the database startup process, we found that the first operation of the database to start is:



CREATE TABLE bootstrap$ (line# number not NULL, obj#

Number isn't null, Sql_text VARCHAR2 (4000) NOT NULL) storage (initial

50K objno Extents (file 1 Block 377))





This part of the code is written in an Oracle application, and after creating the bootstrap$ in memory, Oracle can read additional information from File 1,block 377 and create important database objects. Thus starting the database based on this section of information, which enables the database to boot, similar to the initialization of the operating system.

You can refer to Biti_rainy's article in this section.



The x$ table is thus established. This part of the table can be found from the 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$ views
After the x$ table is established, gv$ and v$ views based on the x$ table are created.



This part of the view can also be obtained by querying v$fixed_table.



Sql> Select COUNT (*) from v$fixed_table where name like ' gv$% ';

COUNT (*)

----------

259



This part is a total of 259 objects.



Sql> Select COUNT (*) from v$fixed_table where name like ' v$% ';

COUNT (*)

----------

259



is also 259 objects.



V$fixed_table has recorded:

394 + 259 + 259 A total of 912 objects.



We tracked the schema of the database 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 see that V$parameter was 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 see Gv$parameter from the X$KSPPI,X$KSPPCV two x$ tables. X$KSPPI,X$KSPPCV basically contains all the database adjustable parameters, V$parameter shows arguments that do not begin with "_". Arguments that begin with "_" are often referred to as suppressed parameters and are generally not recommended for modification, but many are widely known for their powerful and often used features.