CODEWITHSUNDEEP
oracle-databaseoracle10g
Snehal
Snehal

Reputation: 47

Error in execution of parallel partition on pre built table

SQL> CREATE TABLESPACE sf1 DATAFILE 'sf1.dbf' SIZE 40M ONLINE;
Tablespace created.

SQL> CREATE TABLESPACE sf2 DATAFILE 'sf2.dbf' SIZE 40M ONLINE;
Tablespace created.

SQL> CREATE TABLESPACE sf3 DATAFILE 'sf3.dbf' SIZE 40M ONLINE;
Tablespace created


SQL>  CREATE TABLE part_sales_tab_mv(time_id, cust_id, sum_dollar_sales, sum_unit_sale)
      2       PARALLEL PARTITION BY RANGE (time_id)
      3          (PARTITION month1
      4          VALUES LESS THAN (TO_DATE('31-12-1998', 'DD-MM-YYYY'))
      5          PCTFREE 0 PCTUSED 99
      6          STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
      7          TABLESPACE sf1,
      8          PARTITION month2
      9          VALUES LESS THAN (TO_DATE('31-12-1999', 'DD-MM-YYYY'))
     10        PCTFREE 0 PCTUSED 99
     11        STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
     12        TABLESPACE sf2,
     13        PARTITION month3
     14        VALUES LESS THAN (TO_DATE('31-12-2000', 'DD-MM-YYYY'))
     15       PCTFREE 0 PCTUSED 99
     16       STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
     17       TABLESPACE sf3)  AS
     18       SELECT s.time_id, s.cust_id, SUM(s.amount_sold) AS sum_dollar_sales
     19       SUM(s.quantity_sold) AS sum_unit_sales
     20       FROM sales s GROUP BY s.time_id, s.cust_id;
     CREATE TABLE part_sales_tab_mv(time_id, cust_id, sum_dollar_sales, sum_unit_
    sale)

ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-14400: inserted partition key does not map to any partition

I am executing this query on sales table of sh schema on oracle 10g. plz help me to resolve the error.The question for query is as follows:

Partition a prebuilt table part_sales_tab_mv as directed:

  1. Create a table part_sales_tab_mv having partitions over three months
  2. Register the materialized view to use the prebuilt table. This materialized view must be eligible for query rewrite

Upvotes: 0

Views: 461

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You are creating three partitions:

  • month1 includes any dates up to 1998-12-30 23:59:59
  • month2 includes any dates between 1998-12-31 00:00:00 and 1999-12-30 23:59:59
  • month3 includes any dates between 1999-12-31 00:00:00 and 2000-12-30 23:59:59

You said in a comment that you have data from January 1998 to December 2001. That spans four years, and your have three year-based partitions (almost, since your boundaries are off by a day); so there is nowhere for the data from 2001, or indeed from 2000-12-31, to go. That is what the error is telling you.

It looks from the first requirement that you want an interval range partitioned table where each partition covers three months; not three partitions, which would each have to cover 16 months to fit all your data in. Or 16 fixed partitions of three months each - an interval range would be more flexible later, but your tutor might be expecting fixed partitions.

Upvotes: 1

Related Questions