Search notes:

Oracle: CREATE TABLE … AS SELECT …

create table … as select … (aka CTAS) creates a table whose column-data types and values stored in its records are determined by the result set of a select statement.

create table tbl_copy
-- nologging
-- pctfree
as
select
   col_1,
   col_4,
   col_5
from
   tbl;

WITH clause

A create table as select statement can be combined with a WITH clause as shown below:

create table tq84_numbers
as
with nums as (
   select
      level as num,
      to_char(to_date(level, 'j'), 'jsp')  spelled
   from
      dual connect by level <= 10000
)
select
   num,
   spelled
from
   nums;

Parallel

It must be made sure that the session is enabled for parallel DDLs:

alter session enable parallel ddl;

Then, the table can be created «parallely»:

create table tab_xyz PARALLEL as
select
  …

Specifying column names

It is possible to specify column names with their datatypes inferred from the select statement:

create table tq84_xyz (foo, bar, baz)
as
   select
      object_name,
      object_type,
      created
   from
      user_objects;
      
desc tq84_xyz;

Specifying the data types as well might result in an ORA-01773: may not specify column datatypes in this CREATE TABLE error.

Specifying storage options

The following CTAS statement creates a table with pctfree 0:

create table tq84_objs
   pctfree 0
as
select
   owner,
   object_type,
   object_name
from
   dba_objects;

Oracle: CREATE TABLE … AS SELECT …

WITH clause

Parallel

Specifying column names

Specifying storage options

See also