Oracle Database Interview Question:

How To Create a Table in a Specific Tablespace?

Submitted by: Administrator

After you have created a new tablespace, you can give it to your users for them to create tables in the new tablespace. To create a table in a specific tablespace, you need to use the TABLESPACE clause in the CREATE TABLE statement. Here is a sample script:

SQL> connect SYSTEM/globalguideline
Connected.

SQL> CREATE TABLESPACE my_space
2 DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> connect HR/globalguideline
Connected.

SQL> CREATE TABLE my_team TABLESPACE my_space
2 AS SELECT * FROM employees;
Table created.

SQL> SELECT table_name, tablespace_name, num_rows
2 FROM USER_TABLES
3 WHERE tablespace_name in ('USERS', 'MY_SPACE');
<pre>TABLE_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ---------------- ----------
MY_TEAM MY_SPACE -
EMPLOYEES USERS 107
...</pre>
Submitted by: Administrator