Home » SQL & PL/SQL » SQL & PL/SQL » TABLE PARTITIONING (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 )
TABLE PARTITIONING [message #666409] Thu, 02 November 2017 23:38 Go to next message
shawaj
Messages: 59
Registered: January 2016
Member
Hello everyone
I am trying to create partition on existing table first time and i am getting error.Steps that i followed are mentioned below.
Please help me
SQL> CREATE TABLE T1(C_ID NUMBER PRIMARY KEY,DT_CREATED DATE);

SQL> SELECT MIN(DT_CREATED)MIN_DATE ,MAX(DT_CREATED) MAX_DATE FROM T1;
 
MIN_DATE    MAX_DATE
----------- -----------
4/1/1970    2/26/4708

SQL> CREATE TABLE T2(C_ID NUMBER,DT_CREATED DATE)
  2  PARTITION BY RANGE (DT_CREATED)
  3  (PARTITION P_2000 VALUES LESS THAN (TO_DATE('01-APR-1970','DD-MON-YYYY')),
  4  PARTITION P_2020 VALUES LESS THAN (TO_DATE('01-APR-2001','DD-MON-YYYY')),
  5  PARTITION P_2030 VALUES LESS THAN (TO_DATE('01-APR-2030','DD-MON-YYYY')),
  6  PARTITION P_2050 VALUES LESS THAN (TO_DATE('01-APR-2050','DD-MON-YYYY')))
  7  ;

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SHAWAJ','T1');
 
PL/SQL procedure successfully completed
 
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SHAWAJ','T1','T2');
 
begin DBMS_REDEFINITION.START_REDEF_TABLE('SHAWAJ','T1','T2'); end;
 
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

Thanks
Re: TABLE PARTITIONING [message #666410 is a reply to message #666409] Fri, 03 November 2017 00:25 Go to previous messageGo to next message
shawaj
Messages: 59
Registered: January 2016
Member
I resolved my problem by increasing partition range(i.e PARTITION P_2050 VALUES LESS THAN (TO_DATE('01-APR-4050','DD-MON-YYYY')))
SQL> SELECT MIN(DT_CREATED)MIN_DATE ,MAX(DT_CREATED) MAX_DATE FROM T1;
 
MIN_DATE    MAX_DATE
----------- -----------
4/1/1970    2/26/4708

Re: TABLE PARTITIONING [message #666411 is a reply to message #666410] Fri, 03 November 2017 01:21 Go to previous messageGo to next message
John Watson
Messages: 7186
Registered: January 2010
Location: Global Village
Senior Member
You should also consider using the MAXVALUE constant as a boundary for the top partition, or using INTERVAL partitioning to generate partitions automatically when needed.
Re: TABLE PARTITIONING [message #666420 is a reply to message #666411] Fri, 03 November 2017 07:55 Go to previous message
shawaj
Messages: 59
Registered: January 2016
Member
Thanks for quick reply.
Previous Topic: ORA-06502: PL/SQL: numeric or value error.
Next Topic: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Goto Forum:
  


Current Time: Sat Dec 16 15:21:02 CST 2017

Total time taken to generate the page: 0.01231 seconds