Logical Structure of Database (Continue)

Don’t confuse it with the logical structure of oracle server i.e. instance. It is the logical structure of database which is the physical part of oracle server. In other words it is the logical structure of the physical part of oracle server. Oracle database can be divided into two parts. One is physical and the other is logical. Physical part of database consists of Data Files and Operating System Blocks. In logical part a database consists of many tablespaces; which in turn consist of many segments. A segment is consisting of many extents and an extent is consisting of many data blocks or they are also known as oracle blocks. I am going to discuss each of them in detail here and in subsequent posts. Logical part can be mapped to the physical part and mapping is already what we know i.e. a tablespace consists of many data files and an Oracle Data Block consists of many Operating System Blocks.

Managing Tablespaces:

In very simple words a tablespace is the logical container of the data stored in the database. Usually a large database is divided into many tablespaces. Remember that tablespace is a logical construct and on physical level only data files exist. Usually a tablespace consists of many data files. Broadly speaking a tablespace will come in one of the following two types.

1. Permanent Tablespace: A permanent tablespace is a tablespace that stores the permanent data. It has two further types.
• System: It stores the data for the system i.e. oracle server. And as we know that oracle server stores all its data in the data dictionary. So you can say that system tablespace stores data dictionary.
• Non System: It stores all the user data that is of permanent nature.
2. Temporary Tablespace: It stores the data that is of temporary nature. For example when you are sorting you need a temporary storage to hold values for some time.

As mentioned system Tablespace stores the data dictionary. It also stores one more thing and that is programs. By programs I mean the triggers, stored procedures etc. In oracle you can create tablespaces of your own. If you do not create oracle does not generates any errors. But it is highly recommended that you create because it helps in managing the database. How? Suppose you have an ERP system in which there is department of sales, another department of production and yet another department of marketing. If you have stored all the data in one tablespace it will be a headache for you to manage it. But if you hade created a separate tablespace for every department; than it will be easy to mange the data of each department. This is called a modular approach. Let us clear it with an example. Suppose at any instant of time you are required to make the data of sales department unavailable for the users. If you have stored all of your data in one tablespace than you have to shutdown your entire database. But if you are following a modular approach i.e. you have created a separate tablespace for every department. Than to make the data of sales department unavailable all you need is to make that tablespace offline. There will be no effect of this on other tablespaces and on database.