CODEWITHSUNDEEP
sqlplsqlsplitoracle12c
m.tizchang
m.tizchang

Reputation: 15

problems with splitiing the range partition in oracle 12c

I have a problem with splitting rang partition in oracle 12c.

i check it with some sources but I thought and saw it a lot. so may it has a simple error but I can't find it. please check this code. thanks.

first of all, I create this table 

CREATE TABLE SALES_SAMPLE_WITH_RANGE_PARTITION
(
DEP_NO      NUMBER (20),
PART_NO     VARCHAR2 (30),
COUNTRY     VARCHAR2(20),
DATES       DATE,
AMOUNT      NUMBER (20)
)
PARTITION BY RANGE(DATES)
(
  PARTITION q1_2012  VALUES LESS THAN(TO_DATE('2012/01/01','YYYY/MM/DD')),
  PARTITION q2_2012  VALUES LESS THAN(TO_DATE('2012/04/01','YYYY/MM/DD')),
  PARTITION q3_2012  VALUES LESS THAN(TO_DATE('2012/07/01','YYYY/MM/DD')),
  PARTITION q4_2012  VALUES LESS THAN(TO_DATE('2013/10/01','YYYY/MM/DD'))
);

after that, I insert these data into it 

INSERT ALL
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES (10, '4519b', 'FRANCE', (TO_DATE('2012/04/17','YYYY/MM/DD')), 45000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '3788a', 'INDIA', (TO_DATE('2012/05/11','YYYY/MM/DD')), 75000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '9519b', 'US', (TO_DATE('2012/09/18','YYYY,MM/DD')), 145000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '3788a', 'PAKISTAN', (TO_DATE('2012/06/21','YYYY/MM/DD')), 37500)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '4577b', 'US', (TO_DATE('2012/06/30','YYYY/MM/DD')), 25000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (30, '7588b', 'CANADA', (TO_DATE('2012/03/26','YYYY/MM/DD')), 50000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (30, '9519b', 'CANADA', (TO_DATE('2012/09/09','YYYY/MM/DD')), 75000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (30, '4519b', 'CANADA', (TO_DATE('2012/06/08','YYYY/MM/DD')), 120000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '3788a', 'US', (TO_DATE('2012/02/18','YYYY/MM/DD')), 4950)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (10, '9519b', 'ITALY', (TO_DATE('2012/02/19','YYYY/MM/DD')), 15000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (10, '9519a', 'FRANCE', (TO_DATE('2012/02/06','YYYY/MM/DD')), 650000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (10, '9519b', 'FRANCE', (TO_DATE('2012/12/16','YYYY/MM/DD')), 650000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '3788b', 'INDIA', (TO_DATE('2012/02/14','YYYY/MM/DD')), 5090)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '4788a', 'US',(TO_DATE('2012/09/17','YYYY/MM/DD')), 4950)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '4788b', 'US', (TO_DATE('2012/12/11','YYYY/MM/DD')), 15000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '4519a', 'INDIA', (TO_DATE('2012/11/21','YYYY/MM/DD')), 650000)
  INTO PP_SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '4519b', 'INDIA', (TO_DATE('2012/12/16','YYYY/MM/DD')), 5090)
  SELECT * FROM DUAL;

and after that I try to split the last partition in two parts

 ALTER TABLE PP_SALES_SAMPLE_WITH_RANGE_PARTITION SPLIT PARTITION q4_2012 AT (TO_DATE('2012/11/15','YYYY/MM/DD') INTO 
  (
  PARTITION Q4_2012_P1,
  PARTITION Q4_2012_P2
  );

unfortunately it doesn't work. :(

please do me a favor and check this code. thanks.

Upvotes: 0

Views: 84

Answers (1)

Younes El-karama
Younes El-karama

Reputation: 189

SQL> CREATE TABLE SALES_SAMPLE_WITH_RANGE_PARTITION
(
DEP_NO      NUMBER (20),
PART_NO     VARCHAR2 (30),
COUNTRY     VARCHAR2(20),
DATES       DATE,
AMOUNT      NUMBER (20)
)
PARTITION BY RANGE(DATES)
(
  PARTITION q1_2012  VALUES LESS THAN(TO_DATE('2012/01/01','YYYY/MM/DD')),
  PARTITION q2_2012  VALUES LESS THAN(TO_DATE('2012/04/01','YYYY/MM/DD')),
  PARTITION q3_2012  VALUES LESS THAN(TO_DATE('2012/07/01','YYYY/MM/DD')),
  PARTITION q4_2012  VALUES LESS THAN(TO_DATE('2013/10/01','YYYY/MM/DD'))
)
Table created.
SQL> INSERT ALL
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES (10, '4519b', 'FRANCE', (TO_DATE('2012/04/17','YYYY/MM/DD')), 45000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '3788a', 'INDIA', (TO_DATE('2012/05/11','YYYY/MM/DD')), 75000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '9519b', 'US', (TO_DATE('2012/09/18','YYYY,MM/DD')), 145000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '3788a', 'PAKISTAN', (TO_DATE('2012/06/21','YYYY/MM/DD')), 37500)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '4577b', 'US', (TO_DATE('2012/06/30','YYYY/MM/DD')), 25000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (30, '7588b', 'CANADA', (TO_DATE('2012/03/26','YYYY/MM/DD')), 50000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (30, '9519b', 'CANADA', (TO_DATE('2012/09/09','YYYY/MM/DD')), 75000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (30, '4519b', 'CANADA', (TO_DATE('2012/06/08','YYYY/MM/DD')), 120000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '3788a', 'US', (TO_DATE('2012/02/18','YYYY/MM/DD')), 4950)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (10, '9519b', 'ITALY', (TO_DATE('2012/02/19','YYYY/MM/DD')), 15000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (10, '9519a', 'FRANCE', (TO_DATE('2012/02/06','YYYY/MM/DD')), 650000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (10, '9519b', 'FRANCE', (TO_DATE('2012/12/16','YYYY/MM/DD')), 650000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '3788b', 'INDIA', (TO_DATE('2012/02/14','YYYY/MM/DD')), 5090)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '4788a', 'US',(TO_DATE('2012/09/17','YYYY/MM/DD')), 4950)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (40, '4788b', 'US', (TO_DATE('2012/12/11','YYYY/MM/DD')), 15000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '4519a', 'INDIA', (TO_DATE('2012/11/21','YYYY/MM/DD')), 650000)
  INTO SALES_SAMPLE_WITH_RANGE_PARTITION  VALUES  (20, '4519b', 'INDIA', (TO_DATE('2012/12/16','YYYY/MM/DD')), 5090)
  SELECT * FROM DUAL
17 rows created.
SQL> REM ## Gather stats and show row nums before the partiton split
SQL> BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     OwnName           => 'SOA_USER1'
    ,TabName           => 'SALES_SAMPLE_WITH_RANGE_PARTITION'
    ,Estimate_Percent  => NULL
    ,Cascade           => TRUE
  ); 
END;
 PL/SQL procedure successfully completed.
SQL> col partition_name for a20
SQL> select partition_name, NUM_ROWS from user_tab_partitions

PARTITION_NAME         NUM_ROWS
-------------------- ----------
Q1_2012                       0
Q2_2012                       5
Q3_2012                       5
Q4_2012                       7

4 rows selected.
SQL> ALTER TABLE SALES_SAMPLE_WITH_RANGE_PARTITION SPLIT PARTITION q4_2012 AT (TO_DATE('2012/11/15','YYYY/MM/DD')) INTO 
  (
  PARTITION Q4_2012_P1,
  PARTITION Q4_2012_P2
  )
Table altered.
SQL> REM ## Gather stats and show row nums before the partiton split
SQL> BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     OwnName           => 'SOA_USER1'
    ,TabName           => 'SALES_SAMPLE_WITH_RANGE_PARTITION'
    ,Estimate_Percent  => NULL
    ,Cascade           => TRUE
  ); 
END;
 PL/SQL procedure successfully completed.

SQL> select partition_name, NUM_ROWS from user_tab_partitions

PARTITION_NAME         NUM_ROWS
-------------------- ----------
Q1_2012                       0
Q2_2012                       5
Q3_2012                       5
Q4_2012_P1                    3
Q4_2012_P2                    4

5 rows selected.

Upvotes: 1

Related Questions