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.
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.
