Skip to Main Content
  • Questions
  • Modify non partitioned table to partitioned online

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Imtiyaz.

Asked: March 03, 2017 - 3:27 pm UTC

Last updated: November 18, 2021 - 2:55 am UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am trying to convert a non partitioned table into interval partitioned table online.I created a simple test table and ran the below script but I get ORA-14006 invalid partition name error. My conjecture would be this is not allowed for interval partitioning. I could do it on list paritioning.

Thanks for your help.

create table a
( x int,
y varchar2(10));

insert into a values(12,'abc');
insert into a values(22,'ab');
insert into a values(32,'c');

alter table a
add constraint xpa primary key (x)
using index;

create index xiea on a(y);

ALTER TABLE a MODIFY
PARTITION BY RANGE(x) INTERVAL (10)
( PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20),
PARTITION P3 VALUES LESS THAN (30)
)

update indexes
(xpa local,
xiea global parition by range(x)
(partition ip1 values less than (MAXVALUE)
) ;

with LiveSQL Test Case:

and Connor said...

You're missing a few brackets, got a few typos and some rules that apply to all partitioned tables, ie

SQL> create table a
  2  ( x int,
  3  y varchar2(10));

Table created.

SQL>
SQL>
SQL> insert into a values(12,'abc');

1 row created.

SQL> insert into a values(22,'ab');

1 row created.

SQL> insert into a values(32,'c');

1 row created.

SQL>
SQL>
SQL>
SQL> alter table a
  2  add constraint xpa primary key (x)
  3  using index;

Table altered.

SQL>
SQL>
SQL> create index xiea on a(y);

Index created.

SQL>
SQL> ALTER TABLE a MODIFY
  2  PARTITION BY RANGE(x) INTERVAL (10)
  3  ( PARTITION P1 VALUES LESS THAN (10),
  4  PARTITION P2 VALUES LESS THAN (20),
  5  PARTITION P3 VALUES LESS THAN (30)
  6  )
  7  update indexes
  8  (xpa local,
  9  xiea global parition by range(x)
 10  (partition ip1 values less than (MAXVALUE)
 11  ) ;
xiea global parition by range(x)
            *
ERROR at line 9:
ORA-00907: missing right parenthesis


SQL>
SQL> ALTER TABLE a MODIFY
  2  PARTITION BY RANGE(x) INTERVAL (10)
  3  ( PARTITION P1 VALUES LESS THAN (10),
  4  PARTITION P2 VALUES LESS THAN (20),
  5  PARTITION P3 VALUES LESS THAN (30)
  6  )
  7  update indexes
  8  (xpa local,
  9  xiea global partition by range(x)
 10  (partition ip1 values less than (MAXVALUE)
 11  ) ;
)
 *
ERROR at line 11:
ORA-00907: missing right parenthesis


SQL>
SQL> ALTER TABLE a MODIFY
  2  PARTITION BY RANGE(x) INTERVAL (10)
  3  ( PARTITION P1 VALUES LESS THAN (10),
  4  PARTITION P2 VALUES LESS THAN (20),
  5  PARTITION P3 VALUES LESS THAN (30)
  6  )
  7  update indexes
  8  (xpa local,
  9  xiea global partition by range(x)
 10  (partition ip1 values less than (MAXVALUE))
 11  ) ;
ALTER TABLE a MODIFY
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14038: GLOBAL partitioned index must be prefixed


But if fix up those, then you're fine

SQL> ALTER TABLE a MODIFY
  2  PARTITION BY RANGE(x) INTERVAL (10)
  3  ( PARTITION P1 VALUES LESS THAN (10),
  4  PARTITION P2 VALUES LESS THAN (20),
  5  PARTITION P3 VALUES LESS THAN (30)
  6  )
  7  update indexes
  8  (xpa local,
  9  xiea global partition by range(y)
 10  (partition ip1 values less than (MAXVALUE))
 11  ) ;

Table altered.


And all of that works only in 12.2. If you are on 12.1, you need to use DBMS_REDEFINITION. See here for an example

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484


Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

Imtiyaz Mir, March 07, 2017 - 7:40 pm UTC

I appreciate the help. This was my first ever question on any internet forum and I am glad it helped me a lot. Thank you.

Doesnt look like the syntax works properly

Gavin Powell, May 27, 2020 - 9:31 pm UTC

Looking forward to when it does

alter table test modify
partition by list (column)
(
partition p1 values('D'), partition p2 values('P'), partition p3 values('U'), partition p4 values('V')
)
;

Produces the same partition name error as above.

partitioning a table based on monthly

Rajasekhar, August 18, 2020 - 11:49 am UTC

Hello Connor,

How to partition a non partitioned table by using date range implicity for historical data on monthly basis

Non partitioned Table data
Date Record description
31-jan-2016 1 Janurary-2016
31-jan-2016 2 Janurary-2016
28-feb-2016 1 february-2016
31-Jul-2016 1 July-2016
31-Dec-2016 1 december-2016
31-jan-2017 1 Janurary-2017
31-Jul-2017 1 July-2017
31-Jul-2017 2 July-2017
31-oct-2018 1 October-2018

Excepted table partitioning
Jan-16 feb-2016 mar-2016
2 records 1 record 0
jan-17
1

is it poosible to partition a table based on month for historical data implicity instead of doing it explicity for all the years
Alter table EDL.T_RPT_FACT
Modify
Partition by P1 (ACCOUNTING_DATE)
(
PARTITION P1 VALUES LESS THAN '01-Jan-2016',
PARTITION P2 VALUES LESS THAN '01-Feb-2016',
.
.
.
.
.

)
online;



Thanks in advance
Chris Saxon
August 18, 2020 - 1:53 pm UTC

You can use interval partitioning when adding the partition scheme. This can generate the monthly partitions without you having to list them all:

create table t (
  c1, c2
) as 
  select level , 
         date'2019-12-31' + level 
  from   dual
  connect by level <= 100;
  
alter table t
  modify partition by range ( c2 ) 
  interval ( interval '1' month ) (
    partition p0 values less than ( date'2020-01-01' )
  );
  
exec dbms_stats.gather_table_stats ( user, 't' ) ;

select partition_name, num_rows, high_value 
from   user_tab_partitions
where  table_name = 'T';

PARTITION_NAME NUM_ROWS HIGH_VALUE                                                                            
P0                    0 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    
SYS_P2120            31 TO_DATE(' 2020-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    
SYS_P2121            29 TO_DATE(' 2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    
SYS_P2122            31 TO_DATE(' 2020-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    
SYS_P2123             9 TO_DATE(' 2020-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    

Invalid partition name

Rajasekhar, August 18, 2020 - 3:08 pm UTC

Hello Chris,

Thank you so much for your help.
i am getting ORA-14006: invalid partition name, when i tried to alter the table both in 11g and 12c from toad.
Could you please help with me above error.


Thanks in advance.

Chris Saxon
August 18, 2020 - 5:04 pm UTC

alter table ... modify partition is a 12.2 feature.

Workaround for versions before 12.2

Rajasekhar, August 18, 2020 - 5:07 pm UTC

Hello Chris,

Is there any workaround for versions before 12.2?


Thanks in advance
Chris Saxon
August 19, 2020 - 11:08 am UTC

You'll want to use dbms_redefinition, as stated in the original answer:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484

Thanks

Rajasekhar, August 19, 2020 - 2:36 pm UTC

Hello Chris,

Thank you so much

alter table to partition by reference

James Su, October 23, 2021 - 10:32 pm UTC

hi Chris,
What is the best way ton convert a nonpartitioned table to partition by reference? I tried this in Oracle 19c and got an error:

ALTER TABLE child_table partition by reference(chi_fk);

partition by reference(chi_fk)
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option

There are other ways but they all require a lot of work. Is it possible to do it with one DDL? Thanks!
Connor McDonald
October 26, 2021 - 2:30 am UTC

Sorry - to my knowledge there is no one-line conversion to reference partitioning currently.

Its back to first principles or dbms_redefinition.

A compromise could be to add the parent partitioning key into the child table and then convert each online.

alter table to partition by reference - 19.12

Rajeshwaran Jeyabal, October 26, 2021 - 7:42 am UTC

Tried this on 19.12 and it worked.
demo@PDB19> create table dept as
  2  select *
  3  from scott.dept;

Table created.

demo@PDB19> create table emp as
  2  select *
  3  from scott.emp;

Table created.

demo@PDB19> alter table dept modify
  2  partition by hash(deptno)
  3  ( partition p1, partition p2 );

Table altered.

demo@PDB19> alter table dept
  2  add constraint dept_pk
  3  primary key(deptno);

Table altered.

demo@PDB19>
demo@PDB19> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept
  5  modify deptno not null;

Table altered.

demo@PDB19> alter table emp modify
  2  partition by reference( emp_fk );

Table altered.

demo@PDB19> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------
---
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

Connor McDonald
October 27, 2021 - 2:11 am UTC

Well that is nice indeed and seems a recent addition.

alter table to partition by reference

James Su, October 26, 2021 - 1:27 pm UTC

Thank you Rajeshwaran, this is good news, it's time to upgrade my 19.10 database!

interval partitioning

James Su, November 13, 2021 - 2:21 am UTC

hi Rajeshwaran and McDonald,

It looks like my error is caused by interval partitioning on the parent table. It works for other partitioning methods.

Could you please do a test in 19.12? Thank you!

create table parent(pk int constraint parent_pk primary key, i int, j int)
partition by range(i)
INTERVAL(10 )
(partition p1 values less than (10)
);


create table child(fk int not null, uk1 int unique, uk2 int unique,
constraint child_fk foreign key(fk) references parent(pk));

alter table child partition by reference(child_fk);

ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


Connor McDonald
November 16, 2021 - 5:06 am UTC

No you just left out the word "modify" :-)

SQL> create table parent(pk int constraint parent_pk primary key, i int, j int)
  2  partition by range(i)
  3  INTERVAL(10 )
  4  (partition p1 values less than (10)
  5  );

Table created.

SQL>
SQL> create table child(fk int not null, uk1 int unique, uk2 int unique,
  2  constraint child_fk foreign key(fk) references parent(pk));

Table created.

SQL>
SQL> alter table child modify partition by reference(child_fk);

Table altered.



Works like a charm!

James Su, November 16, 2021 - 3:01 pm UTC

What a silly mistake I've made!
Thank you McDonald.
Connor McDonald
November 18, 2021 - 2:55 am UTC

:-)

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.