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.