CODEWITHSUNDEEP
sqloracle-databaseoracle11gdatabase-administration
Pandey Amit
Pandey Amit

Reputation: 703

how to increase column size of a table which is partitoned in oracle

How to increase column size of a table which is partitioned oracle.

create table tbl(id number(5) primary key,
  name varchar2(20)) 
  partition by range(id) ( partition part1 values less than (100));

Now I want to increase size of column id to 10. How would I do so without loosing data?

Upvotes: 1

Views: 6750

Answers (1)

Patrick Marchand
Patrick Marchand

Reputation: 3445

So you probably found out: ORA-14060: data type or length of a table partitioning column may not be changed

That means you need to rebuild the table.

There may be a way to use DBMS_REDEFINITION (http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_redefi.htm) or you can swap the partitions with regular tables (with the new column definition applied), recreate the partitioned table with the same new column definition and swap the tables back. The nice thing is that no data needs to be moved during the process.

Credit goes here (see bottom of the thread): https://forums.oracle.com/thread/2445497

Upvotes: 2

Related Questions