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
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
No comments:
Post a Comment