Oracle: “CREATE TABLE AS SELECT” (CTAS) with PARTITIONING and Column Default Value

This article guides you to write Oracle PL/SQL command for creating new table as select from other tables/views with partitioning and column default value.

First of all, this is the syntax of a simple CTAS:

CREATE TABLE TBLA AS SELECT * FROM TBLB;

Please note that in the example above, TBLA is the new created table. TBLB is the source table. Now SQL syntax for CTAS with PARTITION:

CREATE TABLE TBLA 
PARTITION BY RANGE(CREATED_DATE)
(
PARTITION P201906 VALUES LESS THAN (TO_DATE('20190701', 'YYYYMMDD')),
PARTITION P201907 VALUES LESS THAN (TO_DATE('20190801', 'YYYYMMDD')), 
PARTITION P201908 VALUES LESS THAN (TO_DATE('20190901', 'YYYYMMDD')), 
) 
AS SELECT * FROM TBLB

I created a “RANGE PARTITION” in the example, but you can create any kind of PARTITION you want.

About column default value, we need to do it in a separated SQL with alter table command.

ALTER TABLE TBLA MODIFY ( COLUMN1 DATE DEFAULT SYSDATE);