"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<tg1Nb.1490$AA6.1330_at_fed1read03>...
> c3r3bro wrote:
> > 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;
>
> My crystal ball is in for repair
> and I'm not about to guess at which
> of the many lines above actaully generated an error.
I thought it was obvious that it was the second delete that causes the
error. Thanks for the help though.
Received on Wed Jan 14 2004 - 14:35:01 CST
