Table space – Important concept of Database
Wondering what a Table space is!!!!!!!!
Database is divided into one or more logical storage units known as table space. The table space is created by database administrator. After the table space gets created by administrator tables can be created by users in their table space in other words tables can be created in the table space provided for the users.
Also one has to understand that table space is a logical area of storage but data present in table occupy physical storage space. The relationship between them is after table space gets created it is related or associated to physical data. Since table space is related to data fields the size of the table space is equal to the combined or collective size of data files related to that table space. And the size of database is equal to the combined size of all table spaces.
Types of Table spaces
SYSTEM Table space
Before getting into the details of what a system table space is it is important to know the concept of data dictionary in database. The data dictionary has information about user name, the privilege of users, roles of users, names and properties of objects like tables, views, synonyms, indexes and so on ,information about constraints created and so on. So data dictionary is an important part in a relational database management system technology.
From the above it is clear that somewhere this data dictionary must get stored. This is the place where SYSTEM table space comes in. In order to store the data dictionary tables of Oracle database a table space is created called as system table space. It is also known from the above description that every database has SYSTEM table space necessarily.
While studying about table space one must also known the options available in table space creation or the modes in which the table space can be created. There are two options in creating table space. They are namely:
Online Table space
Offline Table space
One can create a table space in any one of the two modes. But it is a must that SYSTEM table space must be created as online table space only. To know why it is so we must first understand what an online and offline table space. Online table space is one in which the table space created are available to users once the database is opened. Offline table space on the other hand is one in which the table space created is not available to users .So from the above definition it is very clear that since data dictionary must be available to users the place of storage of this data dictionary namely the system table space must be created as online table space only.
Temporary Table space
This is another type of table space which gets created temporarily for users. This table space gets created for each user when the users need to store temporary objects. Thus it is possible to create this temporary table space for each user.
Default Table space
This gets created when the as soon as the user name gets created and hence the name default table space. For each user name there is an associated default table space. This table space is used when the user does not explicitly provide table space names while creating any object. In such scenarios the default table space is used and gets associated with the object.
Having seen in detail about table space now let see the benefits or advantages of using table spaces
Maintenance: We have seen that the table space is logical storage of objects and also since we have seen that the table space that is present in all namely SYSTEM table space have the data dictionary. Because of this reason table space helps to maintain objects in a structured way and monitor the growth or size of object occupied by each. In other words helps in maintaining.
Storage: Since using table space it is possible to view the objects storage it is possible to maintain storage. In other words if a user used more storage space it can be viewed and it is possible to set limits for the storage of the users and so on.
Ease work of DBA: In other words it is possible to structure objects efficiently and also conveniently by using table space. To explain this concept let us see an instance. For example there may be many old data which when viewed for access time is found to be used only long time before. That is there may be data which have not been accessed. But those data could be important and because of the reason could not be removed.
In such a scenario what can be done is it is possible for the DBA or database administrator to create a separate table space for this and name it as say archive table space and place all the data in this table space and make this table space offline. In other words make this table space not accessible to users. Now one can remove the data from disk and free the space which can be used for some other purpose. Like the above scenarios there is lot more ways in which database administrator’s work is eased and efficient management of database is possible by use of table space.
Increase Performance: Apart from the above advantages table space by allocating spaces and managing efficiently it is possible to improve the performance of objects.
