Re: packages becoming invalid at random

That's an interesting point. I never thought about it. I tested it with subpartitions, and that's true as well.

There's a minor annoyance in a special case with this fact that a dependent object stays valid when the base table goes through partition management. Here's a test in 10.2.0.4 where the view explicitly specifies a partition, which is later renamed.

create table testpart (x int) partition by range (x)  (partition p1 values less than (10),
  partition p2 values less than (maxvalue)  );
create view v_testpart as select * from testpart partition(p1); select object_name, subobject_name, status, timestamp from user_objects
where object_name in ('TESTPART', 'V_TESTPART'); alter table testpart rename partition p1 to p1old; desc v_testpart

SQL> select * from v_testpart;
select * from v_testpart

              *
ERROR at line 1:
ORA-02149: Specified partition does not exist

SQL> select object_name, subobject_name, status, timestamp   2 from user_objects
  3 where object_name in ('TESTPART', 'V_TESTPART');

OBJECT_NAM SUBOB STATUS TIMESTAMP

---------- ----- ------- -------------------
TESTPART   P1OLD VALID   2009-10-02:13:43:07
TESTPART   P2    VALID   2009-10-02:13:41:16
TESTPART         VALID   2009-10-02:13:43:07
V_TESTPART       VALID   2009-10-02:13:41:54

In this case, I would rather the view be invalid. If I have a script to check for invalid objects based on dba_objects.status, I'd like this view to be caught. (A view based on a synonym doesn't have this problem.)

Yong Huang       

--
http://www.freelists.org/webpage/oracle-l