how to find fixed underlying table for particular v$ parameter


If you want to find underlying base or fixed table from where all data in v$ view is coming from, you can use the below query.

SQL> select view_definition
  from v$fixed_view_definition where view_name=’V$name_of_view’;
for example, if i want to check the fixed table info for view v$backup
query would be
SQL> select view_definition from v$fixed_view_definition where lower (view_name)=’v$backup’;

VIEW_DEFINITION
——————————————————————————————————————————————————————————————————–
select FILE# , STATUS , CHANGE# , TIME from GV$BACKUP where inst_id = USERENV(‘Instance’)

let’s check the fix table for this view gv$backup
SQL> select view_definition from v$fixed_view_definition where lower (view_name)=’gv$backup’;

VIEW_DEFINITION
——————————————————————————————————————————————————————————————————–
select inst_id,hxfil, decode(hxerr, 0,decode(bitand(fhsta, 1), 0,’NOT ACTIVE’,’ACTIVE’), 1,’FILE MISSING’, 2,’OFFLINE NORMAL’, 3,’NOT VERIFIED’, 4,’FILE NOT FOUND’, 5,’CANNOT OPEN FILE’, 6,’CANNOT REA
D HEADER’, 7,’CORRUPT HEADER’, 8,’WRONG FILE TYPE’, 9,’WRONG DATABASE’, 10,’WRONG FILE NUMBER’, 11,’WRONG FILE CREATE’, 12,’WRONG FILE CREATE’, 16,’DELAYED OPEN’, ‘UNKNOWN ERROR’), to_number(fhbsc),
to_date(fhbti,’MM/DD/RR HH24:MI:SS’,’NLS_CALENDAR=Gregorian’) from x$kcvfhonl

so now we know this all info comes from x$kcvfhonl.
there is one more way we can check the same information, but that would be the ideal approach when we know we can get the same with less effort.
SQL> set autot stat
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot on exp
SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ——————
1 NOT ACTIVE 1.2388E+11 08-APR-16
2 NOT ACTIVE 1.2388E+11 08-APR-16
3 NOT ACTIVE 1.2388E+11 08-APR-16
4 NOT ACTIVE 1.2388E+11 08-APR-16
5 NOT ACTIVE 1.2388E+11 08-APR-16
6 NOT ACTIVE 1.2388E+11 08-APR-16
7 NOT ACTIVE 1.2388E+11 08-APR-16
8 NOT ACTIVE 1.2388E+11 08-APR-16
9 NOT ACTIVE 1.2388E+11 08-APR-16
10 NOT ACTIVE 1.2388E+11 08-APR-16
11 NOT ACTIVE 1.2388E+11 08-APR-16

FILE# STATUS CHANGE# TIME
———- —————— ———- ——————
155 NOT ACTIVE 1.2388E+11 08-APR-16
156 NOT ACTIVE 1.2388E+11 08-APR-16
157 NOT ACTIVE 1.2388E+11 08-APR-16
158 NOT ACTIVE 1.2388E+11 08-APR-16
159 NOT ACTIVE 1.2388E+11 08-APR-16
160 NOT ACTIVE 1.2388E+11 08-APR-16
161 NOT ACTIVE 1.2388E+11 08-APR-16
162 NOT ACTIVE 1.2388E+11 08-APR-16
163 NOT ACTIVE 1.2388E+11 08-APR-16
164 NOT ACTIVE 1.2388E+11 08-APR-16
165 NOT ACTIVE 1.2388E+11 08-APR-16

FILE# STATUS CHANGE# TIME
———- —————— ———- ——————
166 NOT ACTIVE 1.2388E+11 08-APR-16
167 NOT ACTIVE 1.2388E+11 08-APR-16
168 NOT ACTIVE 1.2388E+11 08-APR-16
169 NOT ACTIVE 1.2388E+11 08-APR-16
170 NOT ACTIVE 1.2388E+11 08-APR-16
171 NOT ACTIVE 1.2388E+11 08-APR-16
172 NOT ACTIVE 1.2388E+11 08-APR-16
173 NOT ACTIVE 1.2388E+11 08-APR-16
174 NOT ACTIVE 1.2388E+11 08-APR-16
175 NOT ACTIVE 1.2388E+11 08-APR-16
176 NOT ACTIVE 1.2388E+11 08-APR-16

FILE# STATUS CHANGE# TIME
———- —————— ———- ——————
177 NOT ACTIVE 1.2388E+11 08-APR-16
178 NOT ACTIVE 1.2388E+11 08-APR-16
179 NOT ACTIVE 1.2388E+11 08-APR-16
180 NOT ACTIVE 1.2388E+11 08-APR-16
181 NOT ACTIVE 1.2388E+11 08-APR-16

:
:
:
: And so on

181 rows selected.

Execution Plan
———————————————————-
Plan hash value: 591542025

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 74 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$KCVFHONL | 1 | 74 | 0 (0)| 00:00:01 |
——————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter(“INST_ID”=USERENV(‘INSTANCE’))

so here from explain plan we can get the same info.

I hope it was useful 🙂 Thanks for visiting the blog, keep learning and keep sharing.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s