Oracle 9i Result:
SQL> drop table t;
Table dropped.
SQL> create table t (c number);
Table created.
SQL> create view v1
as select count(1) count from t;
View created.
SQL> create view v2
as select count(*) count from t;
View created.
SQL> select object_name, object_type, status from user_objects
where (object_name in ('V1','V2'))
and object_type = 'VIEW';
OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------- --------
V2 VIEW VALID
V1 VIEW VALID
Add another column in the table.
SQL> alter table t add ( c2 number);
Table altered.
SQL> select object_name, object_type, status
2 from user_objects
3 where (object_name in ('V1','V2'))
4 and object_type = 'VIEW';
OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------ -------------
V2 VIEW INVALID
V1 VIEW INVALID
Both the views are Invalid in Oracle 9i environment.
Oracle 11g Result:
SQL> drop table t;
Table dropped.
SQL> create table t (c number);
Table created.
SQL> create view v1
2 as
3 select count(1) count from t;
View created.
SQL> create view v2
2 as
3 select count(*) count from t;
View created.
SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';
OBJECT_NAME OBJECT_TYPE STATUS
----------- -------------- -------------
V1 VIEW VALID
V2 VIEW VALID
Add another column in the table.
SQL> alter table t add(c2 number);
Table altered.
SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';
OBJECT_NAME OBJECT_TYPE STATUS
------------ --------------- -------------
V1 VIEW INVALID
V2 VIEW VALID
The V1 became Invalid. but V2 is valid
Thursday, April 8, 2010
Count with View in Oracle 9i vs 11g
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment