How To Create a Table in a Specific Tablespace?
Page 1 of 1
How To Create a Table in a Specific Tablespace?
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/fyicenter
Connected.
SQL> CREATE TABLESPACE my_space
2 DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE my_team TABLESPACE my_space
2 AS SELECT * FROM employees;
Table created.
SQL> SELECT table_name, tablespace_name, num_rows FROM USER_TABLES
2 WHERE tablespace_name in ('USERS', 'MY_SPACE');
TABLE_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ---------------- ----------
MY_TEAM MY_SPACE -
EMPLOYEES USERS 107
SQL> connect SYSTEM/fyicenter
Connected.
SQL> CREATE TABLESPACE my_space
2 DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE my_team TABLESPACE my_space
2 AS SELECT * FROM employees;
Table created.
SQL> SELECT table_name, tablespace_name, num_rows FROM USER_TABLES
2 WHERE tablespace_name in ('USERS', 'MY_SPACE');
TABLE_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ---------------- ----------
MY_TEAM MY_SPACE -
EMPLOYEES USERS 107
Similar topics
» How To Create a New Table by Selecting Rows from Another Table?
» How To Create a New Table by Selecting Rows from Another Table?
» How To Create a New Tablespace?
» How To Create a Testing Table?
» How To Create a Table Interactively?
» How To Create a New Table by Selecting Rows from Another Table?
» How To Create a New Tablespace?
» How To Create a Testing Table?
» How To Create a Table Interactively?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal