Oracle Architecture---Logical Database Structures

Logical database structures manage the storage of physical data. They include:

TABLESPACES

A database is divided into logical storage units called Tablespaces. A tablespace is a logical construct for arranging different types of data you have in the database. An Oracle database must have at least a system tablespace. It is recommended to have different tablespaces for user and system data. One or more datafiles are created for each tablespace to physically store the data of all Logical structures. The relationship between tablespace and datafiles can be understood as: for every user-table created a System Tablespace is created.
System tablespaces exists purely as a logical unit; where as the user-tablesapces exist as physical spaces.

Oracle has provided a standard, recommended way to arrange different kinds of data in different logical units. They have even recommended conventions for naming these logical units. Lots of people still follow this way of arranging their database data and find it to be especially logical and the most effective way. Oracle has provided us with seven logical tablespaces. Let's look at all of them so as to understand their purpose in the database. The seven logical tablespaces are:

1. SYSTEM Tablespace: This tablespace is available in every Oracle database. The database cannot function without this logical unit, which stores the SYSTEM data. This data is controlled by Oracle itself and it Inserts, Updates, and Deletes this data internally. This logical unit is also the area where DBA's get most of the internal information about database functioning. This logical unit should never be deleted. The System Tablespace plays a role in almost all the transactions that occur in the database.
2. DATA Tablespace: This tablespace is the logical area where the user of the database stores all the application data. This is the area where you will store USER data. For example, a payroll application will have all of its names, salary, commission, and tax information stored in this logical unit. Depending on the amount of data, this logical unit could be huge and have many datafiles associated with it. Oracle recommends that you relate the tablespace name with its associated datafile name in some way. For example, if the tablespace name for this logical unit is PAYROLL_DATA , then you name its associated physical datafiles as payroll_data_01.dbf and the second one (if needed) as payroll_data_02.dbf . This way if you see any datafile anywhere on the system, you will have no problem relating it to the corresponding tablespace associated.
3. INDEX Tablespace: This logical unit is used in conjunction with the DATA tablespace. This stores the indexes needed by the application for a speedy response time. Indexes are logical objects created internally in the database to help in the speedy retrieval of user data and thus help in increasing the overall performance of the Application.
4. TEMP Tablespace: This is the logical unit that serves a very special purpose in the database. When users retrieve data stored in the database, they sometimes do a lot of sorting and grouping. To give you an example, suppose that a user of the database who serves a payroll application wants to see all employees who are working the Information Technology field, who are in their twenties, and who are earning more than 100K a year. To retrieve this kind of data (i.e., to show names of employees who fit this profile), the Oracle database has a lot of work to do inside. First, it must retrieve data relating to information technology personnel; then it sorts this data again to retrieve the employees between 20 and 30 years old. Again it sorts this data to take only those employees who earn more than 100K. Now if we look at the entire transaction, we see Oracle doing a lot of sorting inside the database. Where does all this happen? It happens in the TEMP tablespace, the logical unit, which allocates space to perform all these tasks.
5. USERS Tablespace: This Tablespace is created for all users who will be connecting to the database. All the users need a work area, which they can use to access data. Having such a logical unit is very helpful because it restricts users to a specific logical area, preventing access to the rest of the database, a feature making database management much easier.
6. ROLLBACK Tablespace: Let us suppose you go inside the database and write a command that increases the salary of all the employees by 20%. Immediately after writing this command, you realize you didn't intend to give them a 20% raise, only a 10% raise. So how do you undo? Oracle comes to your help in this area because it stores all data before any change is made, in a special area called rollback area. You can retrieve the old data from this holding area. This logical unit is available to hold all the undo information. The usual name given to this tablespace is ROLLBACK, or RBS.
7. TOOLS Tablespace: This is an important Tablespace that you should have if you plan to install Oracle's or any other third party tools in your database. Most of these tools need to use the SYSTEM database account in the database to create tables and indexes. Now the SYSTEM database account has the System Tablespace as its default Tablespace for the user to create tables. It is important to keep in mind that you should NEVER create any database objects in the System Tablespace even if you install any tools. Thus, to give the database user SYSTEM some place in the database to create tables and indexes needed for tools you may be installing, you should create a separate Tablespace and make that Tablespace its default Tablespace. Oracle recommends you call that Tablespaces Tools.

Schemas and Schema Objects

A Schema is a collection of database objects. Schema objects refer directly to the data.

