|
Lyall, Not sure if this is relevant or not, but it sure surprised me... If you have partitioned tables, and you perform operations like MERGE|RENAME|DROP PARTITION on them, and you also have dependent compile objects like views, packages, procedures, or function referencing these tables, then the following might be relevant... 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. In earlier versions (i.e. v8.0.x, v8.1.x, v9.0.x, v9.2.x), many partition management operations would invalidate and some would not, but Oracle cleaned this up by v10.2.x, at least according to my testing. However, if your dependent views, packages, procedures, or functions reference the partitioned tables through synonyms, then the synonyms become invalidated by partition management operations. Of course, this invalidation will also cascade down to the dependent objects that reference the synonym. So, following Mark's advice, check the DBA_DEPENDENCIES data-dictionary view to see if your compiled objects which are going invalid are referencing partitioned tables. If so, check to see if they are referencing the table directly, or through a synonym. If the latter, then check to see if partition management operations are occurring around the time your invalidations are occurring. If anyone is interested in using a SQL*Plus script I put together long ago to test whether partition management commands cause invalidations in a particular version of Oracle, I'll be glad to share it with them if they request offline from the list. Hope this helps... Tim Gorman consultant - Evergreen Database Technologies, Inc. P.O. Box 630791, Highlands Ranch CO 80163-0791 website = http://www.EvDBT.com/ email = Tim@xxxxxxxxx mobile = +1-303-885-4526 fax = +1-303-484-3608 Yahoo IM = tim_evdbt Powell, Mark D wrote: -- //www.freelists.org/webpage/oracle-l |
