CODEWITHSUNDEEP
sqloracle-database
VenkatKrishna
VenkatKrishna

Reputation: 127

Table partitions in oracle with alter command

I have to create partitions dynamically for oracle table in a JAVA program , from user input. If user enters 3 I have to make 3 partitions. previously there is a table with around 100 columns. But my problem is when I am executing the below query in Sql Developer it is giving error as shown below.

Is there any alternative to add three or more partitions at a time with alter command or must I alter the table for three times for three partitions?

I am using 'Oracle 9i', suggest some thing

Thanks in advance

Sql Query 

ALTER TABLE table_01  add 
partition p2 values less than(20),
partition p3 values less than(30),
partition p4 values less than(40);

Error 

Error report -
SQL Error: ORA-14043: only one partition may be added
14043. 00000 -  "only one partition may be added"
*Cause:    ALTER TABLE ADD PARTITION contained descriptions of more than  one
       partition to be added
*Action:   Ensure that the statement contains exactly one partition
       definition and that it does not contain any commas

Upvotes: 0

Views: 1116

Answers (1)

APC
APC

Reputation: 146209

" I am using 'Oracle 9i', suggest some thing"

I'm afraid the only suggestion is to upgrade to more recent version of Oracle. 9i has been out of support for almost a decade.

Oracle 12c supports the syntax you want to implement. Previous versions only support adding a single partition at a time; I have checked 11gR2 and it hurls ORA-14043: only one partition may be added.

If you can't upgrade you'll have to write your application to add the partitions in multiple statements.

Upvotes: 5

Related Questions