Setting up Automatic Undo Management

AUM will automatically allocate the undo segments and manage the undospace thus increasing the efficiency and read consistency.

Following are the steps to setup AUM:

1. Configure the following in initialization parameter

   	UNDO_MANAGEMENT = auto

2. Create a dedicated tablespace to hold the undo information.

           SQL> CREATE UNDO TABLESPACE undotbs_01 DATAFILE '/export/home/oracle/oradata/orcldir/undotbs01.dbf' SIZE 500M;

If you have got two undo tablespaces you want to use other undo tablespace (say undotbs_02) then you need to change the                                      UNDO_TABLESPACE value to the new one.

           SQL> ALTER SYSTEM SET UNDO_TABLESPACE=undotbs_02;

This will change the value in the initialization parameter dynamically.

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.

Architecture

Oracle 10g Architecture

It clearly shows you everything how oracle 10g database works. Let us do not go into the details at this point of time

Introduction

Hi!! I am using this site to share all my knowledge on oracle 10g database. This as my prep work for my final goal(ocp). If there are any worries or doubts please do comment or contact me directly to my mail. Content on this site is not out of my imagination, It is being implemented and tested before. Feel free to copy the content and distribute.

Bottom Line:

• If you have time with you please go through the definition of Database.
• Go to the Oracle Web site and stay updated.
• Can copy and distribute the content but please leave a word of appreciation before doing that.