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?

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

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

Quote:

---

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.

a trip to the manuals is required for you me thinks

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

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;

;)

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.

1. ??

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

Quote:

---

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.