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.
Table of Contents
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:
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:
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.
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.
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.
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]
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.
The considerations while using bigfile tablespaces are as follows.
The three underlying ones are the different varieties of tablespaces supported in Oracle.
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.
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.
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.
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.
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;
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 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.
Various ways of creating temporary tablespaces have been provided by Oracle. The most recent methods should be used:
[Related Blog: Temporary Tablespaces]
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.
Name | Dates | |
---|---|---|
Oracle DBA Training | Oct 15 to Oct 30 | View Details |
Oracle DBA Training | Oct 19 to Nov 03 | View Details |
Oracle DBA Training | Oct 22 to Nov 06 | View Details |
Oracle DBA Training | Oct 26 to Nov 10 | View Details |
Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.