Skip to content

Create Table as Select (CTAS) to Tablespace

  • by

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.

Leave a Reply

Your email address will not be published. Required fields are marked *