Relationship between Schema Objects and Tablespaces:

Objects within the same Schema can be stored in different tablespaces, and the same tablespace can store objects from different Schemas. In a database named EMPLOYEE, a user has created the following objects:

• Table EMP (Physical space)
• Table DEPT (Physical space)
• Index I_EMP (Logical space)
• Index I_DEPT (Logical space)

The schema contains the objects EMP, DEPT, I_EMP, and I_DEPT; part of the EMPLOYEE database.

Relationship Between Schema Objects and Datafiles:

A schema object can span datafiles. In fact, in Oracle RDBMS, the user does not have the control of specifying in which datafile she wants the data of a particular Schema object to go. If a datafile gets exhausted, then the data of the particular object starts filling up another datafile of that tablespace. The relationship between Schema objects and datafiles are depicted.
Schema objects include structures such as:

• tables, which you can use to store data;
• views, which present data from one or more tables;
• sequences, which generate a list of numbers;
• synonyms, which provide a mask for other database objects (such as tables or views);
• indexes and clusters, which can assist in improving the performance when you retrieve data;
• procedures, functions, packages, and triggers (collectively called Program Units), which are programs that perform specific tasks;
• database links, which describe the path when using more than one database.

Table: This is the main database object within which the data is stored inside the database. Every table has columns in which it stores different kind of data. Oracle stores information about all the tables that exist inside the database. When you create a table, you specify how many columns it will have and what kind of data (number, string, date, etc.) these columns will contain. To better know how a table is organized and filled, think of a spreadsheet. In a spreadsheet you have columns and rows; similarly in an Oracle table, you have columns and rows. Columns describe the data stored and rows consist of the actual data stored. The following code is an example of how a table is created syntactically in Oracle:

CREATE TABLE EMPLOYEE (first_name varchar2(30), last_name varchar2(30), gender varchar2 (5), salary number);

The above code creates a table called EMPLOYEE, which has four columns: first_name, last_name, gender, and salary. The varchar2 and number are two datatypes of Oracle, which represent the kind of data that a column can have. The varchar2 datatype accepts numbers, letters, and any special characters. The number datatype accepts only numbers.

View: A view is an object that acts as a window, or a filter, to the data in a table. What we mean by a window is that using this object, you get to see only partial data from one or more tables. This object is not a physical object and does not have a storage clause as any other table. A view is based on the underlying table or tables and allows you to see only the columns you specifically include in that view. This object is stored in the database as a query against a table and whenever anything is trying to retrieve data from a view, the underlying query is executed and the data from pre-selected columns are obtained. Let's look at how do we create views in Oracle. To create a view based on the EMPLOYEE table, the syntax would appear as follows:

CREATE VIEW my_view AS SELECT first_name, salary FROM EMPLOYEE;

The above syntax would create a view of the employee table with only first_name and salary as its viewable columns. Thus, if you try to retrieve data from my_view , you will be able to see the first name and the salaries of employees. The chief reasons as to why we create views are: one, to get an additional level of security on the data and to restrict users from being able to see all data; two, to hide the complexity of the data. That is, a view can be based on several tables and you can isolate only the requested from all the base tables. This effectively hides how the data is stored in the underlying base tables from outside users. You can relate this feature to normalizing the database. When you normalize tables, you try to avoid redundant data by creating many tables and establishing a link between tables using Primary and Foreign Keys. A view helps you to retrieve data from normalized tables and make it (data) appear as coming from a single table.

Indexes: Indexes are database objects that help performance by speeding data retrieval. Consider the index given at the end of a book, which you use to look up any topic or word in the entire book, without having to search yourself page by page or line by line. These indexes basically help you find a topic faster. Similarly, the indexes in Oracle help you look in specific rows from data stored in a table. Indexes could be based on one or more columns of a table. An index is used if you want to filter the data based on the Primary Key. The syntax to create an index on the salary column of the EMPLOYEE table is as follows:

CREATE INDEX sal_idx
ON EMPLOYEE (salary);

Synonyms: This object in the database is just a pointer to an object like an alias. Assume there is a table called EMP0934BASE that stores all the employee data. You can create a Synonym called employee which points to the EMP0934BASE table. There are two reasons for creating a Synonym; one is to simplify an objects's name, and the other to disguise the owner of the object. Assume that two users, John and Richard, have access to the table, EMP0934BASE. If John is the owner of the table (that is, the person who created the table), then Richard can access the table only by referencing the table with John's name: John.EMP0934BASE. To avoid this unnecessary need for prefixing the table name with the name of the owner, you can create direct simple synonyms. Once you create a Synonym, Richard no longer needs to prefix it with John's name. There are two kinds of Synonyms: Private and Public. You may want to use synonyms if you want to hide the true name or identity of the table or to give simpler names for the users to use in place of more complicated names.

