Please look at the following two statements which Oracle uses to create v$datafile and v_$datafile.
It looks like V$datafile is a synonym of v_$datafile. However, v_$datafile is a view built from selecting v$datafile. This is a "circular" definition...... How can this be???????
create public synonym V$DATAFILE
for SYS.V_$DATAFILE;
create or replace view sys.v_$datafile as
select "FILE#","CREATION_CHANGE#","CREATION_TIME",
"TS#","RFILE#","STATUS","ENABLED",
"CHECKPOINT_CHANGE#","CHECKPOINT_TIME","UNRECOVERABLE_CHANGE#","UNRECOVERABLE_TIME",
"LAST_CHANGE#","LAST_TIME","OFFLINE_CHANGE#",
"ONLINE_CHANGE#","ONLINE_TIME","BYTES","BLOCKS",
"CREATE_BYTES","BLOCK_SIZE","NAME",
"PLUGGED_IN"
from v$datafile;
Yes, I know this:
V$DATAFILE is PUBLIC SYNONYM and V_$DATAFILE is View
But, my question is this:
If you read the definition of these two objects carefully, you will see that they are circular definition.
V$datafile is a synonym of v_$datafile. However, v_$datafile is a view built from selecting v$datafile. This is a "circular" definition...... How can this be???????
Well, actually there is no circular definition. But, true, Oracle made all this pretty confusing. The part that causes most confusion is that there are actually two objects named V$DATAFILE . One is a public view, the other is a "fixed" view under schema SYS.
Let's see what is each part of this chain based on.
1.
*Public* synonym V$DATAFLE. This is a public synonym throu which ve usually query the data regarding datafiles. It is the synonym for the *regular* view SYS.V_$DATAFILE.
2.
SYS.V_$DATAFILE. This is normal view under schema SYS. It is based on the internal (fixed) view called SYS.V$DATAFILE. This is the part where you see the circularity: (public)V$DATAFILE -> SYS.V_$DATAFILE -> SYS.V$DATAFILE. But remember - the first and the last element in this 3-element chain are not the same (read further for the explanation).
3.
SYS.V$DATAFILE. This is a view, but not the "normal" view, it is so called "fixed" view. Usually fixed views are based directly on top of special internal C structures residing in SGA (so called "fixed" tables"). Well, nothing can be simple in Oracle, so in this particular case this fixed view is based on another fixed view, called SYS.GV$DATAFILE. BTW, you can't reference fixed views directly if you are not connected as SYS or INTERNAL. For example, if you are connected as SYSTEM, the following will not work even if you prefixe the name of the view by schema owner:
SELECT * FROM sys.v$datafile;
But if you are connected as SYS the above query will work.
4.
SYS.GV$DATAFILE. This is another fixed view (Aaain, do not confuse this fixed view with public synonym GV$DATAFIE.), based on fixed tables X$KCCFE, X$KCCFN and X$KCVFH. So the join on those three fixed tables (C structures in SGA) are at the bottom of this complicated chain below the public view V$DATAFILE.
How do you find the definition of V$ views under SYS schema?
I did not see any view called 'V$......' under SYS. All V$ objects are just public synonyms. How did you find out the definition of V$ views? Thank you!
SQL> connect sys/change_on_install
Connected.
SQL> SELECT * FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'V$%';
no rows selected
SQL> SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'V$%'
2 GROUP BY OBJECT_TYPE;
You can't actually see them, as I said they are special (so-called fixed) views. But you gan see how they are defined by looking into a V$FIXED_VIEW_DEFINITION.
SELECT view_name, view_definition FROM V$FIXED_VIEW_DEFINITION
WHERE view_name = 'V$DATAFILE;
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Reply With Quote
Bookmarks