Oracle 8: An Overview

1
Oracle 8 An Overview

• Personal Databases
• Client/Server Databases
• Oracle and its Tools
• Types of SQL Statements
• Logging onto SQLPlus Environment
• SQL versus SQLPlus
• SQLPlus Commands

2
Personal Databases

• Usually stored on a client computer
• Primarily for single-user applications
• Stored on a server for multi-user or shared
  access
• A server is a computer that accepts and services
  requests from clients. A servers resources
  include hard disk, programs, data, or printers.
• For example, Microsoft-Access and Visual Fox Pro

3
Overview of Personal Databases

• File Server
• (Database files)
• gets file requests from clients
• sends files to clients
• receives files back from clients

NETWORK

• Client Computer 2
• (DBMS, Client App)
• sends file request
• Receives files
• Adds, deletes, updates data
• Sends files back

• Client Computer 1
• (DBMS, Client App)
• sends file request
• Receives files
• Adds, deletes, updates data
• Sends files back

4
Characteristics of aPersonal Database System

• Heavy traffic on network to transport
  applications and databases between server and
  clients
• Heavy demand on Client computer
• Inferior system response time
• Not scalable to large number of users/clients

5
Characteristics of aPersonal Database System

• No automatic table locking, but optimistic
  locking
• Client failure may result in record locking and
  database corruption
• Transaction log kept in clients memory

6
Client/Server Databases

• Usually stored on the server computer
• Primarily for multi-user applications
• Allows shared-access with locking
• An Oracle server accepts and services requests
  from clients.
• The client computer does not contain database and
  does not perform any processing
• For example, Oracle 8

7
Overview of Client/Server Databases

• Database Server
• (DBMS process)
• Gets data requests from clients
• Adds, deletes, updates, filters data

NETWORK

• Client Computer 1
• (Client Application)
• sends data request
• receives results
• Sends new data or changes

• Client Computer 2
• (Client Application)
• sends data request
• receives results
• Sends new data or changes

8
Characteristics of a Client/Server Database
System

• Low network traffic as only client requests and
  results are transported
• Low demand on client as server processes requests
• Scalable to large number of users/clients
• E.g. 10,000 for oracle

9
Characteristics of a Client/Server Database
System

• Automatic locking
• If a client fails, other clients not affected
• Central Transaction Log kept on server
• If a batch transaction fails, entire transaction
  is rolled back

10
Oracle Database Management System

• Purpose
• Why we use it?
• How it works
• Instance
• database

11
Purpose of Oracle DBMS

• Efficiently and reliably manage large amounts of
  data in a multi-user environment
• Must accomplish the above while maintaining a
  high level of performance
• Provide efficient solution to failure recovery
  and read consistency
• Provide a high level of data concurrency

12
Other Reasons to Use Oracle

• A Client/Server database system
• A object-relational database system
• Oracle Corp is the second largest software
  company in the world

13
Oracle Components

• Oracle consists of a database and an instance.
• A database includes all the physical data files,
  control files, and redo log files that will hold
  your data and Oracles metadata information.
• An instance is a combination of the pool of
  physical memory (RAM) allocated to Oracle,
  referred to as the System Global Area (SGA), and
  the background processes that Oracle spawns to
  use this memory pool.
• SGA
• Area where Oracle attempts to cache database data
  for faster access (RAM I/O is about 1000x faster
  than disk I/O).

14
Oracle overview
Instance
Database
PMON
SGA
SMON
dbf
rbf
dbf
DBWR
LGWR
ctrl
redo
CKPT
Server Process
Server Process
index
Server Process
listener
Server
User App
Client
15
Oracle 8 Tools

• SQLPlus environment to write command-line SQL
  queries
• PL/SQL Oracles procedural language extension
  to SQL
• Query Builder graphical environment for SQL
  query building
• Developer Form Builder, Report Builder, Graphic
  Builder

16
Oracle 8 Tools

• Enterprise Manager a tool to manage database
  and users. For example, Storage Manager,
  Instance Manager and Security Manager
• Oracle Web Application Server to access Oracle
  databases through web pages

17
SQLPLUS Environment

• Accepts ad hoc entry of statements at the prompt
• Accepts SQL statements from files
• Provides a line editor for modifying SQL queries
• Provides environment, editor, format, execution,
  interaction, and file commands
• Formats query results and displays reports on the
  screen
• Controls environmental settings
• Accesses local and remote databases

18
Structured Query Language (SQL)

• Universal query language for relational databases
• Standardized and accepted by ANSI and ISO
• A non-procedural, fourth generation, high-level
  language

19
Types of SQL statements

• Data Definition Language (DDL)
• creates, changes, removes tables structure
• For example, CREATE, ALTER, DROP, RENAME, and
  TRUNCATE
• Data Manipulation Language (DML)
• inserts, changes and removes rows
• For example, INSERT, UPDATE, and DELETE
• Data Retrieval
• retrieves data from database
• For example, SELECT

20
Types of SQL statements (cont)

• Transaction Control Language (TCL)
• manages logical transactions
• For example, COMMIT, SAVEPOINT, and ROLLBACK
• Data Control Language (DCL)
• Gives and removes rights to objects
• For example, GRANT and REVOKE

21
Logging onto SQLPlus

• Once a user logs on by using supplied valid User
  Name, Password and Host String, SQLgt prompt is
  displayed.
• User can type SQL queries or SQLPlus commands at
  the SQLgt prompt.

22
SQL versus SQLPlus
23
SQLPlus File-Related Commands
24
SQLPlus Editing Commands
25
Interaction between SQL and SQLPLUS
SQL Queries
SQL Queries
Buffer
Server
SQLPlus
Query Results
SQLPlus Commands
Formatted Report
26
Sample Databases

• College student database
• 10 tables to store student master records,
  faculty master records, course master records,
  term-by-term course offerings, and student
  registration by each term and supporting tables
  for lookup and additional information for basic
  entities in the database

27
Sample Databases

• Corporation Employee Database
• 6 tables to keep track of the employees basic
  information and the companys organizational
  structure, payroll, raises, and promotion-related
  issues.

28
Alternate Text Editors

• SQLPlus editor is a line editor
• User has no control over the screen
• Difficult to use
• Need more training
• Use alternative editors, like Notepad.
• EDIT (or ED)

29
Oracle Online Help

• SQLPlus environment
• help topic
• E.g. help show, help index
• Explanation of an error messages
• Click Start-gt Programs-gtOracle for Windows xx -gt
  Oracle 8 Error messages, or
• Double clicking on http//goofy.is.ualr.edu7777/o
  racledocs/index.htm

30
Oracle Database Account

• Username
• First letter of the first name plus
• First letter of the initial and plus
• last name (first 6 characters, if gt 7 characters)
• E.g. David A. Scott -gt dascott
• Password
• The student ID
• Host string
• oraprod.is.ualr.edu
• Change the initial password after login
• SQLgt alter user dascott identified by ltnew
  passwordgt

31
Logging onto SQLPlus

• Once a user logs on by using supplied valid User
  Name, Password and Host String, SQLgt prompt is
  displayed.
• User can type SQL queries or SQLPlus commands at
  the SQLgt prompt.