-
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?)
"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
-
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.
-- Dilip
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|