Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login

You must be logged to download. Click here to login


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Concepts of Oracle Database

Today we are going to learn “basics of Oracle” .This is a series of Oracle tutorials in which the first part is Introduction to Oracle for beginners. This first part explains the relational theory on which database are built.

What is Oracle Database?

Oracle Corporation primary business is to deal with the database and its products.

Oracle Database is a relational database (A database in which data are stored in the form of relation (tables) of 2-dimension which have a row as “Tuple” and column as “domain”) with a features of object oriented programming.

An Oracle database is a set of logically related data treated as a unit whose purpose is to store and retrieve information efficiently. A database server is the key for solving the problems related to Information management. A server has to handle bulk amount of data for concurrent access in multiuser environment.

Oracle Database is the first database designed for enterprise computing, the most flexible, reliable and cost effective way to manage information and its related applications. There is no need to be bother, because capacity can be easily added or reallocated from the resource as needed.

The database has a Physical and Logical Structure both are separated so that will not interfere in each other structure and we can change any of it without affecting other.

Oracle Logo

Physical Database Structures:

Physical Database Structure of Oracle has following components:

  • Datafiles
  • Control Files
  • Online Redo Log Files
  • Archived Redo Log Files
  • Parameter Files
  • Alert and Trace Log Files
  • Backup Files


Each Oracle Database has one or more files which stores data and data of logical database structures are stored in a data files allocated for that database.

Features of Data Files:

  • One or more data files form a logical unit of database storage called tablespace.
  • A data file can be related to only one table space.
  • Datafiles can be defined to extend automatically when they are full.

Control Files

A database also have a Control File which specify the physical structure of the database, including Database name, Names and locations of datafiles and redo log files and Timestamp of database creation.

Online Redo Log Files

Each Oracle database has two or more online redo log files. These online redo log files are merged with the duplicate of redo log files, which are known as the redo log for the database.

Archived Redo Log Files

Archived redo log files are are created by database and placed in offline mode of online redo log files. It archives redo log files when the database is in desired mode.

Parameter Files

Parameter files have a list of configured parameters needed to configure a file. Both Piles (parameter files) and spfiles (server parameter files) allow us to store and manage initialized parameter permanently at server-side disk. A server parameter file has some additional advantages:

The file is updated simultaneously when some parameter values are changed in the active instance.

The file is located at center for access by all instance of database.

Alert and Trace Log Files

Associated Trace files are generated by each server and log files and if an internal error is detected by a process, the process pool inform about the error to its associated trace file. Some of the data written to a trace file is meant for the database administrator, while other information is useful for Support Services of Oracle. Trace file information is also used to tune applications and instances. The alert files a special trace file. The alert log of a database is a log of messages and errors.

Backup Files

To restore a file take backup of it. Generally we restore a file when a user or media resources damaged or deleted original file data.

User-managed backups and recovery mechanism requires us to generally restore backup files before we can perform a trial recovery of the backups.

Server-managed backups and recovery manages task the backup process, such as recovery process, as well as the scheduling of backup, such as apply it to the correct backup file when recoveries are essential.

Overview of Logical Database Structures

This section discusses logical storage structures which comprise of data blocks, extents, segments, and tablespaces. These structures (logical storage) enable Oracle Database to have fine-grained control of disk space use.

This section has following components:

  • Oracle Database Data Blocks
  • Extents
  • Segments
  • Tablespaces

Oracle Database Data Blocks

Oracle Database data is stored in data blocks. One data block represents a particular amount of number of bytes of physical database space on disk. The standard block size is allocated by the DB_BLOCK_SIZE initialization parameter. In extension, we can declare up to four other block sizes too. A database uses and allocates free database space to data blocks of oracle.


The next logical database is an extent. A extent is a specific number of contiguous blocks of data, obtained in a single allocation and used to store a particular type of information.


Above extents, the level of logical database storage is a segment. A segment is a set of extents which is allocated for a rollback segment, index, table, or for temporary use by a session, SQL parser or transaction. In relation to physical database structures, all the extents belonging to a segment exist in the same table space, but they can be in different data files.

When the extents of a segment are full or occupied, Oracle dynamically allocates another extent for that segment. The extents of a segment may or may not be contiguous on disk because it is allocated only when needed.


Each database is divided logically called Tablespaces, which consist of related segments, blocks, extent and data. For example, tablespaces group together all application objects to make efficient some administrative operations.

One or more datafiles are explicitly created for each tablespaces to physically store the data of all logical structures in a tablespaces.

Advantages of Oracle Database:

  • Center of management for admin and data
  • Follow a Standard

Disadvantages of Oracle Database:

  • Inefficient for Recursive processing
  • Incompability and Complexity

Let us take the example of a very simple database with just 2 tables:

  • Student
  • Course

The Student table has just three columns:

  • student_id
  • employee_ name
  • course_id

The Course table has just two columns:

  • course _id
  • course _name

There is a relationship between these two tables via the course_id column in each table. The course_id column in the Student table relates to the course_id column in the Course table. This enables we to assign courses to student and determine which student studies which course.

Syntax of Creating a table in Oracle:

Create Table <TableName>(“column1” “data type”,”column2” “data type”,……………..);


Creating a Student Table in Oracle:

Create Table  Student(student_id  varchar2(10) primary key,  student_name  varchar2(25),course_id references Course);


Table Created

Creating Course Table:

Create Table  Student(course_id varchar2(10)  primary key,  course_name varchar2(25));


Table Created

Inserting Data into Tables:

INSERT INTO <TableName> (column1,column2) VALUES(Value1,Value2);


Inserting a Data in CourseTable:
Insert  into  Course (1,’BCA’);


1 row Inserted.

Similarly the Student and Course Table look likes:

Student Table

Student_id Student_name Course_id
123 XYZ 1
789 PQR 1
456 ABC 2

Course Table

Course_id Course_name

Selecting a all the rows of a table:

Select * from

Selecting particular column with some condition:

Select column1, column2….. from

Note:We can also add where clause to it


Select * From Student


Student_id Student_name Course_id
123 XYZ 1
789 PQR 1
456 ABC 2


Select course_id, student name from Student where student_id=123;


Student_id Student_name Course_id
123 XYZ 1

Updating Data in Oracle:

Update <TanleName> set Columnname=Value;

Note: We can also add where clause to it


Update Course set course_name=”MSC” where Course_id=1;


1 row updated

Deleting a row from a Table:



Delete from Course where course_id=1;


1 Row Deleted.

Renaming a table:

Rename <OldName> <NewName>


Rename Course Course1


Table Renamed

Drop a table from a Database:

Drop Table <TableName>


Drop table Course;


Table dropped.

Truncate a Table From a Database:

Truncate Table <TableName>


Truncate Table Student


Table Truncated


In this article, we studied the most efficient and simple queries of Oracle to be performed on a database to get efficient data easily with no inconsistency and duplicity.

We studied how to create, insert, update data in Oracle database and perform some simple operations.

In next article, we studied some more tasks to be performed on Database like how to use clauses like (join, having etc).

Have done the bachelor's in computer's and have done various courses on many front-end and back-end languages as Java, HTML, CSS, Javascript.

What did you think of this post?
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!

> More info about MrBool Credits
You must be logged to download.

Click here to login