| partition non partioned table [message #685924] |
Wed, 27 April 2022 13:50  |
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 #685927 is a reply to message #685925] |
Thu, 28 April 2022 08:23  |
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 #685930 is a reply to message #685928] |
Thu, 28 April 2022 09:40 |
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 #685932 is a reply to message #685931] |
Thu, 28 April 2022 12:30 |
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 |
Solomon Yakobson
Messages: 3305
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> |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
