1

I have a table in Oracle exadata with 3.4 billion rows. I need to create a new column in this table which is the sum of the other 2 columns in the table. I tried the below. - Create column using alter table add column. - Update table set column C = Column A + column B. But update threw the below error after a while. ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

I read the online documentation for this error and checked with the DBA and he said its not a space issue and that I should try an alternative to the update statement in this case. Please let me know what other ways I can do this operation. Can I do this using insert?

Thanks, Sawan

Sawan S
  • 87
  • 8

2 Answers2

2

As you are updating billion rows that is why it is consuming more space and returning table space error. One simple solution is you can update the table in two parts instead of directly updating the entire table.

If you have column with unique sequence number then you can do the following

update test set columnC=columnA+columnB where id between 1 and 1000000;

And similarly update the second part.

Naveen Kumar
  • 1,988
  • 1
  • 7
  • 11
2

To get an additional column that is calculated from other column of the row you can use a virtual column.

The great benefit is that you store no data, it can't be inconstistent and it is installed immediately.

alter table TEST add
(columnC NUMBER GENERATED ALWAYS AS (columnA + columnB) VIRTUAL)
;

select * from TEST;

   COLUMNA    COLUMNB    COLUMNC
---------- ---------- ----------
         1          1          2
         2          2          4
         3          3          6
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53