Home » SQL & PL/SQL » SQL & PL/SQL » Alter Table <<table name>> add partition by range does not work (Oracle 11g)
Alter Table <<table name>> add partition by range does not work [message #670366] |
Thu, 28 June 2018 14:11 |
|
manikandan23
Messages: 34 Registered: February 2017
|
Member |
|
|
Hi, I have created the following table with the given syntax below. But when I tried to alter the table to add more partitions, it gives error in the alter statement.It says "Invalid data type"
when I execute the below given alter table statement.
Any help on this is greatly appreciated.
Thank you,
Mani A
First Statement: -- Working Fine
CREATE TABLE sales1
(
dept_no number,
part_no varchar2(5),
country varchar2(20),
date1 timestamp(6),
amount number
)
PARTITION BY RANGE(date1)
(
PARTITION OD201201 VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
Second Statement -- Error "Invalid data type"
alter table sales1
add PARTITION BY RANGE(date1)
(
PARTITION OD201202 VALUES LESS THAN (TO_DATE(' 2012-02-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
|
|
|
Re: Alter Table <<table name>> add partition by range does not work [message #670367 is a reply to message #670366] |
Thu, 28 June 2018 14:20 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Did you RTFM on add partition syntax? Meditate over:
SQL> alter table sales1
2 add PARTITION BY RANGE(date1)
3 (
4 PARTITION OD201202 VALUES LESS THAN (TO_DATE(' 2012-02-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
5 )
6 /
add PARTITION BY RANGE(date1)
*
ERROR at line 2:
ORA-00902: invalid datatype
SQL> alter table sales1
2 add PARTITION OD201202 VALUES LESS THAN (TO_DATE(' 2012-02-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
3 /
Table altered.
SQL>
SY.
|
|
|
Re: Alter Table <<table name>> add partition by range does not work [message #670368 is a reply to message #670366] |
Thu, 28 June 2018 14:44 |
|
manikandan23
Messages: 34 Registered: February 2017
|
Member |
|
|
Thank you!
When I tried to create a partition by range using alter table statement, it does not let me do it at all.
Initially, I created a table and loaded data. Then wanted to create a range partition on that table based on a column.
So I have tried using the following syntax: it did not let me create all at once
alter table sales1 add
PARTITION BY RANGE(date1)
(
PARTITION OD201201 VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OD201202 VALUES LESS THAN (TO_DATE(' 2012-02-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OD201203 VALUES LESS THAN (TO_DATE(' 2012-02-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OD201204 VALUES LESS THAN (TO_DATE(' 2012-02-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
Since the above statement did not work, (my DBA told the table should be empty to create a range partition) and so I wanted to test it and hence decided to issue the above mentioned comments in my original post.
Sorry, I am still not clear on what is really wrong in altering a table that was not created with any type of partitions and has considerable number of records (~100000 rows) and later add a range partition on it based on a column on the table.
Thanks again,
Mani A
|
|
|
Re: Alter Table <<table name>> add partition by range does not work [message #670369 is a reply to message #670368] |
Thu, 28 June 2018 14:58 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Where did you see BY RANGE(date1) in code I posted??? Can't you just read manuals and not invent own syntax?
SQL> CREATE TABLE sales1
2 (
3 dept_no number,
4 part_no varchar2(5),
5 country varchar2(20),
6 date1 timestamp(6),
7 amount number
8 )
9 PARTITION BY RANGE(date1)
10 (
11 PARTITION OD201201 VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
12 )
13 /
Table created.
SQL> alter table sales1 add
2 PARTITION OD201202 VALUES LESS THAN (TO_DATE(' 2012-02-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
3 PARTITION OD201203 VALUES LESS THAN (TO_DATE(' 2012-02-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
4 PARTITION OD201204 VALUES LESS THAN (TO_DATE(' 2012-02-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
5 /
Table altered.
SQL>
SY.
|
|
|
Re: Alter Table <<table name>> add partition by range does not work [message #670376 is a reply to message #670369] |
Fri, 29 June 2018 06:52 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You could also just set up automatic partition creation (available in 11g) and have oracle just make new partitions depending on the data
CREATE TABLE Sales1
(
Dept_no NUMBER,
Part_no VARCHAR2(5),
Country VARCHAR2(20),
Date1 TIMESTAMP(6),
Amount NUMBER
)
PARTITION BY RANGE(Date1)
INTERVAL ( NUMTODSINTERVAL(1, 'DAY') )(PARTITION Od201201
VALUES LESS THAN
(TO_DATE(' 2012-02-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')));
|
|
|
Goto Forum:
Current Time: Fri Sep 27 16:56:06 CDT 2024
|