Dynamic Performance Views
Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance.
Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them..
alter table x$ksmsp add sriram varchar2(30);
alter table x$ksmsp add sriram varchar2(30)
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views.
We are going to discuss about two types of views,
- V$ Views
- GV$ Views
Almost For Every V$View, there is a GV$View which will hold the instance-specific Information in a Multi-Instance Environment like RAC.
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.C:\Users\DBA>sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 19 13:48:28 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> desc v$version
Name Null? Type
—————————————– ——– —————————-
BANNER VARCHAR2(80)
CON_ID NUMBERSQL> desc gv$version
Name Null? Type
—————————————– ——– —————————-
INST_ID NUMBER
BANNER VARCHAR2(80)
CON_ID NUMBERSQL>
These Dynamic Views are owned by Sys User and These views are constructed based on the Oracle Internal Memory Structures (X$ Tables/Views). We cannot alter or Update on these views. These X$ structures Get Created when the Instance started and Will get destroyed when you shut down the DB.
V$FIXED_VIEW
V$FIXED_TABLE displays all dynamic performance tables, views, and derived tables in the database.
V$FIXED_VIEW_DEFINITION
This View contains the definitions of all the fixed views (views beginning with V$). Use this table with caution. Oracle tries to keep the behavior of fixed views the same from release to release, but the definitions of the fixed views can change without notice.
As described in the Above Example, Dynamic Performance views are based on Internal Memory Structures X$.These X$ tables/ views are platform-specific & the No of tables are kept on increasing By Oracle Version.The Oracle kernel consists of layers. The X$ table names contain an abbreviation for the particular kernel layer.
The No# Of Views are dependent on Oracle version and the OS Specific. These Views Get populated only when the Instance Started. These Views Contains information about all the parameters, performance etc info. As We all know Oracle has documented some of the Dynamic Views (V$_ objects) which are in handy to get the info from your instance these Dynamic Views are Based on GV$_ VIEWS which includes the data for Multiple Instances. GV$ are Designed from these Fixed Views. View Columns might be similar from version to version whereas Underlying Fixed X$ objects might different.
ind> select * from V$FIXED_VIEW_DEFINITION 2 where view_name='V$FIXED_VIEW_DEFINITION'; VIEW_NAME VIEW_DEFINITION ------------------------------ -------------------------------------------------- V$FIXED_VIEW_DEFINITION select VIEW_NAME , VIEW_DEFINITION from GV$FIXED_ VIEW_DEFINITION where inst_id = USERENV('Instance' ) 1 row selected. ind> select * from V$FIXED_VIEW_DEFINITION 2 where view_name='GV$FIXED_VIEW_DEFINITION'; VIEW_NAME VIEW_DEFINITION ------------------------------ -------------------------------------------------- GV$FIXED_VIEW_DEFINITION select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx = t.indxMicrosoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.C:\Users\DBA>sqlplus sys as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 19 10:21:40 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> select count(*) from gv$fixed_table where type=’TABLE’ and name like ‘X$%’;
COUNT(*)
———-
1144SQL>
Why do we call them “Fixed”?
As mentioned, All these Objects are owned by SYS and DDL or DML Operations are not permitted/supported on these. Even if we try to perform, Operation will end up with error ORA-02030: can only select from fixed tables/views.
These objects contain information about the current configuration of the instance, information about the sessions connected to the instance and performance information.
Using Views like (g) v$fixed_view_definition one can get the Actual Creation script of these Fixed views which are harmful in case of replacing the Existing or Create New views, which will end up with Errors or incorrect data as These Views are for Oracle not for Others. DBA/Developers should not create Objects using actual “V$_ “, they need to use v$ Objects instead of v$_ or X$, as The Definition of the base view may change in OS/DB software versions.
Conclusion: The X$ views are an Oracle internal interface not exposed for customer use; customers should not be using it unless directed to do so by the support.Customer use without support guidance is unsupported. The X$ views are completely undocumented, and Oracle does not provide any assistance for deriving the meaning of the contents of the X$ structures. since V$ views are dynamic and consistency is not guaranteed, Oracle does not support joins or sorts of dynamic views.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
