Oracle Architecture in detail

Pull out the diagram from here

Oracle architecture is described in three categories:

• User related process
• Instance(Logical memory structure) and
• Physical file structure

Note: Instance + Database= Oracle server

Segments

Segment is the entity that uses the physical storage space within the database. Some of the common segment types are:

• Table: Stores data in columns and rows.
• Index: Improves the access to the table data. Helps in increasing the performance of data extraction.
• Rollback: Rolls back the uncommitted data.
• Partition: Divides a table into smaller pieces for performance.

As shown in the figure, group of extents make a segment. Each extent is made of a collection of oracle database blocks , Minimum size of extent is five blocks. The database block sizes are in multiples of 2 like 2k,4k,8k and 16k.

Tables and Databases

As we all know how to create a table eg: create table emp (empno number(10), ename varchar2(25)). This sql statement creates a table with two columns(empno and ename), which contains all the important information regarding the employee. Similarly oracle database also contains SYSTEM tables which store data about the database itself (metadata). Here oracle builds a views on these tables to make it easier for sql to have a look at the content of the actual tables. To be precise views pacifies the access to the metadata. Oracle 10g contains two types of metadata views.

• Data dictionary views &
• Dynamic performance views.

Data dictionary views have the names begin with DBA_, ALL_ and USER_.

1. DBA_ : DBA_TABLES as an example shows all the tables present in the database.
2. ALL_ : ALL_TALBES shows all the tables of the user(created by himself) and other tables which are granted by the DBA.
3. USER_ : ALL_USER view shows all the tables created by the user only.

Most of the views that begin with V$ are called as dynamic performance views. Eg: V$Database, V$Version and V$Sql.

Note: As a DBA we are not supposed to manipulate metadata.