Chapter 1

Introduction to Oracle DBMS

CT208H – Oracle Database Management System
 Content
1. Oracle 11g Enterprise Edition
2. Database user types
3. SQL, PL/SQL, SQL*Plus and Oracle SQL Developer
4. Oracle versions and editions
5. Administration accounts & security

2
1. ORACLE 11G ENTERPRISE EDITION

Chapter 1. Introduction to Oracle DBMS

3
  What Is Oracle Database?

What is a database?
• A database is an organized collection of structured data stored
electronically in a computer system
• Before the database system was invented, the flat file
structure was commonly used to store data
– When the number of rows in
the flat file is increased
e.g., million rows, it becomes
unmanageable

4
  What Is Oracle Database?

Relational database model

• Invented by Dr. Ted Codd in the 1970s
• Data is organized into entities and attributes instead of
combining everything in a single structure
– An entity (row/record) is a person, place, or thing
– Attributes (field) describe the person, place, and thing
– Similar entities are stored in a table
– Related tables are stored in a database
– Relations of entities are described by data
5
  What Is Oracle Database?

Relational Database Management Systems

• Database Management Systems based on relational data
model (RDBMS in short)
• The most popular type of DBMS
• Some other notable ones:
– Oracle (from Oracle Corporation)
– DB2 (from IBM)
– SQL Server (from Microsoft)
– MySQL (open-source RDBMS, also from Oracle)
6
  What Is Oracle Database?

Oracle Database Management System

• One of the most popular RDBMS, developed by Oracle
Corporation in 1977
• Oracle database characteristics:
– Cross-platform: Windows, Unix, GNU/Linux
– ACID-compliant: ensures data integrity and reliability
– Oracle networking stack: applications from different platforms
can communicate with each other
– Commitment to open technologies: supports open GNU/Linux

7
  What Is Oracle Database?

Oracle Database Management System

• Important features:

8
 Oracle Database Management System
• Availability:
– Supported 24*7 availability of the database
– Oracle Data Guard functionality: allows using of the secondary
database as a copy of the primary database during any failure
• Security:
– Unauthorized accesses are prevented
– Oracle Advanced Security: offers Transparent Data Encryption
(source level or after export) and Data Redaction (application level)

9
 Oracle database management system
• Scalability:
– Provides features like RAC (Real Application Cluster) and
Portability, which makes an Oracle DB scalable based on usage
– RAC provides capabilities such as rolling instance migrations,
performing upgrades, maintaining application continuity, etc.
• Performance: Provides many performance optimization tools,
such as Oracle Advanced Compression, and Oracle Database
In-Memory,… to improve system performance

10
 Oracle database management system
• Analytics:
– OLAP (Oracle Analytic Processing): provides complicated
analytical calculations on business data
– Oracle Advanced Analytics: assists customers in determining
predictive business models through data and text mining, as
well as statistical data computation
• Management: Oracle Multitenant combines a single container
database with many pluggable databases in a consolidated
design
11
 Oracle database management system
• Some other features:
– Logical data structure: users can interact with the database
without knowing where the data is stored physically
– Partitioning: a large table can be divided into different pieces,
and pieces can be stored across storage devices
– Memory caching: allows scaling up a very large database that
still can perform at a high speed
– Portability: Oracle database can be ported on all different
platforms than any of its competitors
12
 Oracle database management system
• Some other features:
– Backup and recovery:
• Ensures the integrity of the data in case of system failure
• The Recovery Manager (RMAN) tool allows DBA to perform cold,
hot, and incremental database backups and point-in-time recovery
– Clustering: enables high availability that enables the system to
be up and running without interruption of services in case one
or more servers in a cluster fails

13
 Oracle database management system
• Disadvantages:
– Complexity: Oracle is not recommended for use when the users
are not technically savvy and have limited technical skills
required to deal with the Oracle Database
– Cost: the price of Oracle products is very high in comparison to
other databases
– Difficult to manage: Oracle databases are often much more
complex and difficult in terms of the management of certain
activities
14
2. ORACLE DATABASE USER TYPES

Chapter 1. Introduction to Oracle DBMS

18
 Types of Oracle users
• Database Administrators
• Security Officers
• Application Developers
• Application Administrators
• Database Users
• Network Administrators

19
 Types of Oracle users
• Database administrators:
– Oracle DBs are large and have many users
– Main responsibilities:
• Installing and upgrading the Oracle Server and application tools
• Managing DBs (storage, tablespaces, DB objects)
• Enrolling users and maintaining system security
• Monitoring and optimizing the performance of the database
• Planning for backup and recovery of database information

20
 Types of Oracle users
• Application developers:
– Design and implement database applications
– Main responsibilities:
• Designing the database structure for an application
• Tuning the application during development
• Establishing an application's security measures during
development
• Application administrators: administrate a particular
application
21
 Types of Oracle users
• Security officers: concerned with enrolling users, controlling
and monitoring user access to the database, and maintaining
system security
• Database users: interact with the database via applications or
utilities
– entering, modifying, and deleting data, where permitted
– generating reports of data
• Network administrators: administering Oracle networking
products, such as Net8
22
3. SQL, PL/SQL, SQL*PLUS AND SQL DEVELOPER

