Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: invalid objects and partitioning

Re: invalid objects and partitioning

From: DA Morgan <>
Date: Wed, 09 Feb 2005 19:18:54 -0800
Message-ID: <1108005366.777311@yasure>

mr wrote:

> I have a need to partition several tables using a date column. Each
> table may be partitioned into say 7day period, 15day period, 6month
> period. In other words, I am trying to preserve data for 7days etc. I
> have searched most of the partitioning references/articles that relate
> to this and so far have concluded that a range partition will work for
> me.
> Unfortunately, upon trying this techique for couple of months on our
> database tables, we have noticed that there is a consistent problem of
> invalid-objects. The reason this happens is obvious since each time we
> rotate out a partition using the 'alter table drop partition ' command,
> thereby invoking a DDL, and therefore resulting in all dependent
> objects/stored-procedures to become invalid.
> Oracle usually consistently compiles these on first access, but remote
> objects that may have 'dblinked' references to these partitioned tables
> never get rebuilt automatically and therefore remain invalid and
> unuseable.
> If this was a case where I had one or two 'audit' tables then we could
> rearchitect the schema to eliminate dependecies across server instances
> or possible eliminate dependencies within the server itself to other
> objects, but this is not an option since I am partitionoing about
> 70-100 tables that grow consistently and must be kept 'in-check' by
> pruning them ever so often.
> Maybe partitioning is the wrong tool for this need. Your thoughts and
> possibly other approaches to manage this situation are appreciated.
> -MR

And the version of Oracle is?

Local or global indexes?

Have you looked at the excellent examples of doing this at

Daniel A. Morgan
University of Washington
(replace 'x' with 'u' to respond)
Received on Wed Feb 09 2005 - 21:18:54 CST

Original text of this message