Creating A Table In Different Tablespace

Creating A Table In Different Tablespace

If you want to create a table under different tablespace then you need to include tablespace clause in the command,

Example:

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> 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>  select TABLESPACE_NAME, TABLE_NAME, OWNER from dba_tables where table_name in ('TTAB');

TABLESPACE_NAME                TABLE_NAME                     OWNER
------------------------------ ------------------------------ ------------------------------
EXAMPLE                                       TTAB                           HDHILLON