The relationship between Oracle Dynamic view v$,v_$,gv$,gv_$ and x$

Preface: In the process of Oracle operations, often use some dynamic views beginning with v$, such as v$session, once saw someone with v_$session, think someone else wrote wrong, didn't think Desc v_$session can see and v$ Session of the structure, and then found in the beginning of the gv$ view and so on. The opportunity to mount Oracle on a Linux system finally clarified the relationship between these dynamic views and the corresponding tables.
These are data structures that are managed by Oracle itself, starting with v$fixed_table:
[Email protected] admin]$ sqlplus Sys/[email protected] as Sysdba
Sql*plus:release 11.2.0.1.0 Production on Mon Dec 14 11:27:20 2009
Copyright (c) 1982, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0–production
With the partitioning, OLAP, Data Mining and Real application testing options
sql> desc v$fixed_table;
Name Null? Type
—————————————– ——– —————————-
NAME VARCHAR2 (30)
OBJECT_ID number
TYPE VARCHAR2 (5)
Table_num number
Sql> SELECT * from v$fixed_table
NAME object_id TYPE Table_num
—————————— ———- —– ———-
X$kqfta 4294950912 TABLE 0
X$kqfvi 4294950913 TABLE 1
Gv$process 4294951256 VIEW 65537
V$process 4294950917 VIEW 65537
Gv$bgprocess 4294951257 VIEW 65537
.............................................
From above you can see that gv$ and v$ are views, x$ is a table. What is the relationship between them? Get the following information from another view v$fixed_view_definition (for example, v$fixed_table):
Sql> Set Linesize 100
Sql> Col view_name for A15
Sql> Col view_definition for A80
Sql> SELECT * from v$fixed_view_definition where view_name= ' v$fixed_table ';
View_name view_definition
———————- ——————————————————————————–
V$fixed_table Select NAME, object_id, TYPE, table_num from gv$fixed_table where inst_id =
USERENV (' Instance ')
Sql> SELECT * from v$fixed_view_definition where view_name= ' gv$fixed_table ';
View_name view_definition
————————- ——————————————————————————–
Gv$fixed_table Select Inst_id,kqftanam, Kqftaobj, ' TABLE ', indx from X$KQFTA union ALL Select I
Nst_id,kqfvinam, Kqfviobj, ' VIEW ', 65537 from X$KQFVI union ALL Select Inst_id,k
Qfdtnam, Kqfdtobj, ' TABLE ', 65537 from X$KQFDT
The original gv$ is a global view, and v$ is a view of an instance, $X is the data source for all gv$, from gv$ to v$ need to add where inst_id = USERENV (' Instance '). In general, an Oracle database will operate on only one instance, but there can be multiple instances on the RAC that mount and open a database at the same time, and the result on the RAC is:
[Email protected]> SELECT distinct inst_id from gv$session;
inst_id
———-
1
2
4
3
There are four instances of this RAC. Well, the understanding of the instance and the database is deepened again.
Where is the definition of gv_$ and v_$? Originally stored in the $oracle_home/rdbms/admin System management script, found in the Catalog.sql:
–catctl-s Initial Scripts Single Process
@ @cdstrt
@ @cdfixed. sql
@ @cdcore. sql
–catctl-m
@ @cdplsql. sql
@ @cdsqlddl. sql
........................................................................................
Further found in the Cdfixed.sql
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;
Grant SELECT on V_$fixed_table to Select_catalog_role;
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;
Grant SELECT on Gv_$fixed_table to Select_catalog_role;
............................................................................................................................
[Email protected] admin]$ sqlplus Sys/[email protected] as Sysdba
Sql> select * from User_role_privs;
USERNAME granted_role ADM DEF os_
—————————— —————————— — — —
SYS Select_catalog_role Yes Yes NO
So our common v$ is synonymous with v_$, v_$ is based on the real view v$, and the real v$ view is gv$ based on the restrictions inst_id get;
Our common gv$ is synonymous with gv_$, gv_$ based on True view gv$, while the real gv$ view is based on system table x$.

The relationship between Oracle Dynamic view v$,v_$,gv$,gv_$ and x$