Oracle - Create Table from Another Table and Add a New Column

Let's say we want to add a new column to an existing database table T.

Get the DDL for the table and its constraints (e.g. use SQL Developer or DBMS_METADATA.GET_DDL).

Duplicate the old table. This table will be temporary for transferring existing data to the new table.

CREATE TABLE T_TMP AS (SELECT * FROM T);

Drop the old table.

DROP TABLE T CASCADE CONSTRAINTS;

Duplicate the temporary table and add the column.

CREATE TABLE T AS
  SELECT COL_1, COL_2, COL_3, CAST(NULL AS NUMBER(15)) AS COL_NEW
  FROM RUN_T_TMP;

Recreate all constraints, primary keys and indexes.

Drop the temporary table.

DROP TABLE T_TMP CASCADE CONSTRAINTS;


That's it.