Merge Partition Invalidates Dependent Objects Views, Procedures (Doc ID 846568.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.6 to 9.2.0.8 - Release: 9.2 to 9.2
Information in this document applies to any platform.

Symptoms

Merge Partition operation invalidates dependent objects views, procedures

 create table pt (
     n number
     ,d date
  )
  partition by range (n) (
     partition pt1 values less than (10),
     partition pt2 values less than (20),
     partition ptm values less than (maxvalue)
  )
  /

create or replace view vpt as
select * from pt;

create or replace procedure ppt as
m number;
begin
select max(n) into m from pt;
dbms_output.put_line(m);
end ppt;
/

select object_name ,object_type ,status from user_objects
where object_name in ('VPT', 'PPT') ;

-- MERGE PARTITIONS
alter table pt merge partitions pt1, pt2 into partition pt2;

select object_name ,object_type ,status from user_objects
where object_name in ('VPT', 'PPT') ;

OBJECT_NAME OBJECT_TYPE STATUS
----------- ----------- --------------
PPT         PROCEDURE   INVALID
VPT         VIEW        INVALID


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms