Oracle PL/SQL
Course Objectives
At the end of course, the participants will be able to:
Explain the Oracle 9i features, Oracle Server Architecture and its
components
Write effective PL/SQL programs
Write packages, procedures, functions and triggers
Create Collection types and Objects in Oracle
Oracle PL/SQL - Contents
Oracle Architecture
PL/SQL Basics
Exception handling
Cursors
Procedures and Functions
Database Triggers
Objects in Oracle
Oracle Architecture
Oracle Architecture - Objectives
At the end of this session, the participants will be able to:
Identify the different components of the SGA
Recognize the importance of background components
List the different types of files that make up the physical database
Understand the importance of each type of file
Comprehend the logical database
Oracle Architecture - Contents
System Global Area
Background Processes
Instance
Physical database
Logical database
Oracle Server Architecture
Oracle Server
Oracle Database Oracle Instance
Physical Logical • SGA Memory
• Data Files Structure
• Table spaces
• Control Files • Segments • Background
• Redo Log Files Processes
• Extents
• Blocks
Oracle Server Components
I N S T A N C E
Server
Shared Pool System Global Area
Library Cache
Database Buffer Redo log
Data Dictionary Cache Buffer cache
Cache
PMON SMON DBWR LGWR CKPT Others
Data files Archived Log files
Server Process
Control files
Database
Parameter file (init.ora)
PGA
Redo log files Password file
User Process
User
Oracle Instance
Is a means to access an Oracle database
Can open and use only one database at a time
Consists of SGA memory structure and background processes
- Allocated in the virtual memory of the computer
where the oracle server resides
System Global Area (SGA) stores database information
- Consists of several memory structures
- Shared Pool
- Database Buffer Cache
- Redo Log Buffer
Shared Pool
Stores most recently executed SQL statements and the most recently
used data from the data dictionary
The server process uses the area to compile the SQL statement.
It has two components
- Library Cache: stores information about the most
recently used SQL statements
- The shared SQL area contains
- The text of SQL statement
- The parse tree: compiled version of a statement
- The execution plan
Shared Pool (contd…)
- Data Dictionary Cache (dictionary cache/row
cache): collection of the most recently used
definitions in the database.
- Stores information about database files, tables,
indexes, columns, users, privileges, etc
Database Buffer Cache
Stores the most recently used data. The data is read from, and written to
the data files
The size of each buffer in the buffer cache is equal to the size of an
Oracle block
Redo Log Buffer
Records changes made to the database using the instance
The server process records changes in the redo log buffer
It records the block that is changed, the location of the change, and the
new value
The buffer is reused after it is filled, after all the old redo entries are
recorded in the redo log files
Background Processes
Log Writer (LGWR)
Performs sequential writes from the redo log buffer to the redo log file
- When a transaction commits
- When the redo log buffer is one-third full
- When there is more than a megabyte of changes
recorded in the redo log buffer
- Before DBW0 writes modified blocks in the
database buffer cache to the data files
Confirms the COMMIT only after the redo is written to disk
Background Processes (contd…)
Database Writer (DBW0)
The server process records changes to rollback and data blocks in the
buffer cache
The DBW0 writes the dirty buffers from the database buffer cache to the
data files.
It ensures that a sufficient number of free buffers are available in the
database buffer cache
Background Processes (contd…)
Server processes make changes only in the buffer cache, and the DBW0
defers writing to the data files until
- A number of dirty buffers reaches the threshold
value, or
- A process scans a specified number of blocks
when scanning for free buffers and cannot find any,
or
- A timeout occurs (every three seconds), or
- A checkpoint occurs
Background Processes (contd…)
System Monitor (SMON)
Recovers the instance when the database is started
Rolls forward changes in the redo logs
Rolls back uncommitted transactions
Combines adjacent areas of free space in the data files
De-allocates temporary segments used to store data during SQL
statement processing
Background Processes (contd…)
Process Monitor (PMON)
Cleans up the resources if one of the processes fails
Includes
- Rolling back the user’s transaction
- Releasing all currently held table or row locks
- Freeing other resources currently reserved by the
user
Oracle Database
Logical Structure: Tablespaces
•An Oracle Database can be logically grouped into smaller logical
areas of space known as table spaces
•A tablespace can belong to only one database at a time
•Each tablespace consists of one or more OS files (called data files)
SYSTEM ACCOUNTING SALES
Tablespace Tablespace Tablespace
Data Data Data Data
File File File File
Segments
• Space allocated for a specific logical structure within a tablespace
• Tablespace may consist of one or more segments
• A segment cannot span tablespaces; however, a segment can span
multiple data files that belong to the same tablespace
• Each segment is made up of one or more extents
Types of Description
Segments
Data Stores the data associated with tables
Index Each index file has an index segment
Temporary Used to store temporary data during sorting operations
Rollback To rollback uncommitted transactions for users
Segments (contd…)
Extents
- Space allocated to a segment by extents
- One or more extents make up a segment
- Extent is a set of contiguous Oracle blocks
- Extent must exist in one data file
Data Blocks
- At the finest level of granularity, the data is stored in data
blocks
- One data block corresponds to one or more OS blocks
allocated from existing data file
- Data block size should be a multiple of the OS block size
to avoid unnecessary I/O
Segments (contd…)
Table
Segment
8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB
8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB
Data Block 8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB
Extent Extent Extent
Oracle Database
Is a collection of data that is treated as a unit. Its purpose is to store and retrieve
related information
Its physical structure is the set of OS files in the database
Consists of three file types
- Control Files: contain info required to maintain and verify the
integrity of the database. A database needs at least one control
file
- Data Files: Each tablespace in an Oracle database consists of
one or more files called data files. A data file can belong to only
one tablespace. Contain the data in the database, including
tables, indexes, rollback segments and temp. segments
- Online Redo Logs: Contain a record of changes made to the
database to enable recovery of the data in case of failures. A
database requires at least two redo log files
Other Files
Parameter File
Defines the characteristics of an oracle instance
Password File
Authenticates privileged database users
Archived Redo Logs
Are backups of the online redo logs
How Oracle Works?
I N S T A N C E
Server
Shared Pool System Global Area
Library Cache
Database Buffer Redo log
Data Dictionary Cache Buffer cache
Cache
PMON SMON DBWR LGWR CKPT Others
Data files Archived Log files
Server Process
Control files
Database
Parameter file (init.ora)
PGA
Redo log files Password file
User Process
User
Summary
We have discussed,
The database buffer cache, redo buffer cache and the shared pool
Oracle server background processes
The logical database
The physical database
