difference between views and tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: difference between views and tables

  1. #1
    Join Date
    Oct 2002
    Posts
    391

    difference between views and tables

    Hi. I know the differences are as belows (exmaple, v$tablespaces, dba_tablespace)

    1) Views pull information from various tables.

    2) You cant update views.

    3) dba_tablespace is static while v$tablespaces are dynamic


    My questions

    1) Tables or views availability depends on the startup phase?

  2. #2
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    u can also update views if u have granted privileges on it.
    Yes, they are accessible only if ur database is started up and open.

    Thanks

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The structure of the view will decide if it is updatable or not. The manuals talk about "key preserved . . . "

    I think of it as: If I can write an update such that ONE row of ONE table is updated, then that update is allowed and can be generalised to many rows. (Is that a ROT? Is there a flaw it it?)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Oct 2002
    Posts
    391
    Originally posted by kris123
    u can also update views if u have granted privileges on it.
    Yes, they are accessible only if ur database is started up and open.

    Thanks
    Hi. I could be very wrong but just want to clarify that in the different stages of startup, these views and tables are subject to availability?


    In additional, there are 3 stages of startup as belows

    1) startup nomount (read the initsid.ora)

    2) startup mount (only the control file is read)

    3) startup open (all the datafiles)

    Please advise.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    a trip to the manuals is required for you me thinks

  6. #6
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    You question isn't very clear but I think what you are trying to ask is what (data dictionary) static tables and dynamic views are visible at various stages of database startup.

    eg.
    startup nomount
    v$session - yes
    dba_tablespaces - no

    alter database mount;
    v$session - yes
    dba_tablespaces - no

    alterdatabase open;
    v$session - yes
    dba_tablespaces - yes

    Have a play on your own DB (and then hit those books to figure out why).

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Or yls177 means this:

    startup nomount;
    select * from v$sga;

    alter database mount;
    select * from v$datafile;

    alter database open;
    select * from dba_blah_blah;

    ---------------

  8. #8
    Join Date
    Oct 2002
    Posts
    391
    Hi..

    Thanks for all who taught me. i tried and get the below

    ORA-01219: database not open: queries allowed on fixed tables/views only

    Cheers!

    01219, 00000, "database not open: queries allowed on fixed tables/views only"
    // *Cause: A query was issued against an object not recognized as a fixed
    // table or fixed view before the database has been opened.
    // *Action: Re-phrase the query to include only fixed objects, or open the
    // database.

    Just 2 more confirmation

    1) Since the above indicates that there are fixed views, and with my example, v$session can be selected in the startup nomount stage, therefore, it is not a fixed view since it can only be selected after a database open.

    2) what are the other fixed views?

    Please advise.

  9. #9
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    1. ??

    2. Query V$FIXED_TABLE for all fixed tables and fixed views.
    -- Dilip

  10. #10
    Join Date
    Oct 2002
    Posts
    391
    Originally posted by patel_dil
    1. ??

    2. Query V$FIXED_TABLE for all fixed tables and fixed views.
    Hi

    you have helped me with qns 1 from the answer that u have given to qns 2.


    SQL> select * from v$fixed_table where name='V$SESSION';

    NAME OBJECT_ID TYPE TABLE_NUM
    ------------------------------ ---------- ----- ----------
    V$SESSION 4294950919 VIEW 65537


    Therefore, v$session is a fixed table view.

    Cheers!

    One last qns, is to find out those not fixed table views.

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