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);