Change column datatype in Oracle table (ALTER TABLE)

I had to change my column from USER_ID NUMBER(22) to USERNAME VARCHAR2 (20 BYTE)Oracle does not allow to modify column when there is data in the table. Also, you can not rename the column.

Finally I came up with the following script:

  1. ALTER TABLE: add new column (USERNAME) with desired datatype.
  2. UPDATE: copy data to the new column
  3. ALTER TABLE: drop the original column (USER_ID)

I heard you can rename a column in Ms SQL.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s