Database Table spaces

Database Tablespaces

Introduction

            A database refers to a systematic collection of data files together with the programs and commands that manipulate the data files. An Oracle database stores two types of information which are the user data and system data. User data is relevant and important to a certain computer application while the system data is the data that the database requires for self-management. A database is, therefore, made up of the components such as database files, control files; redo logs, extents, segments, and Tablespaces.

Tablespaces

            A tablespace is a logical group and storage of data files while in the database. A typical database contains, at least, a tablespace, but it usually has two or more tablespaces. The tablespace resides inside and plays a role as a computer’s hard drive folder. Each of the logical tablespaces in the database corresponds to one r several physical database files. It is worth to note that there is a close relationship between databases, tablespaces, and data files. However, they have significant differences. An Oracle database contains the following typical tablespace (Rabl, Pfeffer, & Kosch, 2008):

·         SYSTEM: Stores all the information required for self-management

·         TEMP: Storage of all temporary files

·         TOOLS: Storage of database objects required to support various tools

·         USER: Stores information about the users

·         DATA & INDEX: Stores the actual data together with the indexes

·         ROLLBACK: Stores all the information required for undo

Bigfile Tablespace

            Oracle Database lets the user create bigfile tablespaces thereby allowing the Oracle database to hold tablespaces consisting of single large files instead of several smaller files. The Oracle database is then able to utilize the ability of 64-bit systems thereby managing and creating ultra large files. This results into the Oracle Database being able to scale up 8 exabytes in size. With files being managed by Oracle, bigfile tablespace makes data files appear completely transparent to the users. This simply means that a user can perform operations on tablespace instead of underlying the data file. Therefore, bigfile tablespaces make the tablespace the major unit of the administration of the disk space, recovery, and backup among others. They also help in simplifying the management of data file with Oracle-managed Files and Automatic Storage Management through the elimination of the need for adding new data files and dealing with several files (Kurtz, 2012).

Smallfile Tablespace

            A smallfile tablespace is an old or the traditional Oracle tablespace that has the capability of holding 1022 datafiles or tempfiles. Furthermore, each of the 1022 datafiles or tempfiles has the capability to hold approximately 4 million (222) blocks. A smallfile tablespace is made up of four pieces format like OOOOOOFFFBBBBBBRRRR where (Boszormenyi & Schonig, 2013):

·         OOOOOO represents the data object of the whole segment

·         FFF represents the tablespace-relative to the datafile number in which the datafile row is contained

·         BBBBBB is the data block in which the row is contained

·         RRR is the slot number that helps to identify the row inside a certain block

A smallfile tablespace provides the database user with a layer that helps in abstraction between the physical data and logical data. Its main function is allocating storage for all the segments managed by the DBMS (Lujan-Mora & Trujillo, 2006).

Comparison between Bigfile and Smallfile Tablespaces

            Bigfile tablespace has a datafile transparency while smallfile tablespace does not have the feature. Therefore, bigfile tablespace helps to simplify the management of the database by providing the transparency required for the datafile. Furthermore, bigfile tablespace requires a reduced number of the datafiles while it is exact opposite with the smallfile tablespace in that they require a lot of datafiles. Therefore, bigfile tablespaces help in simplification of the management of datafiles in large databases. The parameters are also adjustable thereby reducing the SGA space that datafile information and control file size require (Wrembel, 2009).

            Smallfile tablespace can contain 1024 files only. Bigfile tablespaces can contain one datafile that is 1024 times larger than smallfile tablespace. Therefore, bigfile tablespace leads to significant increase in the storage capacity of the Oracle database. Smallfile and bigfile tablespace have similar total tablespace capacity. However, each database is limited to 64K and because a database can contain 1024 times more bigfile tablespaces than the smallfile tablespaces, leads the bigfile tablespaces increasing the total capacity of the database by a magnitude of order 3. In my database, I would use bigfile tablespace because of the transparency, large storage space, and simple database management as explained above (Rubio & John, 2005).

Conclusion

            This paper has discussed the tip of the iceberg of the bigfile and smallfile tablespaces and tablespaces in general. There is more specific information about the topics that underlie tablespace in general. Therefore, there is a dire need to unearth more information about tablespaces and everything about it. Having information about the phenomena will enable the database admin to make the correct choice while selecting the table space that is suitable for their database.

References

Boszormenyi, Z., & Schonig, H.-J. (2013). PostgreSQL replication: Understand basic        replication concepts and efficiency replicate interruptions. Birmingham: Packt             Publishing.

Kurtz, D. (2012). PeopleSoft for the Oracle DBA. Berkeley, CA: Springer.

Lujan-Mora, S., & Trujillo, J. (2006). Physical modelling of data warehouse using UML    component and deployment diagrams: Design and implementation issues. Journal of           Database Management, 17 (2), 12-42.

Rabl, T., Pfeffer, M., & Kosch, H. (2008). Dynamic allocation in a self-scaling cluster database.   Concurrency and Computation: Practice and Experience, 20 (17), 2025-2038.

Rubio, J., & John, L. K. (2005). Reducing server data traffic using a hierarchical computation       model. IEEE Transactions on Parallel and Distributed Systems, 16 (10), 933-943.

Wrembel, R. (2009). A survey of managing the evolution of data warehouse. International            Journal of Data Warehousing and Mining, 5 (2), 24-56.

Sherry Roberts is the author of this paper. A senior editor at MeldaResearch.Com in customized term papers if you need a similar paper you can place your order for research paper custom.