Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

partition exchange error on table with nested table

dario-2280378Aug 17 2011 — edited Aug 18 2011
On Oracle 11.2.0.1, I have a partitioned table with some partitions that need to be 'archived' (in terms of moving partitions to an 'archive' table).

I have a source table like:
CREATE TABLE IS_PODACI245
(
  ID_OBJEKTA_IDENTIFIKACIJA  NUMBER(10),
  ID_OBJEKTA                 NUMBER(20),
  DATUM                      TIMESTAMP(6)       NOT NULL,
  TZ                         NUMBER(3),
  DATA1                      NUMBER(10),
  DATA2                      NUMBER(6),
  DATA3                      NUMBER(10),
  DATA4                      NUMBER,
  DATA5                      T_NTCIP_CLIMATE_TABLE
)
NESTED TABLE DATA5 STORE AS IS_PODACI245_STORE_TABLE
TABLESPACE DATA
PARTITION BY RANGE (DATUM)
(  
  PARTITION P_201107 VALUES LESS THAN (TIMESTAMP' 2011-08-01 00:00:00')
    LOGGING
    NOCOMPRESS 
    TABLESPACE DATA,  
...
  PARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE)
    LOGGING
    NOCOMPRESS 
    TABLESPACE DATA
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX IDX_IS_PODACI245_KOMPLEKS ON IS_PODACI245
(ID_OBJEKTA_IDENTIFIKACIJA, ID_OBJEKTA, DATUM)
  TABLESPACE DATA
LOCAL (  
  PARTITION P_201107
    LOGGING
    NOCOMPRESS 
    TABLESPACE DATA,  
....
  PARTITION P_MAXVALUE
    LOGGING
    NOCOMPRESS 
    TABLESPACE DATA
)
NOPARALLEL;

CREATE OR REPLACE TYPE t_ntcip_climate_table as table of t_ntcip_climate_fmt;

CREATE OR REPLACE TYPE t_ntcip_climate_FMT as object
(  dev_index number(6)
,   dev_description varchar2(512)
,   dev_type number(10)
,   dev_status number(10)
,   dev_mfr_status varchar2(512)
,   dev_active number(3)
,   dev_test_activation number(10)
);
/
I would like to make exchange partition using stage table, and everything is going fine on all tables, but only on a few of them (listed source is one of them, and they're only tables with nested tables wihin), where I get an error.. but sometimes ;)

on a statement like:
ALTER TABLE IS_PODACI245_ARH EXCHANGE PARTITION P_201106  WITH TABLE IS_PODACI245_STAGE EXCLUDING INDEXES  WITHOUT VALIDATION;
I got an error:
ORA-00001: unique constraint (TXV.SYS_C0032911) violated

it's an unique index between parent and nested table.

what could cause that problem?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 15 2011
Added on Aug 17 2011
4 comments
363 views