Re: packages becoming invalid at random
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 2 Oct 2009 12:08:11 -0700 (PDT)
> If your dependent views, packages, procedures, or functions reference
> the partitioned tables directly (without synonyms), then partition
> management operations (i.e. MERGE|RENAME|DROP PARTITION) will not
> invalidate them, at least in 10gR2.
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
