Create Table as Select (CTAS) to Tablespace

Create Table as Select Tablespace

Without specifying tablespace, Create Table as Select (CTAS) will go to the default tablespace of the user. How about specifying a tablespace we want to allocate the new table in CTAS?

SQL> create table big_table_bak as select * from big_table tablespace erptbs;
create table big_table_bak as select * from big_table tablespace erptbs
                                                                 *
ERROR at line 1:
ORA-00933: SQL command not properly ended

We saw ORA-00933 when creating a table as select.

In fact, we should put the tablespace clause after CREATE TABLE clause and right before AS SELECT.

SQL> create table big_table_bak tablespace example as select * from big_table;

Table created.

SQL> select tablespace_name from user_tables where table_name = 'BIG_TABLE_BAK';

TABLESPACE_NAME
--------------------
EXAMPLE

We made it.