Create table in different tablespace

If you want to create the table under different tablespace then you need to include tablespace clause in the command, the example is as below:

Identify the default tablespace of the user

SQL> select DEFAULT_TABLESPACE, USERNAME  from dba_users where username in ('HDHILLON');

DEFAULT_TABLESPACE             USERNAME
------------------------------ ------------------------------
MICHAEL                        HDHILLON

Create table as user which will go under user's default tablespace

SQL> create table ttab as select * from dual;

Table created.

SQL>  select TABLESPACE_NAME, TABLE_NAME, OWNER from dba_tables where table_name in ('TTAB');

TABLESPACE_NAME                TABLE_NAME                     OWNER

------------------------------ ------------------------------ ------------------------------

MICHAEL                        TTAB                           HDHILLON

SQL> 

SQL> drop table ttab;

Table dropped.

SQL>

Create table with the tablepspace clause, now table should be create under EXAMPLE tablespace.

SQL>  create table ttab tablespace EXAMPLE  as select * from dual;

Table created.

SQL> 

SQL>  select TABLESPACE_NAME, TABLE_NAME, OWNER from dba_tables where table_name in ('TTAB');

TABLESPACE_NAME                TABLE_NAME                     OWNER

------------------------------ ------------------------------ ------------------------------

EXAMPLE                        TTAB                           HDHILLON

SQL>