IMPACT OF CHANGING REFERENCED OBJECT ON VIEW
Prior 11g, if we add any column to the base table of a view then the view got invalidated. From 11g onward this problem has been fixed. Oracle automatically compiled the invalid object when it is called for execution.
CREATE OR REPLACE VIEW VW_EMPLOYEES_COPY_1 AS SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE, DEPARTMENT_ID FROM EMPLOYEES_COPY;
CREATE OR REPLACE VIEW VW_EMPLOYEES_COPY_2 AS SELECT * FROM EMPLOYEES_COPY;
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'VW_EMPLOYEES_COPY_1', 'VW_EMPLOYEES_COPY_2');
ALTER TABLE EMPLOYEES_COPY ADD (LEAVE_TAKEN NUMBER);
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'VW_EMPLOYEES_COPY_1', 'VW_EMPLOYEES_COPY_2');
So from above example we can understand that adding a new column in the reference table doesn't invalidate the views.
Now we will check the impact of modifying any existing column definition on view.
ALTER TABLE EMPLOYEES_COPY MODIFY (JOB_ID VARCHAR2(50));
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'VW_EMPLOYEES_COPY_1', 'VW_EMPLOYEES_COPY_2');
Now we can see that the view VW_EMPLOYEES_COPY_2 has invalidated but the view VW_EMPLOYEES_COPY_1 is still valid. The reason is that the column JOB_ID is not included in the view definition in VW_EMPLOYEES_COPY_1. So its status is valid. But the view VW_EMPLOYEES_COPY_2 has been invalidated because it has included all the columns in its definition.
RELATED TOPICS:
