Dependencies and invalidations.

In Oracle RDBMS 10.2 it is practically impossible to invalidate a PL/SQL object. I tried with adding indexes, dropping primary key and analyzing the underlying tables, but the PL/SQL procedure build on top of the table remained valid. Oracle 9i PL/SQL objects were much more sensitive. Is anybody aware of any change with regard to dependency tracking in Oracle 10.2? Here is what I did:

set termout on
set echo on
set trimspool on
set trimout on
drop table scott.emp1 purge;
drop function sum_nomgr;
spool /tmp/invalid.lst
create table emp1 as select * from emp;

create or replace function sum_nomgr (d number) return number  as
total_sal number(10,3);
begin
select sum(sal) into total_sal
from emp1
where deptno=d and
job != 'MANAGER';
return(total_sal);
end;
/

select object_name, status
from user_objects
where object_name in ('EMP1','SUM_NOMGR')
/

alter table emp1 add constraint emp1_pk primary key(empno); select object_name, status
from user_objects
where object_name in ('EMP1','SUM_NOMGR')
/

analyze table emp1 compute statistics for table for all indexed columns; select object_name, status
from user_objects
where object_name in ('EMP1','SUM_NOMGR')
/

create index emp1_deptno_i on emp1(deptno); analyze table emp1 compute statistics for table for all indexed columns;

select object_name, status
from user_objects
where object_name in ('EMP1','SUM_NOMGR')
/

alter table emp1 drop constraint emp1_pk;

select object_name, status
from user_objects
where object_name in ('EMP1','SUM_NOMGR')
/

spool off

Here is the produced spool file:

SQL> create table emp1 as select * from emp;

Table created.

SQL>
SQL> create or replace function sum_nomgr (d number) return number   2 as
  3 total_sal number(10,3);
  4 begin
  5 select sum(sal) into total_sal
  6 from emp1
  7 where deptno=d and
  8 job != 'MANAGER';
  9 return(total_sal);
 10 end;
 11 /

Function created.

SQL> select object_name, status
  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME                         STATUS
----------------------------------- -------
EMP1                                VALID
SUM_NOMGR                           VALID

SQL> alter table emp1 add constraint emp1_pk primary key(empno);

Table altered.

SQL> select object_name, status
  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME                         STATUS
----------------------------------- -------
EMP1                                VALID
SUM_NOMGR                           VALID

SQL> analyze table emp1 compute statistics for table for all indexed columns;

Table analyzed.

SQL> select object_name, status
  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME                         STATUS
----------------------------------- -------
EMP1                                VALID
SUM_NOMGR                           VALID

SQL> create index emp1_deptno_i on emp1(deptno);

Index created.

SQL> analyze table emp1 compute statistics for table for all indexed columns;

Table analyzed.

SQL>
SQL> select object_name, status
  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME                         STATUS
----------------------------------- -------
EMP1                                VALID
SUM_NOMGR                           VALID

SQL> alter table emp1 drop constraint emp1_pk;

Table altered.

SQL>
SQL> select object_name, status
  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME                         STATUS
----------------------------------- -------
EMP1                                VALID
SUM_NOMGR                           VALID

SQL> spool off

As you can see, the SUM_NOMGR function remains valid all the time, throughout my little exercise. In oracle 9i, the function would go invalid for each of the DDL operations on the table. Oracle 10.2 does record the dependencies properly:

SQL> select referenced_owner,referenced_name,referenced_type   2 from user_Dependencies
  3 where name='SUM_NOMGR' and
  4 type='FUNCTION';

REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE

------------------------------ --------------- -----------------
SYS                            STANDARD        PACKAGE
SYS                            SYS_STUB_FOR_PU PACKAGE
                               RITY_ANALYSIS

SCOTT                          EMP1            TABLE

SQL> What is going on here? Why is the function not becoming invalid, even after copious amount of DDL applied to EMP1?

-- 
http://www.mgogala.com