2

i have searched a lot but i have found nothing about how to add a range partition to an existing table

alter table myuser.mytable
add PARTITION  BY RANGE (mynumber) INTERVAL (1)
( PARTITION p1 VALUES LESS THAN (108))

that gives me ORA:14150 error, SUBPARTITON keyword is missing, but i dont want to give subpartition

Thomas
  • 366
  • 6
  • 19
  • Possible duplicate of [How do I alter my existing table to create a range partition in Oracle](https://stackoverflow.com/questions/35932771/how-do-i-alter-my-existing-table-to-create-a-range-partition-in-oracle) – Vadzim Apr 09 '18 at 14:14

1 Answers1

4

EDIT: On 19c and 12cR2 this can be done using the MODIFY Clause of ALTER TABLE

ALTER TABLE myuser.mytable MODIFY
PARTITION  BY RANGE (mynumber) INTERVAL (1)
( PARTITION p1 VALUES LESS THAN (108)
PARTITION p2 VALUES LESS THAN (109))  
ONLINE
UPDATE INDEXES

See this from Oracle Docs

PRIOR To 19c or 12cR2:

If your existing Table is Non-Partitioned you will have to:

  1. CREATE a new TABLE with partition definitions. Lets call this table MYTABLE_NEW
  2. INSERT into MYTABLE_NEW all data from MYTABLE
  3. RENAME MYTABLE to MYTABLE_OLD
  4. RENAME MYTABLE_NEW to MYTABLE
  5. DROP MYTABLE_OLD

OR

dbms_redefinition can also be used

See this from AskTom

Also see this other Answer

Akio Hamasaki
  • 525
  • 6
  • 11