Chapter 1. Introduction to Oracle DBMS

23
 SQL
• A set-based declarative language that provides an interface to
an RDBMS such as Oracle Database
• A nonprocedural that describes what should be done, not
how to derive it
• SQL is the ANSI standard language for relational databases.
• Oracle SQL is an implementation of the ANSI standard and
supports numerous features that extend beyond standard SQL

24
 PL/SQL
• A procedural extension to Oracle SQL
• Enables to use all of the Oracle Database SQL statements,
functions, and data types
• Used to control the flow of SQL programs, create and use
variables, and write error-handling procedures
• A primary benefit of PL/SQL is the ability to store application
logic in the database itself
=> Built-in functionalities can be deployed anywhere
25
 SQL*Plus
• A command-line interactive user interface and batch query
tool that is installed with every Oracle Database installation
• Has its own commands and environment, and it provides
access to the Oracle Database
– Format, perform calculations, store, and print from query results
– Examine table and object definitions
– Develop and run batch scripts
– Perform database administration
26
SQL*Plus

27
Oracle SQL Developer

28
4. ORACLE VERSIONS AND EDITIONS

Chapter 1. Introduction to Oracle DBMS

29
Oracle Database versions

30
 Oracle Database 11g editions
• Enterprise Edition:
– Provides the performance, availability, scalability, and security
required for mission-critical applications such as high-volume
Online Transaction Processing (OLTP) applications, query-
intensive data warehouses, and demanding Internet applications
– Contains all of the components of the Oracle Database, and can
be further enhanced with the purchase of the options and packs
described

31
 Oracle database 11g editions
• Standard Edition One:
– Delivers unprecedented ease of use, power, and performance for
workgroup, department-level, and Web applications
– From single-server environments for small business to highly
distributed branch environments
• Standard Edition:
– Unprecedented ease of use, power, and performance of Standard
Edition One
– Supports larger machines and clustering of services with Oracle Real
Application Clusters (Oracle RAC)

32
 Oracle database 11g editions
• Personal Edition:
– Supports single-user development and deployment
environments that require full compatibility with all other
editions
– Includes all of the components and options that are included
with Enterprise Edition, with the exception of the Oracle Real
Application Clusters option and the Management Packs
– Available on Windows and Linux platforms only

33
 Oracle database 11g editions
• Express Edition:
– An entry-level edition of Oracle Database
– Quick to download, simple to install and manage, and is free to
develop, deploy, and distribute
– Can be installed on any size machine with any number of CPUs,
stores up to 11GB of user data, uses up to 1 GB of memory and
only one CPU on the host machine

34
5. ADMINISTRATION ACCOUNTS & SECURITY

Chapter 1. Introduction to Oracle DBMS

35
 DB administrator security and privileges
• Administrative tasks in Oracle need extra privileges both
within the database and possibly in the operating system (e.g. ,
for software installation, executing OS commands, etc.)
• Access to a database administrator account should be tightly
controlled
• Two user accounts are automatically created with the
database and granted the DBA role: SYS and SYSTEM

36
 The SYS account
• Automatically created and granted the DBA role
• All of the base tables and views for the database’s data
dictionary are stored in the schema SYS
• Tables in the SYS schema are manipulated only by Oracle
• Most database users should never be able to connect using
the SYS account
• DBA role: contains all DB system privileges, and thus, it should
only be granted to fully functional DB administrators
37
 The SYSTEM account
• Automatically created and granted all system privileges
• The SYSTEM username creates additional tables and views
that display administrative information and internal tables and
views used by Oracle tools
• Never create tables of interest to individual users in the
SYSTEM schema

38
 DB administrator authentication
• DB administrators must often perform administrative
operations such as shutting down or starting up a database
=> need a more secure authentication scheme than normal users
• Authentication methods:
– Operating system authentication
– Network authentication
– Oracle database authentication
• Oracle allows using all methods within the same DB instance
39
 Operating system authentication
• Benefits:
– Once authenticated by the operating system, users can connect
to Oracle more conveniently without specifying a username or
password
– With control over user authentication centralized in the
operating system, Oracle need not store or manage user
passwords, though it still maintains usernames in the database
– Audit trails in the database and operating system use the same
usernames
40
 Network authentication
• Authentication capabilities at the network layer are handled
by the SSL protocol or by third-party services such as:
– DCE authentication
– Kerberos authentication
– Public Key Infrastructure-Based authentication
– Authentication with RADIUS
– Directory-based services

41
 Oracle database authentication
• Oracle can authenticate users attempting to connect to a
database by using information stored in that database
• To set up Oracle to use database authentication:
– Creating each user with an associated password that must be
supplied when the user attempts to establish a connection
– Users’ passwords are stored in the data dictionary in an
encrypted format to prevent unauthorized alteration

42
QUESTIONS?

Chapter 1. Introduction to Oracle DBMS

43