Privileges: This is a database object related to database security and its objects. All objects created in the database have access rights associated with them. Only users with proper rights can access a database object. There are four main privileges that can be granted on database objects by the owner. They are: SELECT, INSERT, UPDATE, and DELETE. SELECT allows the user to be able to see the data in a table or view. It does not allow the user to manipulate the data stored in it in any way. INSERT, UPDATE, and DELETE are granted to allow other users to add, change, or remove data, respectively. The syntax to grant a privilege in the database is as follows:

GRANT SELECT, INSERT, DELETE
ON EMPLOYEE TO richard;

This way you have granted three privileges on the EMPLOYEE table to the user called Richard.

Roles: Consider a scenario where you have 1,000 users and they all are developers. There are 10 tables in the database and all users need to have SELECT and INSERT privileges on all the 10 tables. To give each user privileges for each table separately would require two days of writing a similar SQL over and over again. There is, though, another more efficient way. You can create a role (let's call it development ) and grant all the necessary privileges to this single role and then simply grant this role to all the users. This way, if you want to provide UPDATE privilege on one table to all the users, you can simply grant it to the role instead of granting it to each and every user. Thus, a role makes the management of privileges more transparent and more manageable. The following figure shows how the role called development fits in the picture. You grant privileges to the role, then grant the role to all the users.

Data Blocks

A data block is the smallest unit of Input/Output used by the Oracle database. Though the underlying operating system stores the data in arrays of bits and bytes, Oracle writes and reads in chunks of database blocks. One datablock corresponds to a specific number of bytes of physical database space on disk. The size of data block for any database is fixed at the time of creation of the database; to change it you will have to recreate the database. Thus the data block size for that database should be chosen very carefully. Some values of the data block size are 2KB, 8KB, 16KB, and 32KB. Oracle recommends a size of 8KB for a typical Online Transaction Processing (OLTP) database.

Extents

An extent is the next level of data storage. One extent consists of a specific number of contiguous data blocks allocated for storing a specific type of information. One noteworthy aspect of extents is that storage values for all physical objects created in the database are in number of extents. One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment. When you try to create any physical object in the database, storage parameters expressed in terms of extents define every segment you wish to create for that object. Thus, in the storage clause of the CREATE TABLE statement, you can determine how much space is initially reserved for a table's data segment.

Segments

Segment is the level of logical database storage above an Extent. A segment consists of a set of extents assigned for a specific Logical structure. Each table's data is stored in its own single segment. Similarly, each index's data is stored in a single segment. The different types of segments are the data segments, index segments, rollback segments, and temporary segments.

TIP: You can remember the order of levels of the logical storage structures through the acronym 'DEST' (Datablocks, Extents, Segments, Tablespaces).

More extents are automatically allocated by Oracle to a segment if its existing extents become full. An important point to be remembered is that although an Extent consists of contiguous data blocks (i.e., data blocks adjacent to each other on the same datafile), a segment may not consist of contiguous Extents; thus, the Extents constituting a segment may lie on different datafiles.

There are four main types of segments that are used in the Oracle database. They are:

1. Data segment: This type of segment is created by Oracle when you create a non-clustered table or a cluster inside an Oracle database. Thus, a non-clustered table, or a cluster, has a single data segment to hold all its data.
2. Index Segment: Like the data segment, every index in the Oracle database has an index segment to hold all its data. A CREATE INDEX command creates this kind of segment.
3. Temporary Segment: Many commands used to communicate with the database create a temporary segment which Oracle uses for a transaction. These segments are automatically created and dropped by Oracle.
4. Rollback Segment: These are the undo segments which Oracle has for its use to store the undo information. Every database has several of these segments. The database can function only if there is at least a single rollback segment.

Logical Structures:

Schema Objects --> Need storage space
Data Blocks --> Stored in one or more datablocks
Extends --> One or more continuous data blocks form an extent
Segments --> A set of extends form a segment; when full Oracle creates/allocates space.
Tablespace --> Includes above structures and form a database