Alter Table

This section shows you the translations related to ALTER TABLE.

Some parts in the output code are omitted for clarity reasons.

1. Description

Use the ALTER TABLE statement to alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition. For object tables or relational tables with object columns, use ALTER TABLE to convert the table to the latest definition of its referenced type after the type has been altered (Oracle documentation).

Oracle syntax

ALTER TABLE [ schema. ] table
  [ alter_table_properties
  | column_clauses
  | constraint_clauses
  | alter_table_partitioning
  | alter_external_table
  | move_table_clause
  ]
  [ enable_disable_clause
  | { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS }
  ] ...
  ;

To review Snowflake syntax, review the following documentation.

2. Sample Source Patterns

2.1. Alter table with clauses

memoptimize_read_clause and memoptimize_read_clause are not applicable in Snowflake so are being removed.

Oracle

Query

IN -> Oracle_01.sql

ALTER TABLE SOMESCHEMA.SOMENAME 
MEMOPTIMIZE FOR READ 
MEMOPTIMIZE FOR WRITE
 ADD (SOMECOLUMN NUMBER , SOMEOTHERCOLUMN VARCHAR(23)) 
 (PARTITION PT NESTED TABLE COLUMN_VALUE STORE AS SNAME
 ( SUBPARTITION SPART NESTED TABLE COLUMN_VALUE STORE AS SNAME))
ENABLE TABLE LOCK;

Snowflake

Query

OUT -> Oracle_01.sql

ALTER TABLE SOMESCHEMA.SOMENAME
ADD (SOMECOLUMN NUMBER(38, 18), SOMEOTHERCOLUMN VARCHAR(23));

Only some column_clauses and constraint_clauses are applicable in Snowflake. In Oracle alter table allows modifying properties from partitions created but in Snowflake, these actions are not required

2.2. Alter table with not supported cases

Oracle

Query

IN -> Oracle_02.sql

ALTER TABLE SOMENAME MODIFY COLUMN SCOLUMN NOT SUBSTITUTABLE AT ALL LEVELS FORCE;

ALTER TABLE SOMENAME MODIFY(SCOLUMN VISIBLE,SCOLUMN INVISIBLE);

ALTER TABLE SOMENAME MODIFY VARRAY VARRAYITEM (
STORAGE(PCTINCREASE 10));

Snowflake

Query

OUT -> Oracle_02.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SOMENAME" **
ALTER TABLE SOMENAME
MODIFY COLUMN SCOLUMN NOT SUBSTITUTABLE AT ALL LEVELS FORCE;

!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SOMENAME" **

ALTER TABLE SOMENAME
MODIFY(SCOLUMN VISIBLE,SCOLUMN INVISIBLE);

!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SOMENAME" **

ALTER TABLE SOMENAME
MODIFY VARRAY VARRAYITEM (
STORAGE(PCTINCREASE 10));

2.3. ADD CONSTRAINT action

The ADD CONSTRAINT action has an equivalent in Snowflake, but it only one constraint can be added per ALTER TABLE statement, so it will be commented when the statement contains two or more constraints.

enable_disable_clause is removed since it is not relevant in Snowflake.

Oracle

Query

IN -> Oracle_03.sql

-- MULTIPLE CONSTRAINT ADDITION SCENARIO
ALTER TABLE TABLE1 ADD (
CONSTRAINT TABLE1_PK
PRIMARY KEY
(ID)
ENABLE VALIDATE,
CONSTRAINT TABLE1_FK foreign key(ID2)
references TABLE2 (ID) ON DELETE CASCADE);

-- ONLY ONE CONSTRAINT ADDITION SCENARIO
ALTER TABLE TABLE1 ADD (
CONSTRAINT TABLE1_FK foreign key(ID2)
references TABLE2 (ID) ON DELETE CASCADE);

Snowflake

Query

OUT -> Oracle_03.sql

-- MULTIPLE CONSTRAINT ADDITION SCENARIO
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0067 - MULTIPLE CONSTRAINT DEFINITION IN A SINGLE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
ALTER TABLE TABLE1
ADD (
CONSTRAINT TABLE1_PK
PRIMARY KEY
(ID) ,
CONSTRAINT TABLE1_FK foreign key(ID2)
references TABLE2 (ID) ON DELETE CASCADE);

-- ONLY ONE CONSTRAINT ADDITION SCENARIO
ALTER TABLE TABLE1
ADD
CONSTRAINT TABLE1_FK foreign key(ID2)
references TABLE2 (ID) ON DELETE CASCADE;

Known Issues

1. Some properties on the tables may be adapted to or not applicable.

Related EWIs

1. SSC-EWI-OR0046: Alter Table syntax is not applicable in Snowflake.

2. SSC-EWI-OR0067: Multiple constraint definition in a single statement is not supported in Snowflake.