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.
Received on Tue Jan 13 2004 - 19:51:21 CST
