ORA-12839 on 9.2.0.4

From: c3r3bro <cerebruspipeline_at_yahoo.co.uk>
Date: 13 Jan 2004 09:41:15 -0800
Message-ID: <74f64a8f.0401130941.27b53318@posting.google.com>

Hi,

I have created the following sql script that replicates the problem I am having. I have migrated from 8.1.7.4 where the exact same code runs absolutely fine, but I get oracle errors in 9i.

Can anyone shed any light on this?

Thanks,

-c3r3bro

-----script------

drop table bbb;
drop table ddd;
drop table ccc;
drop table aaa;


CREATE TABLE bbb(
    alarm_id                   NUMBER(15, 0)    NOT NULL,
    day_no                     NUMBER(2, 0)     NOT NULL,
    poll_id                    NUMBER(11, 0)    NOT NULL,

    CONSTRAINT pk_bbb PRIMARY KEY (alarm_id, day_no)     USING INDEX
    LOCAL
    STORAGE (INITIAL 10k NEXT 10k PCTINCREASE 0 FREELISTS 4) )
STORAGE (INITIAL 10k NEXT 10k PCTINCREASE 0 FREELISTS 4) PARTITION BY RANGE(day_no)
SUBPARTITION BY HASH(alarm_id)
SUBPARTITIONS 8

(PARTITION bbb_p1 VALUES LESS THAN (2),
 PARTITION bbb_p2 VALUES LESS THAN (3),
 PARTITION bbb_p3 VALUES LESS THAN (4),
 PARTITION bbb_p4 VALUES LESS THAN (5),
 PARTITION bbb_p5 VALUES LESS THAN (6),
 PARTITION bbb_p6 VALUES LESS THAN (7),
 PARTITION bbb_p7 VALUES LESS THAN (8),
 PARTITION bbb_p8 VALUES LESS THAN (9))

PARALLEL 8
;

ALTER INDEX pk_bbb PARALLEL 8
;

CREATE TABLE ddd(

    alarm_id                      NUMBER(15, 0)    NOT NULL,
    day_no                        NUMBER(2, 0)     NOT NULL,

    CONSTRAINT pk_ddd PRIMARY KEY (alarm_id, day_no)     USING INDEX
    LOCAL
    STORAGE (INITIAL 10k NEXT 10k PCTINCREASE 0 FREELISTS 4) )
STORAGE (INITIAL 10k NEXT 10k PCTINCREASE 0 FREELISTS 4) PARTITION BY RANGE(day_no)
SUBPARTITION BY HASH(alarm_id)
SUBPARTITIONS 8

(PARTITION ddd_p1 VALUES LESS THAN (2),
 PARTITION ddd_p2 VALUES LESS THAN (3),
 PARTITION ddd_p3 VALUES LESS THAN (4),
 PARTITION ddd_p4 VALUES LESS THAN (5),
 PARTITION ddd_p5 VALUES LESS THAN (6),
 PARTITION ddd_p6 VALUES LESS THAN (7),
 PARTITION ddd_p7 VALUES LESS THAN (8),
 PARTITION ddd_p8 VALUES LESS THAN (9))

PARALLEL 8
;

ALTER INDEX pk_ddd PARALLEL 8
;

CREATE TABLE aaa(

    alarm_id                   NUMBER(15, 0)    NOT NULL,
    day_no                     NUMBER(2, 0)     NOT NULL,
    poll_id                    NUMBER(11, 0)    NOT NULL,

    CONSTRAINT pk_aaa PRIMARY KEY (alarm_id)     USING INDEX
    LOCAL
    STORAGE (INITIAL 10k NEXT 10k PCTINCREASE 0 FREELISTS 4) )
STORAGE (INITIAL 10k NEXT 10k PCTINCREASE 0 FREELISTS 4) PARTITION BY HASH (alarm_id)
PARALLEL 8
;

ALTER INDEX pk_aaa PARALLEL 8
;

CREATE TABLE ccc(

    alarm_id                      NUMBER(15, 0)    NOT NULL,
    day_no                        NUMBER(2, 0)     NOT NULL,
    poll_id                       NUMBER(11) NOT NULL,

    CONSTRAINT pk_ccc PRIMARY KEY (alarm_id)     USING INDEX
    LOCAL
    STORAGE (INITIAL 10k NEXT 10k PCTINCREASE 0 FREELISTS 4) )
STORAGE (INITIAL 10k NEXT 10k PCTINCREASE 0 FREELISTS 4) PARTITION BY HASH (alarm_id)
PARALLEL 8
;

ALTER INDEX pk_ccc PARALLEL 8
;

ALTER TABLE ccc ADD CONSTRAINT con_ccc_aaa

    FOREIGN KEY (alarm_id)
    REFERENCES aaa(alarm_id)
;

alter session enable parallel dml;

INSERT INTO bbb partition (BBB_P8) (

  	 alarm_id,
	 day_no,
	 poll_id)
  SELECT alarm_id,
	 day_no,
	 poll_id

    FROM aaa
   WHERE poll_id = 1;

INSERT INTO ddd partition (ddd_p8)(

    alarm_id,
    day_no)
  SELECT
    alarm_id,
    day_no
  FROM ccc
 WHERE poll_id = 1;

  DELETE FROM ccc
   WHERE poll_id = 1;

  DELETE FROM aaa
   WHERE poll_id = 1; Received on Tue Jan 13 2004 - 11:41:15 CST