Home » SQL & PL/SQL » SQL & PL/SQL » partition non partioned table (12 c)
partition non partioned table [message #685924] Wed, 27 April 2022 13:50 Go to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Hi Experts,
I am trying to modify non-partitioned table into partitioned .
alter table tb_spc modify
partition by range (tm_id)
(partition p1 values less than (1000),
partition p2 values less than (10000) and greater than (999),
partition p3 values less than (15000) and greater than (9999),
partition p4 values less than (maxvalue)) online;

I am using column tm_id to define the range.
But when I try to run the command it gives the error.

Could you please tell me if this is the right way and syntax to change non-partitioned table into partitoned table?


Thanks,
Varun
Re: partition non partioned table [message #685925 is a reply to message #685924] Wed, 27 April 2022 14:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3152
Registered: January 2010
Location: Connecticut, USA
Senior Member
Is it 12.1 or 12.2? Modify non-partitioned table into partitioned is available starting 12.2.

SY.

[Updated on: Wed, 27 April 2022 14:07]

Report message to a moderator

Re: partition non partioned table [message #685926 is a reply to message #685924] Thu, 28 April 2022 01:16 Go to previous messageGo to next message
John Watson
Messages: 8720
Registered: January 2010
Location: Global Village
Senior Member
There is no "and greater than" clause.
Re: partition non partioned table [message #685927 is a reply to message #685925] Thu, 28 April 2022 08:23 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Actually it is 12.1 , thats the reason It gives error.
Can I recreate the table with partition enabled then import the data from old non-partitioned Table,
Would that be the best solution?

Thanks,
Varun
Re: partition non partioned table [message #685928 is a reply to message #685927] Thu, 28 April 2022 08:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3152
Registered: January 2010
Location: Connecticut, USA
Senior Member
You can. But as John noted partitioning syntax is wrong.

SY.
Re: partition non partioned table [message #685929 is a reply to message #685928] Thu, 28 April 2022 09:14 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Thanks John and SY
Re: partition non partioned table [message #685930 is a reply to message #685928] Thu, 28 April 2022 09:40 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
So I have another UAT server on 19c where I can try running this.
I want to modify table to range partition and the column I want to use is tm_id.
tm_id has 122 distinct values , So Do I have to mention all the values while modifying the table to partitioned table?
Or is there a command where it will create all the partitions based upon those 122 values on its own.
Right now I have to issue this mentioning all the 122 tm_id values to modify:-

alter table tb_spc modify
partition by range (tm_id)
(partition p1 values less than (1000),
partition p2 values less than (10000)
partition p3 values less than (15000)
partition p4 values less than (maxvalue)) online;
Is there any command that I could use where it creates all the partitions by itself?

Thanks,
Varun

[Updated on: Thu, 28 April 2022 09:41]

Report message to a moderator

Re: partition non partioned table [message #685931 is a reply to message #685930] Thu, 28 April 2022 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68226
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you have N distinct values and want N partitions then it is not range partitioning you need but list partitioning.
Range partitioning is when you want to partition values by... range.

Re: partition non partioned table [message #685932 is a reply to message #685931] Thu, 28 April 2022 12:30 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Ahhhh Michel Thanks for making me understand that .

So it that case i can just run :-
alter table tb_spc modify partition by list (tm_id) online;
Right now that 19c db is down,I will run it once it is back up and let all know if that works.
-Varun
Re: partition non partioned table [message #685933 is a reply to message #685931] Thu, 28 April 2022 12:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3152
Registered: January 2010
Location: Connecticut, USA
Senior Member
As Michel said, LIST AUTOMATIC better fits your requirements:

SQL> create table tm_spc(tm_id number);

Table created.

SQL> alter table tm_spc modify partition by list(tm_id) automatic (partition p0 values(null));

Table altered.

SQL> select partition_name,high_value from user_tab_partitions where table_name = 'TM_SPC';

PARTITION_NAME  HIGH_VALUE
--------------- ----------
P0              null

SQL> insert into tm_spc values(123);

1 row created.

SQL> select partition_name,high_value from user_tab_partitions where table_name = 'TM_SPC';

PARTITION_NAME  HIGH_VALUE
--------------- ----------
P0              null
SYS_P6936       123

SQL> insert into tm_spc values(99);

1 row created.

SQL> select partition_name,high_value from user_tab_partitions where table_name = 'TM_SPC';

PARTITION_NAME  HIGH_VALUE
--------------- ----------
P0              null
SYS_P6936       123
SYS_P6937       99

SQL>
SY.
Re: partition non partioned table [message #685935 is a reply to message #685933] Thu, 28 April 2022 14:15 Go to previous message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Thanks a lot SY.
Previous Topic: INSERT, SELECT CONNECT by
Next Topic: Oracle flinding max plan id
Goto Forum:
  


Current Time: Mon Jul 04 16:34:11 CDT 2022