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 Go to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
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 Go to previous message
Bill B
Messages: 1969
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')));

Previous Topic: How to replace the new line and line feed in a specific manner
Next Topic: Select using DBMS_SQL.EXECUTE (merged)
Goto Forum:
  


Current Time: Sun Sep 20 06:31:11 CDT 2020