difference between views and tables

[yls177]

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?

[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

[DaPi]

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?)

[yls177]

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.

[davey23uk]

a trip to the manuals is required for you me thinks

[mrchrispy]

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).

[reydp]

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;

[yls177]

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.

[patel_dil]

1. ??

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

[yls177]

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.