v_$datafile & v$datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: v_$datafile & v$datafile

  1. #1
    Join Date
    Mar 2001
    Posts
    286

    Angry

    v_$datafile & v$datafile

    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;

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    V$DATAFILE is PUBLIC SYNONYM and V_$DATAFILE is View.

  3. #3
    Join Date
    Mar 2001
    Posts
    286
    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???????





  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.

    So, in short, the whole chain looks like this:

    (public.)V$DATAFILE -> SYS.V_$DATAFILE -> SYS.V$DATAFILE -> SYS.GV$DATAFILE -> X$KCCFE+X$KCCFN+X$KCVFH

    This same principle is valid (with some minor modifications and exceptions) for all V$ views.

    I hope this explanation didn't confuse even greater confusion than it was initialy ;)....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Mar 2001
    Posts
    286

    Talking

    Jurij,

    You are excellent!!!

    Your explanation is really appreciated.

    dba_admin

  6. #6
    Join Date
    Mar 2001
    Posts
    286

    Question

    One more question:

    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;

    OBJECT_TYPE COUNT(*)
    ------------------ ----------
    SYNONYM 184

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    Mar 2001
    Posts
    286

    Talking

    Thank you!

    SQL> @col_width VIEW_DEFINITION 70
    SQL> @col_width view_name 20
    SQL> @col_wrap_word.sql VIEW_DEFINITION
    SQL> select * from V$FIXED_VIEW_DEFINITION where view_name='V$DATAFILE';

    VIEW_NAME VIEW_DEFINITION
    -------------------- ----------------------------------------------------------------------
    V$DATAFILE 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 GV$DATAFILE where
    inst_id = USERENV('Instance')


    SQL> select * from V$FIXED_VIEW_DEFINITION where view_name='GV$DATAFILE';

    VIEW_NAME VIEW_DEFINITION
    -------------------- ----------------------------------------------------------------------
    GV$DATAFILE select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn),
    to_date(fe.fecrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
    fe.fetsn,fe.ferfn,
    decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'),
    decode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')),
    decode(fe.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12),
    0,'DISABLED',4,'READ ONLY',12,'READ WRITE','UNKNOWN')),
    to_number(fe.fecps), to_date(fe.fecpt,'MM/DD/RR
    HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number(fe.feurs),
    to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
    to_number(fe.fests), decode(fe.fests,NULL,to_date(NULL),
    to_date(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')),
    to_number(fe.feofs),to_number(fe.feonc_scn),
    to_date(fe.feonc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
    fh.fhfsz*fe.febsz,fh.fhfsz,fe.fecsz*fe.febsz,fe.febsz,fn.fnnam,
    fe.fefdb from x$kccfe fe, x$kccfn fn, x$kcvfh fh where
    fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil and fe.fefnh=fn.fnnum and
    fe.fedup!=0 and fn.fntyp=4 and fn.fnnam is not null

    ===========================================

    SQL> select FILE# , CREATION_CHANGE# , CREATION_TIME , TS# , RFILE# ,
    2 STATUS , ENABLED , CHECKPOINT_CHANGE# , CHECKPOINT_TIME,
    3 UNRECOVERABLE_CHANGE#, UNRECOVERABLE_TIME, LAST_CHANGE# , LAST_TIME ,
    4 OFFLINE_CHANGE# , ONLINE_CHANGE# , ONLINE_TIME , BYTES , BLOCKS ,
    5 CREATE_BYTES , BLOCK_SIZE , NAME , PLUGGED_IN from GV$DATAFILE where
    6 inst_id = USERENV('Instance');

    FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
    ---------- ---------------- --------- ---------- ---------- ------- ---------- ------------------ --------- --------------------- --------- ------------ --------- --------------- -------------- --------- ---------- ---------- ------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1 4 27-FEB-00 0 1 SYSTEM READ WRITE 3402654 16-MAR-01 0 07-JAN-01 2937266 2937267 24-FEB-01 266600448 32544 0 8192 C:\ORACLE\ORADATA\ORCL\SYSTEM_TBS01.DBF
    2 4081 27-FEB-00 1 2 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 73400320 8960 0 8192 C:\ORACLE\ORADATA\ORCL\RBS01.DBF
    3 4091 27-FEB-00 2 3 ONLINE READ WRITE 3402654 16-MAR-01 0 17-JAN-01 2937266 2937267 24-FEB-01 96993280 11840 0 8192 C:\ORACLE\ORADATA\ORCL\USERS01.DBF
    4 4099 27-FEB-00 3 4 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 5242880 640 0 8192 C:\ORACLE\ORADATA\ORCL\TEMP01.DBF
    5 4108 27-FEB-00 4 5 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 10485760 1280 0 8192 C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
    6 4117 27-FEB-00 5 6 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 5242880 640 0 8192 C:\ORACLE\ORADATA\ORCL\INDX01.DBF
    7 139597 27-FEB-00 6 7 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 20971520 2560 0 8192 C:\ORACLE\ORADATA\ORCL\DR01.DBF
    8 2816421 18-FEB-01 12 8 ONLINE READ ONLY 3301696 12-MAR-01 0 3301696 12-MAR-01 2937266 2937267 24-FEB-01 2097152 256 2097152 8192 C:\ORACLE\ORADATA\ORCL\USERS2.DBF
    9 2816692 19-FEB-01 13 9 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 1048576 128 1048576 8192 C:\ORACLE\ORADATA\ORCL\USERS03X.DBF
    10 2816693 19-FEB-01 13 10 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 1048576 128 1048576 8192 C:\ORACLE\ORADATA\ORCL\USERS03Y.DBF
    11 3301744 12-MAR-01 14 11 ONLINE READ ONLY 3301839 12-MAR-01 0 3301839 12-MAR-01 0 0 2097152 256 2097152 8192 C:\ORACLE\ORADATA\ORCL\TPTBS_DATA_01_P01.DBF
    12 3301758 12-MAR-01 15 12 ONLINE READ ONLY 3301842 12-MAR-01 0 3301842 12-MAR-01 0 0 2097152 256 2097152 8192 C:\ORACLE\ORADATA\ORCL\TPTBS_INDEX_01_P01.DBF
    13 1771676 26-JAN-01 10 13 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 2097152 256 5242880 8192 C:\ORACLE\ORADATA\ORCL\TEST0126.DBF

    13 rows selected.

    SQL> select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn),
    2 to_date(fe.fecrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
    3 fe.fetsn,fe.ferfn,
    4 decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'),
    5 decode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')),
    6 decode(fe.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12),
    7 0,'DISABLED',4,'READ ONLY',12,'READ WRITE','UNKNOWN')),
    8 to_number(fe.fecps), to_date(fe.fecpt,'MM/DD/RR
    9 HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number(fe.feurs),
    10 to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
    11 to_number(fe.fests), decode(fe.fests,NULL,to_date(NULL),
    12 to_date(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')),
    13 to_number(fe.feofs),to_number(fe.feonc_scn),
    14 to_date(fe.feonc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
    15 fh.fhfsz*fe.febsz,fh.fhfsz,fe.fecsz*fe.febsz,fe.febsz,fn.fnnam,
    16 fe.fefdb from x$kccfe fe, x$kccfn fn, x$kcvfh fh where
    17 fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil and fe.fefnh=fn.fnnum and
    18 fe.fedup!=0 and fn.fntyp=4 and fn.fnnam is not null;

    INST_ID FENUM TO_NUMBER(FE.FECRC_SCN) TO_DATE(F FETSN FERFN DECODE( DECODE(FE. TO_NUMBER(FE.FECPS) TO_DATE(F TO_NUMBER(FE.FEURS) TO_DATE(F TO_NUMBER(FE.FESTS) DECODE(FE TO_NUMBER(FE.FEOFS) TO_NUMBER(FE.FEONC_SCN) TO_DATE(F FH.FHFSZ*FE.FEBSZ FHFSZ FE.FECSZ*FE.FEBSZ FEBSZ FNNAM
    ---------- ---------- ----------------------- --------- ---------- ---------- ------- ---------- ------------------- --------- ------------------- --------- ------------------- --------- ------------------- ----------------------- --------- ----------------- ---------- ----------------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1 1 4 27-FEB-00 0 1 SYSTEM READ WRITE 3402654 16-MAR-01 0 07-JAN-01 2937266 2937267 24-FEB-01 266600448 32544 0 8192 C:\ORACLE\ORADATA\ORCL\SYSTEM_TBS01.DBF
    1 2 4081 27-FEB-00 1 2 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 73400320 8960 0 8192 C:\ORACLE\ORADATA\ORCL\RBS01.DBF
    1 3 4091 27-FEB-00 2 3 ONLINE READ WRITE 3402654 16-MAR-01 0 17-JAN-01 2937266 2937267 24-FEB-01 96993280 11840 0 8192 C:\ORACLE\ORADATA\ORCL\USERS01.DBF
    1 4 4099 27-FEB-00 3 4 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 5242880 640 0 8192 C:\ORACLE\ORADATA\ORCL\TEMP01.DBF
    1 5 4108 27-FEB-00 4 5 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 10485760 1280 0 8192 C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
    1 6 4117 27-FEB-00 5 6 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 5242880 640 0 8192 C:\ORACLE\ORADATA\ORCL\INDX01.DBF
    1 7 139597 27-FEB-00 6 7 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 20971520 2560 0 8192 C:\ORACLE\ORADATA\ORCL\DR01.DBF
    1 8 2816421 18-FEB-01 12 8 ONLINE READ ONLY 3301696 12-MAR-01 0 3301696 12-MAR-01 2937266 2937267 24-FEB-01 2097152 256 2097152 8192 C:\ORACLE\ORADATA\ORCL\USERS2.DBF
    1 9 2816692 19-FEB-01 13 9 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 1048576 128 1048576 8192 C:\ORACLE\ORADATA\ORCL\USERS03X.DBF
    1 10 2816693 19-FEB-01 13 10 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 1048576 128 1048576 8192 C:\ORACLE\ORADATA\ORCL\USERS03Y.DBF
    1 11 3301744 12-MAR-01 14 11 ONLINE READ ONLY 3301839 12-MAR-01 0 3301839 12-MAR-01 0 0 2097152 256 2097152 8192 C:\ORACLE\ORADATA\ORCL\TPTBS_DATA_01_P01.DBF
    1 12 3301758 12-MAR-01 15 12 ONLINE READ ONLY 3301842 12-MAR-01 0 3301842 12-MAR-01 0 0 2097152 256 2097152 8192 C:\ORACLE\ORADATA\ORCL\TPTBS_INDEX_01_P01.DBF
    1 13 1771676 26-JAN-01 10 13 ONLINE READ WRITE 3402654 16-MAR-01 0 2937266 2937267 24-FEB-01 2097152 256 5242880 8192 C:\ORACLE\ORADATA\ORCL\TEST0126.DBF

    13 rows selected.

    SQL>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width