Oracle – Create Table by Select Statement

08/14/2017

–WITH data

CREATE TABLE test3 AS
SELECT table_name, tablespace_name
FROM all_tables;

–without data

CREATE TABLE ctas AS
SELECT table_name, tablespace_name
FROM all_tables
WHERE 1=2;

— For example, create a table named EMPLOYEE3 that includes all
— of the column definitions from EMPLOYEE where the DEPTNO = D11.

CREATE TABLE EMPLOYEE3 AS
(SELECT PROJNO, PROJNAME, DEPTNO
FROM EMPLOYEE
WHERE DEPTNO = 'D11') WITH NO DATA