Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: invalid objects and partitioning

From: DA Morgan <damorgan_at_x.washington.edu>
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 http://asktom.oracle.com?

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US