What happens to oracle views if we alter a table structure by adding a new column?

Assume that the structure of the table on which a view is based is modified. When you describe the view by using iSQL*Plus DESCRIBE command, you get an error message that states that the object is invalid to describe. This is because the command is not a SQL command and, at this stage, the view is invalid because the structure of its base table is changed. If you query the view now, the view is recompiled automatically and you can see the result if it is successfully recompiled.

Note:- In the case of local dependencies, the objects are on the same node in the same database. The Oracle server automatically manages all local dependencies, using the database’s internal “depends-on” table. When a referenced object is modified, the dependent objects are invalidated. The next time an invalidated object is called, the Oracle server automatically recompiles it.

For better understanding go through an example below:

SQL> create table testtab(col1 number,col2 number);

Table created.

SQL> insert into testtab values(1,1);

1 row created.

SQL> commit;

Commit complete.

SQL> create view testtab_view as select * from testtab;

View created.

SQL> select * from testtab_view;

     COL1      COL2

--------- ---------

        1         1

       

SQL> select object_name,status from user_objects where object_name='TESTTAB_VIEW';

OBJECT_NAME        STATUS

--------------- -----------

TESTTAB_VIEW       VALID

SQL> ALTER TABLE TESTTAB_VIEW ADD (COL3 NUMBER);

ALTER TABLE TESTTAB_VIEW ADD (COL3 NUMBER)

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> ALTER TABLE TESTTAB ADD (COL3 NUMBER);

Table altered.

SQL> select object_name,status from user_objects where object_name='TESTTAB_VIEW';

OBJECT_NAME        STATUS

--------------- -----------

TESTTAB_VIEW       INVALID

SQL> DESC TESTTAB_VIEW;

ERROR:

ORA-24372: invalid object for describe

SQL> SELECT * FROM TESTTAB_VIEW;

     COL1      COL2

--------- ---------

        1         1

SQL> select object_name,status from user_objects where object_name='TESTTAB_VIEW';

OBJECT_NAME        STATUS

--------------- -----------

TESTTAB_VIEW       VALID

Note:- Your view definition is select * from testtab; Even though it doesn’t display col3 why because this column was not there while creation of a view, so view definition doesn’t contain that column in his definition.

Note: The data types of the columns in the base table do not matter at the

time of compiling the view. That means in the above example even columns

C1 is of VARCHAR2 type in table T1 still the view will be made valid. Because

Oracle precisely looks for columns C1 and C2 in table T1and not for any

specific data type. For better understanding Check below example:

SQL> create table test_tab (col1 number,col2 number);

Table created.

SQL> create view test_view as select * from test_tab;

View created.

SQL> desc test_view;

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ---------------

 COL1                                                           NUMBER

 COL2                                                           NUMBER

SQL> drop table test_tab;

Table dropped.

SQL> create table test_tab (col1 number,col2 varchar2(100),col3 number);

Table created.

SQL> desc test_view;

ERROR:

ORA-24372: invalid object for describe

SQL> select * from test_view;

no rows selected

SQL> desc test_view;

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ---------------

 COL1                                                           NUMBER

 COL2                                                           VARCHAR2(100)