On a DB schema, there are many database objects like tables, views, procedures, packages etc.
Here is a case study...
- You want to alter a table for some reason. As a result of running ALTER statement over the table, all dependend objects like Views, Packages gets invalidated irrespective of the fact that change affects/not affects the dependend objects.
- Eg: Assume a table ..
SQL> create table test_foo
( colA varchar2(2)
, colB varchar2(2)
, colC varchar2(2)
);
SQL> create view v_test_foo
as select colA, colB, colC from test_foo;
SQL> select object_name, object_type, status
from user_objects
where object_name in ('TEST_FOO', 'V_TEST_FOO');
----------------------------------------
object_name object_type Status
-----------------------------------------
V_TEST_FOO VIEW VALID
TEST_FOO TABLE VALID
SQL> alter table test_foo add colNew varchar(2);
SQL> select object_name, object_type, status
from user_objects
where object_name in ('TEST_FOO', 'V_TEST_FOO');
----------------------------------------
object_name object_type Status
-----------------------------------------
V_TEST_FOO VIEW INVALID
TEST_FOO TABLE VALID
How do you recompile these ?I use...
SQL>
exec dbms_utility.compile_schema('USERNAME');-- Substitute an appropriate USERNAME above.
So, how does it behave in Oracle 9i and Oracle 10g ?
The behavior is different in both versions of the Oracle.
Oracle 10g: As you expect, all relevant invalid objects are recompiled and becomes Valid.
Oracle 9i: You should be cautitious.. as VIEWs are not validated.
You have to explicitly compile VIEWs as follows.
ALTER VIEW v_test_foo COMPILE;
