Search code examples
sqloracle-databaseoracle12c

alter table adding guid column in oracle

What is the statement to alter a table which holds about 10 million rows, adding a guid column which will hold a unique identifier for each row (without being part of the pk)

What datatype should the global unique identifier column be? Is there a procedure which creates it? How will it auto incremented or produced everytime a new record is inserted?

Solution

  • Break it down into the separate stages

    First, we need a new column:

    alter table MyTable
add guid_column raw(32) default sys_guid();

    Then update the existing rows:

    update MyTable
set guid_column = sys_guid();