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.
May 28, 2020 - 2:08 am UTC
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
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.
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
August 19, 2020 - 11:08 am UTC
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!
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
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
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.
November 18, 2021 - 2:55 am UTC
:-)