INTRODUCTION TO ORACLE DATABASE

PROF. DR. TAREK ELSHISHTAWY

1
 INFORMATION SYSTEMS AND DATABASES

• Every organization has information that it must store and

manage to meet its requirements.
• An information system is a formal system for storing and
processing information.
• Most companies today use a database to automate their
information systems.
• The purpose of a database is to collect, store, and retrieve
2
related information for use by database applications.
RDBMS
• A database application is a software program that interacts
with a database to access and manipulate data.
• A relational database stores data in a set of simple relations.
The relational model is the basis for a relational database
management system (RDBMS).
• Essentially, an RDBMS moves data into a database, stores the
data, and retrieves it so that it can be manipulated by
applications 3
 RDBMS TYPES OF OPERATIONS:

• Logical operations
• An application specifies what content is required.
• For example, an application requests an employee name or adds an
employee record to a table.
• Physical operations
• RDBMS determines how things should be done and carries out the
peration.
• For example, after an application queries a table, the database may use
an index to find the requested rows, read the data into memory, and
perform many other steps before returning a result to the user. 4
 SCHEMA OBJECTS

• In Oracle Database, a database schema is a

collection of logical data structures, or schema
objects.
• A database schema is owned by a database user
and has the same name as the user name.
• Schema objects are user-created structures, that
carries many types of schema objects, the most
important of which are tables and indexes.
5
 TABLES AND INDEXES
• A table describes an entity such as employees.
You define a table with a table name, such as
employees, and set of columns.
• In general, you give each column a name, a data
type, and a width when you create the table.
• An index is an optional data structure that you
can create on one or more columns of a table.
• Indexes can increase the performance of data
retrieval.
• Indexes are useful when applications often query
a specific row or range of rows. 6
 STRUCTURED QUERY LANGUAGE (SQL)
• SQL is a set-based declarative language that provides an interface to an
RDBMS such as Oracle Database.
• All operations on the data in an Oracle database are performed using SQL
statements.
• SQL statements enable you to perform the following tasks:
• ■ Query data
• ■ Insert, update, and delete rows in a table
• ■ Create, replace, alter, and drop objects
• ■ Control access to the database and its objects 7

• ■ Guarantee database consistency and integrity

PL/SQL

PL/SQL is a procedural extension to Oracle SQL.

• You can use PL/SQL to control the flow of a SQL program,
use variables, and write error-handling procedures.
• A primary benefit of PL/SQL is the ability to store
application logic (Business Logic) in the database itself
through the use of PL/SQL procedure or function.

8
TRANSACTIONS

• An RDBMS must be able to group SQL statements so that

they are either all committed, or all rolled back, which
means they are undone.
• A transaction is a logical, atomic unit of work that contains
one or more SQL statements.
• There are many examples.

9
 DATA CONCURRENCY
• A requirement of a multiuser RDBMS is the control of concurrency,
which is the simultaneous access of the same data by multiple users.
• Without concurrency controls, users could change data improperly,
compromising data integrity.
• If multiple users access the same data, then one way of managing
concurrency is to make users wait. However, the goal of a DBMS is
to reduce wait time so it is either nonexistent or negligible.
• Oracle Database uses locks to control concurrent access to data. A
lock is a mechanism that prevents destructive interaction between
10

transactions accessing a shared resource.

DATA CONSISTENCY

• Each user must see a consistent view of the data, including

visible changes made by a user's own transactions and
committed transactions of other users.
• For example, the database must not permit a dirty read, which
occurs when one transaction sees uncommitted changes made
by another concurrent transaction.

11
A DATABASE SERVER

• A database server is the key to information management.

• In general, a server manages a large amount of data in a
multiuser environment so that users can concurrently
access the same data.
• A database server also prevents unauthorized access and
provides efficient solutions for failure recovery

12
 DATABASE AND INSTANCE
• A database is a set of files, located on disk, that store data. These files can
exist independently of a database instance.
• Database instance is a set of memory structures that manage database files.
The instance consists of a shared memory area, called the system global area
(SGA), and a set of background processes. An instance can exist
independently of database files.
• A database can be considered from both a physical and logical perspective.
• Physical data is data viewable at the operating system level.
• For example, operating system utilities can list database files and processes.
• Logical data such as a table is meaningful only for the database.
13

• A SQL statement can list the tables in an Oracle database, but an operating
system utility cannot.
 DATABASE STORAGE STRUCTURES
• Physical Storage Structures
• The physical database structures are the files that store the data. When you
execute the SQL command CREATE DATABASE, the following files are
created:
• Data files contain all the database data. The data of logical database
structures, such as tables and indexes, is physically stored in the data files.
• Control files contains metadata specifying the physical structure of the
database, including the database name and the names and locations of the
database files.
• Online redo log files is a set of two or more online redo log files. An online
redo log is made up of redo entries (also called redo records), which record
14

all changes made to data.

 DATABASE STORAGE STRUCTURES CONT.
• Logical Storage Structures
• The following logical storage structures enable Oracle Database to have fine-
grained control of disk space use:
• Data blocks are the finest level of granularity, Oracle Database data is stored in data blocks.
One data block corresponds to a specific number of bytes on disk.
• Extents: An extent is a specific number of logically contiguous data blocks, obtained in a
single allocation, used to store a specific type of information.
• Segments : A segment is a set of extents allocated for a user object (for example, a table
or index)
• Tablespaces: A database is divided into logical storage units called tablespaces.
A tablespace is the logical container for a segment. Each tablespace contains at 15

least one data file.

 DATABASE INSTANCE STRUCTURES

• An Oracle database uses memory structures and processes to

manage and access the database.
• All memory structures exist in the main memory of the computers
that constitute the RDBMS.
• When applications connect to an Oracle database, they are
connected to a database instance.
• The instance services applications by allocating other memory areas
in addition to the SGA, and starting other processes in addition to
background processes. 16
APPLICATION ARCHITECTURE

• To take full advantage of a given computer system or network,

Oracle Database enables processing to be split between the
database server and the client programs.
• The computer running the RDBMS handles the database server
responsibilities while
• the computers running the applications handle the
interpretation and display of data.
• Application Architecture: The two most common database
architectures are client/server and multitier. 17
 CLIENT/SERVER AND MULTITIER ARCHITECTURES

• In a client/server architecture, the client application initiates a request for

an operation to be performed on the database server.
• The server runs Oracle Database software and handles the functions
required for concurrent, shared data access.
• The server receives and processes requests that originate from clients.
• In a multitier architecture, one or more application servers perform parts of
the operation. An application server contains a large part of the application
logic, provides access to the data for the client, and performs some query
processing, thus lessening the load on the database.
• The application server can serve as an interface between clients and
18
multiple databases and provide an additional level of security
NETWORKING ARCHITECTURE
• Oracle Net Services is the interface between the database and the
network communication protocols that facilitate distributed processing
and distributed databases. Communication protocols are supported as
TCP/IP, HTTP, FTP, and WebDAV
• Oracle Net Listener (called the listener), which is a separate process that
runs on the database server. Client applications can send connection
requests to the listener, which manages the traffic of these requests to
the database server. When a connection is established, the client and
database communicate directly.
• Oracle Net, a component of Oracle Net Services, establishes and maintains a network
session from a client application to a database server. After a network session is
established, Oracle Net acts as the data courier for both the client application19and the
database server, exchanging messages between them.