How to Create TableSpaces - Oracle DBA

Introduction

Developed originally in 1977 by Lawrence Ellison and some other developers, the Oracle Database is considered among the most trusted and popular relational database engines in the world. The Oracle database is a relational database management system (RDBMS) that is managed by the Oracle Corporation.

The system works on the basis of a relational database framework where the users can access the data objects or the application front end through the structured query language (SQL). The architecture is fully scalable and the software is used by global enterprises. The database finds use in managing and processing data across the local and wide area networks. A network component is provided by the Oracle database itself to allow communications. It is sometimes known as Oracle or Oracle RDBMS.

What are Relational Databases and a Database Management System?

An organization might need to store data and meet certain requirements. For instance, human resources records for the employees is kept by every organization. Information storage and processing are performed by the information system. This information system could be a physical database. However, organizations try to automate their information systems. These databases are treated as organised collections of information and each of them is treated as a single unit. A database serves the purpose of collecting, storing, and retrieving information needed by the information systems.

The database management system is responsible for controlling the organization, storage, and retrieval of data. There are some elements in DBMS:

• Kernel code - The kernel code manages the storage and memory of the DBMS
• Metadata repository - This repository is also called the data dictionary
• Query language - Applications are enabled to access data through this language

What is a relational database management system (RDBMS)?

The relational model stands as the basis of the relational database management system. The RDBMS moves the data into a database, stores, and retrieves the data so that proper manipulation can be done by the applications. RDBMS can distinguish between two types of operations:

• Logical operations - The application specifies the contents required, like an employee name or record
• Physical operations - The RDBMS determines how the operations are to be performed. 

The front-end application would query the table and the database would use the index to find the requested rows. The data would then be read into memory and other steps would be performed before a result is returned to the user. The RDBMS stores and retrieves data and physical operations are transparent to the database applications.

Tablespaces, Datafiles, and Control Files

The data is stored logically in the tablespaces and in the datafiles, physically. The thing is that the databases, datafiles and tablespaces are closely intertwined. However, their differences are important:

The Oracle database is compromised by a single or more than one logical unit of storage which are known as tablespaces. They collectively store the data of the database. Each of these tablespaces in the Oracle database would contain one or multiple files and these are known as the datafiles. These are physical structures that conform to the operating system where Oracle is running.

The data in the database would be kept in datafiles and they constitute each of the tablespaces in Oracle. To take an instance, the simplest database in Oracle could contain one data file and one tablespace. There might be another with four tablespaces, each having three data files, with a total of twelve.

What is a Tablespace in Oracle?

Oracle does not require you or the database administrator to consider the operating system files that comprise the Oracle database. Operators can be specified in terms of database objects rather than filenames. The standard file system is used by Oracle to create and delete files and the database structures include tablespaces, control files, and redo log files.

So, what actually are these Tablespaces? The database is partitioned into a few logical units of storage known as tablespaces. These tablespaces are divided into logical units of storage and are known as segments. These segments are further divided into extents. These extents are collections of contiguous blocks. There are a few things about tablespaces that help build the concepts.

The size of the tablespace denotes the size of the data files that make up the tablespace. This means a database can be enlarged in three ways - a new datafile could be added to the tablespace, an entirely new tablespace could be added or the capacity of an existing datafile could be increased.

When a new data file is added to an old tablespace, the space that had been allowed to the corresponding tablespace is increased. Another way to increase the size of the database is to create another tablespace that has additional data files. The last option for enlarging the database by changing the capacity of these data files in the tablespaces makes them grow dynamically when more space becomes needed. This can be achieved by altering the existing datafiles or through the addition of files having dynamic extension capabilities.

Checkout Oracle DBA Tutorial

Bigfile Tablespaces

Oracle permits the creation of big file tablespaces, allowing the Oracle Database to have tablespaces that are made of a single large file instead of numerous small ones. Oracle Database thus helps to maximize the ability of the 64-bit systems to create and manage the ultra-large files. This allows the Oracle Database to have databases that can be 8 exabytes in size.

The big file tablespaces make the datafiles completely transparent for the users through the Oracle-managed files. This means the operations can be performed on the tablespaces rather than the datafiles contained in them. The big file tablespaces make the tablespaces the primary unit of the disk space administration, recovery and backup, and almost everything else.

Both small file and big file tablespaces can be contained in an Oracle database. The different types of tablespaces would be indistinguishable when it comes to the SQL commands which have no mention of the datafiles. A group of temporary tablespaces can be created as well that would let the user consume temporary space from the multiple tablespaces. A tablespace group could also be specified as the default temporary tablespace for a particular tablespace. This feature becomes v3ery useful for the big file tablespaces where a lot of temporary tablespaces could be needed for sorting.

[Related Blog: Bigfile Tablespaces]

What are the benefits of bigfile tablespaces?

Bigfile tablespaces make database management much easier as everything is kept in a single file and not in small parts. The following are some of the benefits of such tablespaces.

The storage capacity of an Oracle database can be significantly increased by the bigfile tablespaces. The smallfile tablespaces have the capacity to hold as many as 1024 files. On the other hand, the big file tablespaces have the capacity to hold only one file that can be 1024 times larger than a small file tablespace. 

There is a limit of 64 thousand data files for each database, which means the maximum size of the Oracle database could be 8 exabytes with big file tablespace with the maximum block size.

The management of datafiles in the ultra-large databases is simplified by reducing the number of datafiles needed. The parameters can also be adjusted to reduce the SGA space required for datafile information and the size of the control file. Datafile transparency simplifies database management.

What are the considerations while using big file tablespaces?

The considerations while using bigfile tablespaces are as follows.

