Oracle DBA Basics

Tablespace Management

What is tablespace?

Oracle database are logically divided into one or more tablespaces. An oracle tablespace is a logical entity that contains the physical datafiles.

Tablespaces are store in all the usable data of the database and the data in the tablespace are stored in one or more datafiles.

Datafiles are Oracle-formatted operating system files. Oracle creates a datafile for a tablespace when you specify the keyword DATAFILE during tablespace creation

Below is the example to create the Tablespace name called USERTS

SQL > Create tablespace USERTS datafile '/d01/oracle/product/oradata/user01.dbf' size 50m;

                                   

The tablespace is a purely logical construct and is the primary logical storage structure of an Oracle database.

A tablespace can have one or more datafiles, and a datafile can belong to only one tablespace.

 

What is datafile?

Oracle data file is a part of the physical structure of oracle database used to store the data, including users data & undo data & they stores the data in
the form of tables & index data.

Data files are grouped together into tablespace. When Oracle first creates a datafile, it’s empty but storage  is allocated exclusively for Oracle’s use.

The free space shown by the “df -k” command shows it as used space from the operating system’s point of view.

 

How big you can make table space?

The tablespace size is depending on the size of your tables and indexes and the total amount of data in the tablespace.

There are no rules about the minimum or maximum size of tablespaces (the maximum size is too large to be of any practical consequence). It is quite

common to have tablespaces that are 100GB in size coexisting in the same database with tablespaces as small as 1GB or even much smaller.

Note: 

  • The size of a tablespace is the sum of the sizes of the data files that contain its data.
  • And if you add up the sizes of the tablespaces or the sizes of all the data files, you will get the size of the database itself.