All About TABLESPACES

An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.

Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDO                           ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT

If you want a new tablespace, you can use the CREATE TABLESPACE ... DATAFILE statement as shown in the following script:
SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME  STATUS          CONTENTS
---------------- --------------- ---------
SYSTEM           ONLINE          PERMANENT
UNDO             ONLINE          UNDO
SYSAUX           ONLINE          PERMANENT
TEMP             ONLINE          TEMPORARY
USERS            ONLINE          PERMANENT
MY_SPACE         ONLINE          PERMANENT

If you have an existing tablespace and you don't want it anymore. You can delete a tablespace by using the DROP TABLESPACE statement as shown in the example below:
SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> DROP TABLESPACE my_space;
Tablespace dropped.

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
  2  FROM USER_TABLES
  3  WHERE tablespace_name in ('USERS', 'MY_SPACE');

TABLE_NAME                     TABLESPACE_NAME    NUM_ROWS
------------------------------ ---------------- ----------
MY_TEAM                        MY_SPACE           - 
EMPLOYEES                      USERS              107
...

If you created a tablespace with a data file a month ago, now 80% of the data file is used, you should add another data file to the tablespace. This can be done by using the ALTER TABLESPACE ... ADD DATAFILE statement. See the following sample script:
SQL> connect HR/fyicenter

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

SQL> ALTER TABLESPACE my_space
  2  DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
Tablespace altered.

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME                             BYTES
--------------- --------------------------------- ---------
USERS           C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600
SYSAUX          C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO            C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
SYSTEM          C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
MY_SPACE        C:\TEMP\MY_SPACE.DBF               10485760
MY_SPACE        C:\TEMP\MY_SPACE_2.DBF              5242880

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
  2  FROM USER_FREE_SPACE
  3  WHERE TABLESPAE_NAME IN ('MY_SPACE');
TABLESPACE_NAME                   FILE_ID      BYTES
------------------------------ ---------- ----------
MY_SPACE                                6    5177344
MY_SPACE                                5   10354688
This script created one tablespace with two data files.