Re: invalid objects and partitioning

On 9 Feb 2005 12:08:09 -0800, "mr" <rangwalamustafa_at_hotmail.com> 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

$ORACLE_HOME/rdbms/admin/utlrp will recompile all invalid objects in correct order. You need to be connected as any user with SYSDBA privilege.

You are saying they become invalid due to ALTER TABLE commands. You aren't referring explicitly to the partitions in your code?

You are also saying you have remote procedures referring to those partitioned tables. As you can call procedures remotely, can't keep those procedures local to the database the partitioned tables are in?

Hth

--
Sybrand Bakker, Senior Oracle DBA