invalid objects and partitioning

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 Received on Wed Feb 09 2005 - 14:08:09 CST