DBA_DEPENDENCIES: 11gR2 fine-grained dependency tracking -- which columns??
DBA_DEPENDENCIES is great, but I need to know the dependent columns. Given that 11g supports fine-grained dependency tracking... for example:
create view emp_view as
select emp_no, emp_name
from employees
where emp_no in (select emp_no from employees where hire_date > sysdate-2000)
;
EMP_VIEW only exposes EMP_NO and EMP_NAME.
If I drop the column EMPLOYEES.HIRE_DATE, Oracle will not invalidate objects that reference just EMP_NO and EMP_NAME, but in this case Oracle immediately invalidates EMP_VIEW because the view definition contains HIRE_DATE.
Where does Oracle store this column dependency? Only at the table/view/function level in DBA_DEPENDENCIES. Maybe in an X$ table somewhere?
Or does Oracle do an "on the fly" check for the dropped column in all dependent objects (seems messy).
Before I change any table or view, I want to know what else I may have to change without finding out the hard way. I can easily look at the view's column list, but if I have to go into the view text, or the function text... messy! Must be a better way.
Thanks.
BB