1.  Automatic Storage Management is to be used with bigfile tablespaces or other logical volume managers that support the dynamically extensible logical volumes and RAID or striping.
2. It would be better to avoid bigfile tablespaces on systems that do not support striping as there could be negative implications for RMAN backup parallelization and parallel execution.
3. It is not recommended to use bigfile tablespaces on those platforms where large file sizes are not supported. Tablespace capacity can be limited. It is important to refer to the documentation of the operating system to know about the maximum supported file sizes.
4. The performance would improve if data is stored in the bigfile tablespaces rather than the traditional ones in terms of checkpoints, database opens and DBWR processes. Increasing the datafile size could increase the time required to create a new datafile or restore a corrupted file.
5. Using bigfile tablespaces could be avoided if there could be no free space available on a disk group and the only way to extend a tablespace is to add a new datafile on a different disk group.

Types of Tablespaces in Oracle

The three underlying ones are the different varieties of tablespaces supported in Oracle.

• Permanent - Permanent tablespaces are to be used to store the application and user data. The Oracle Database stores permanent data in permanent tablespaces, for instance, system data. Also, each user would be assigned a default permanent tablespace.
• Undo - The undo tablespace is transparently created by a database that is running in the automatic undo management mode where undo data is managed. Oracle Database would use undo data if there is a need to provide read consistency or roll back certain transactions. This also helps with database recovery or enabling features like Oracle Flashback Query. Only an active singular undo tablespace can be present in a database instance.
• Temporary - The temporary tablespaces store the temporary data which are created when sort operations are performed by the SQL statements. One temporary tablespace is offered to the Oracle database during the time of its creation. Another temporary tablespace would be created if a temporary tablespace group is created. Creating additional tablespaces would not be necessary under the typical circumstances. Configuring extra temporary tablespaces might be required if there is a huge database.

Permanent Tablespaces

A permanent tablespace contains some persistent schema object where data can be stored in the permanent tablespaces. The data would persist beyond the duration of a transaction session. The objects in the permanent tablespaces are stored in data files.

Connecting to the Database

The first step involved in the process is to connect to the database. This can be done by the sys user or any user having CREATE TABLESPACE system privilege or sysdba privileges.

How can smallfile permanent tablespaces be created?

The CREATE TABLESPACE, a DDL statement is used for the creation of a tablespace. Both of these are Oracle reserved keywords. It is possible to explicitly specify the creation of a smallfile tablespace by mentioning the keyword 'smallfile' between CREATE and TABLESPACE.

Creating a smallfile tablespace

The step is quite optional as Oracle would create smallfile tablespaces by default. However, it is considered a good practice to make the code more readable. You would need to give a name to the tablespace after the CREATE SMALLFILE TABLESPACE statement. The name is user-defined and it can be named anything.

The Datafile clause

One or more datafiles can be added to the tablespace using the datafile clause. Tablespaces are logical storage units with one or multiple datafiles. The DATAFILE clause has to be written and the name of the datafile and the directory path is to be offered inside the single quotes. The directory path parameter can be avoided. In this case, the Oracle engine would create the datafile and place it in the default directory. Specifying the size of the datafile through the SIZE clause is, however, mandatory.

Checkout Oracle DBA Interview Questions

The following is an instance of how the datafile clause can be used:

CREATE SMALLFILE TABLESPACE employees
DATAFILE
    'C:apptbsp_Demoemp1.dbf' SIZE 10M
    'C:apptbsp_Demoemp2.bdf' SIZE 10M
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
SEGMENT SPACE MANAGEMENT AUTO;

Creating bigfile permanent tablespaces

A few things need to be taken care of while creating a bigfile tablespace. Only a single datafile can be present and writing the BIGFILE keyword is mandatory.

CREATE BIGFILE TABLESPACE employee
DATAFILE
    'C:appmannoradatatbsp_Demoemp1.dbf' SIZE 1G
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Tempfiles

Tempfiles are not fully initialised or sparsed files, unlike the normal data files. Oracle writes only to the header and the last block of the file when a TEMPFILE is created. Thus, it is much quicker to create a TEMPFILE than creating a normal database file. The Tempfiles are not recorded in the control file of the database. 

This means that these can be recreated when the database is restored or if they are deleted by accident. Thus different Tempfile configurations are possible between the standby and permanent databases. Tempfiles can also be local and not be shared in a RAC environment.

Datafiles cannot be removed without dropping a tablespace, while it is possible to remove a TEMPFILE.

How to create a temporary tablespace?

Various ways of creating temporary tablespaces have been provided by Oracle. The most recent methods should be used:

• Before Oracle 7.3 - CREATE TABLESPACE temp1 DATAFILE ...;
• Oracle 7.3 and 8.0 - CREATE TABLESPACE temp2 DATAFILE ... TEMPORARY;
• Oracle 8i and later - CREATE TEMPORARY TABLESPACE temp3 TEMPFILE ...;

[Related Blog: Temporary Tablespaces]

Difference Between Tablespaces and Datafiles

Tablespaces	Datafiles
Any Oracle database would consist of one or more logical storage units that are known tablespaces.	One or more datafiles are contained in each tablespace of an Oracle database.
The tablespaces collectively store all the data of the database.	Data files are physical structures and they conform to the underlying operating system.
Tablespaces can be of three different types - permanent, undo and temporary.	In addition to datafiles, there are Tempfiles which are not recorded in the control file of the database.

The use of multiple tablespaces offer the database administrators more flexibility in database operations. The users can perform a multitude of tasks like the separation of the data in data dictionary from user data or separating the data of one application from another, thus preventing multiple applications from being affected. Multiple tablespaces even offer the option to take individual ones offline, while keeping the others online. Thus tablespaces enable optimum usage of the data storage in a database.