Create Tablespace

Note:

Before we start please note that following command is use to check the tablespace detail with the datafiles and the size

SELECT tablespace_name, file_name, bytes/1024/1024 megabytes FROM dba_data_files;

The bytes column storing the datafile size in bytes therefore I need to devided by 1024 twice to show in Megabyte.

To check the status of the tablespace you can use following script

SELECT a.tablespace_name, a.file_name, a.bytes/1024/1024 megabytes, b.status
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name;


1. Create Simple Tablespace

Following is the most basic SQL command creating Tablespace.

CREATE TABLESPACE my_first_tbs
DATAFILE '/datafiles/edba01/edba01/my_first_tbs_01.dbf' SIZE 10m;

To create a tablespace there are 3 basic information you need supply
1. tablespace name for
the example is my_first_tbs
2. the datafile include the full path where the datafile will be store
for the example /datafiles/edba01/edba01/my_first_tbs_01.dbf
3. The initial size of the datafile
for the example 10m (m is megabyte)

2. Creating Tablespace with multiple datafiles

If you read the Oracle concept it said that tablespace can have multiple datafiles. And the datafile is only associated to 1 tablespace.

Following is the example command

CREATE TABLESPACE my_tbs
DATAFILE '/datafiles/edba01/edba01/my_tbs_01.dbf' SIZE 5m,
'/datafiles/edba01/edba01/my_tbs_02.dbf' SIZE 5m;